Get specific data(query) from Oracle into excel

Question

I want to load specific data(query) from Oracle database into excel. I am able to achieve it through external connections, (Mircosoft Data Access - OLE DB Provider for Oracle), but all of the table is loaded. This was by hit and try. I am not aware what OLE DB is.

1. Is it possible to load specific data using that method.
2. How can I load the same from VBA, I have read many sources, blogs but none are lucid and comprehensive. Can somebody please explain for a newbie. Or refer to me some book/source.

Answer 1

This function will connect to an Oracle Database using ADODB. Make sure to include Microsoft ActiveX Data Objects 2.8 as a reference. You can configure the connectingstring to suit your needs if there are admin privileges.

It will store your database into a variant.

Function ConToDataBase(DBPath As String) As Variant
Dim Con As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim SQL As String
SQL = "SELECT * FROM TableName"
Set Con = New ADODB.Connection
With Con
.ConnectionString = "Provider=OraOLEDB.Oracle;Data source=" & DBPath & ";UserID=;Password:=;"
.Open
End With
Set Rs = New ADODB.Recordset
Rs.Open SQL, Con
Dim Var As Variant
Var = Rs.GetRows
ConToDataBase = Var
Set Rs = Nothing
Con.Close
End Function