by BigP (1 Submission)
Category: Databases/Data Access/DAO/ADO
Compatability: Visual Basic 3.0
Difficulty: Advanced
Date Added: Wed 3rd February 2021
Rating: 
(11 Votes)
An alternative method of reading an MS Excel Spreadsheet.
Inputs
You must supply the full path and file name of the Excel Sheet you wish to read.
Code Returns
It returns the Specified Sheets' data in an ADO recordset.
VBcodersDim cn As ADODB.Connection
Dim rsADO As New ADODB.Recordset
Dim strSQL As String
Dim strPath as string
Set cn = New ADODB.Connection
strPath = '[ADD FULL PATH AND FILE NAME]
With cn
.Provider = "MSDASQL"
.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ=" & strPath & " ; ReadOnly=false;MaxScanRows= 0;"
.Open
End With
' Specify Sheet Name and Cell Range
strSQL = "SELECT * FROM [Sheet1$A1:Z10]"
rsADO.Open strSQL, cn
Do while not rs.EOF
' Add code here to work with recordset
rsADO.MoveNext
Loop
Set cn = Nothing
Set rsADO = Nothing