The following example uses ADO with late binding, you need to replace the
"my" arguments in the connection string with yours:
Sub Oracle_Test()
Dim adoCN As Object
Dim adoRS As Object
Dim rngCell As Range
Dim lngX As Long
Set adoCN = VBA.CreateObject("ADODB.Connection")
adoCN.CursorLocation = 3
adoCN.ConnectionString = "PROVIDER=MSDASQL.1;DRIVER={ORACLE ODBC
DRIVER};SERVER=myInstanceName;DBQ=myDBQName;UID=myUserID;PWD=myPassword;"
adoCN.CommandTimeout = 0 'NO TIMEOUT
adoCN.Open
Set adoRS = VBA.CreateObject("ADODB.Recordset")
adoRS.MaxRecords = 0 '0 = ALL RECORDS
Set adoRS = adoCN.Execute("SELECT * FROM DUAL")
For lngX = 1 To adoRS.Fields.Count
ActiveCell.Offset(0, lngX - 1).Value = adoRS.Fields(lngX - 1).Name
Next lngX
ActiveCell.Offset(1, 0).Select
ActiveCell.CopyFromRecordset adoRS
adoRS.Close
Set adoRS = Nothing
End Sub
HTH
