Retrieve Excel Data with ADO
Printer Friendly Page
Set up the variables
Public Sub ExcelExample()
Dim r As Integer, f As Integer
Dim vrecs As Variant
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim fld As ADODB.Field
Set up the connection
Set cn = New ADODB.Connection
Set the data provider
cn.Provider = "Microsoft OLE DB Provider for ODBC Drivers"
aka "MSDASQL.1"
Note we can also use the ProgID: "MSDASQL.1", or nothing!
Define the
Connection string
cn.ConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};
DBQ=C:\QSPR\Q604.xls;"
Open the connection
cn.Open
Get full connection string after opening
Debug.Print "Full connection string: " + cn.ConnectionString
Get recordset using
rs.open SQL statement
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "SELECT * FROM [Branch Totals$]", cn, adOpenDynamic, adLockOptimistic
Print the field names (from first row)
For Each fld In rs.Fields
Debug.Print fld.Name
Next
Get the rows all at once
vrecs = rs.GetRows(6)
For r = 0 To Ubound(vrecs, 1)
For f = 0 To Ubound(vrecs, 2)
Debug.Print vrecs(f, r)
Next
Next
rs.Close
cn.Close
End Sub