ODBC using XLODBC.XLA and ADODB in VBA

Hi,

I have been looking to connect Oracle DB to excel and found this way much eaiser to query tables, packages and procedures.

Using XLODBC.XLA

For this you need to add the "XLODBC.XLA" file to excel and add the reference of the file in VBA editor too. Usually "XLODBC.XLA" files can be found in "C:\Program files\Microsoft Office\Office\Library\Msquery".

Place the below lines of code in the VBA module.

Sub ExecutePackageFunc()
Dim Conn As Variant
'Open the connection using data source name, user id & password
Conn = SQLOpen("DSN=#####;uid=#####;pwd=#####")

'Select query
SQLExecQuery Conn, "SELECT packagename.functionname(parameter1, parameter2) from dual"

'Populate the result in excel range
SqLretrieve Conn, ActiveSheet.Range("A1"), , , True

'Close the connection
SQLClose (Conn)

End Sub

Note:
SqLretrieve Conn, ActiveSheet.Range("A1"), , , True

The above line of code will place the result set in the cell A1 with header. If you don't need the header then modify your code as below.

SqLretrieve Conn, Range("A1"), 1, 1, 0, 0, 0, 1

USING ADODB

Set VBA reference to "Microsoft ActiveX Data Objects 2.5 Library"

Sub ExecutePackageFunc()

'Declaring Connection Variables
Dim Conn As ADODB.Connection
Dim Rst As ADODB.Recordset

'Set Connection Variables
Set Conn = New ADODB.ConnectionSet
Rst = New ADODB.Recordset

'Open connectin variables
Conn.Open "DSNName", "User_ID", "Password"
Rst.ActiveConnection = Conn
Rst.CursorLocation = adUseClient

'Select data from DB and set it to the recordset

Rst.Source = "SELECT packagename.functionname(parameter1, parameter2) from dual"
Rst.Open

gFee = Rst.Fields(0) 'o is the index of the first field returned by the SQL query
Range("A1").Value = gFee

'Close recordset
Rst.Close

'Close DB connection
Conn.Close

End Sub