June 13, 2011

call an oracle SQL script from EXCEL

 call an oracle SQL script from EXCEL

For example, I'll execute SELECT query.

Step 1: Learn name OLEDB provider of data for Oracle. If you established Oracle Client, this provider will be present. To learn a name of the provider, for example, create an empty file with extension *.udl (in Windows, certainly ). Then open its properties. On a page "Provider" you can see the list of providers.
Step 2: Open VBA in Excel (Tools - Macro - Visual Basic Editors) and write this Script (Insert - New Module):

 Sub Macro1()  
 ' Macro1 Macro  
  Dim MyConn As ADODB.Connection  
  Dim MyRst As ADODB.Recordset  
  Dim MyPr As String  
  Dim Ct As Long  
  Set MyConn = New ADODB.Connection  
  MyPr = "Provider=your_OLEDB_provider_name;Password=your_password;Persist Security Info=True;User ID=your_user;Data Source=your_Oracle_server_name"  
  MyConn.Open MyPr  
  Set MyRst = New ADODB.Recordset  
  MyRst.Open "Select * from table_name", MyConn, adOpenStatic, adLockReadOnly  
  Ct = 1  
  Do Until MyRst.EOF  
   For i = 0 To MyRst.Fields.Count - 1  
   Worksheets("Sheet1").Cells(Ct, i + 1).Formula = MyRst(i)  
   Next i  
   Ct = Ct + 1  
  Set MyRst = Nothing  
  Set MyConn = Nothing  
 End Sub  
With Best Regards. Sam.
P.S. Also you should establish necessarily the reference to objects ADO from Excel: Tools - References... For example, on my computer:
1. Microsoft ActiveX Data Objects 2.7 Libriary
2. Microsoft ActiveX Data Objects Recordset 2.8 Libriary

You can add next text in VBA-script:
   Set fso = CreateObject("Scripting.FileSystemObject")  
   Const ForReading = 1  
   Set f = fso.OpenTextFile("path to your SQL-Script", ForReading)  
   SQLText = f.ReadAll  
   Set MyRst = New ADODB.Recordset  
   MyRst.Open SQLText, MyConn, adOpenStatic, adLockReadOnly