ADO and Excel Data

by Linda Quinn

Close Window Print 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