Retrieve Excel Data with ADO
by Linda Quinn
Close Window
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
=================================================================
This content was created by Linda Quinn of LQNet.
See http://www.lqnet.com for a great
collection of articles on this and other topics.
=================================================================
Copyright © 2006-2009, LQ Systems,Inc. All rights reserved.
====================================================================
Want an expert to help with your project?
LQ Systems, Inc.
Business Solutions
====================================================================