Retrieve Data to Excel with a Macro using ADO

The following Excel macro shows how to connect to an Oracle database using ADO, create a new worksheet in the current work book, and then fill in the returned data using a slow method (not recommended) and a fast method.

'Need to add a reference to Microsoft ActiveX Data Objects 2.8 Library before starting
'*** This section might be in the top portion of the user form, worksheet code, or module:
'Declare a connection object in the general section to hold the connection to the database
Dim dbVMFG As ADODB.Connection
 
'Declare a set of variables to hold the username and password for the database
Dim strUserName As String
Dim strPassword As String
Dim strDatabase As String
'***
 
Dim i as Integer
Dim intResult As Integer
Dim strSQL as String
Dim lngRow as Long
    
On Error Resume Next
 
strDatabase = "MyDB"
 
strUserName = "MyUser"
strPassword = "MyPassword"
 
'Connect to the database
'Oracle connection string
Set dbVMFG = New ADODB.Connection
dbVMFG.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUserName & ";Password=" & strPassword & ";ChunkSize=1000;FetchSize=100;"
 
dbVMFG.ConnectionTimeout = 40
dbVMFG.CursorLocation = adUseClient
dbVMFG.Open
 
If (dbVMFG.State <> 1) Or (Err <> 0) Then
    intResult = MsgBox("Could not connect to the database.  Check your user name and password." & vbCrLf & Error(Err), 16, "Excel Demo")
Else
    strSQL = "SELECT" & VBCrLf
    strSQL = strSQL & "  ID AS RESOURCE_ID," & VBCrLf
    strSQL = strSQL & "  DESCRIPTION" & VBCrLf
    strSQL = strSQL & "FROM" & VBCrLf
    strSQL = strSQL & "  MY_TABLE" & VBCrLf
    strSQL = strSQL & "WHERE" & VBCrLf
    strSQL = strSQL & "  DESCRIPTION LIKE '%10%'" & VBCrLf
    strSQL = strSQL & "ORDER BY" & VBCrLf
    strSQL = strSQL & "  ID"
 
    'Add a new worksheet to the new workbook, add after the last sheet
    ActiveWorkbook.Sheets.Add
    ActiveWorkbook.ActiveSheet.Name = "JustATest"
        
    snpData.Open strSQL, dbVMFG
        
    If snpData.State = 1 Then
        'Slow Method------------------------------------
        lngRow = 0
        'Header Row
        For i = 0 To snpData.Fields.Count – 1
            lngRow = lngRow + 1
            ActiveSheet.Cells(lngRow, i + 1).Value = snpData.Fields(i).Name
            ActiveSheet.Cells(lngRow, i + 1).Font.Bold = True
        Next i
 
        'Detail Rows
        Do While Not snpData.EOF
            lngRow = lngRow + 1
            For i = 0 To snpData.Fields.Count - 1
                ActiveSheet.Cells(lngRow, i + 1).Value = snpData.Fields(i)
            Next i
 
            snpData.MoveNext
        Loop
        'End Slow Method------------------------------------
 
        'Fast Method----------------------------------------
        'Do not use this and the slow method!
        For i = 0 To snpData.Fields.Count - 1
            ActiveSheet.Cells(1, i + 1).Value = snpData.Fields(i).Name
        Next i
        ActiveSheet.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(1, snpData.Fields.Count)).Font.Bold = True
    
        ActiveSheet.Range("A2").CopyFromRecordset snpData
            
        'Auto-fit up to 26 columns
        ActiveSheet.Columns("A:" & Chr(64 + snpData.Fields.Count)).AutoFit
        'End Fast Method----------------------------------------
 
        snpData.Close
    End If
End If