Read Excel as Recordset
Posted: 2002-06-01
By: ArchiveBot
Viewed: 53
Filed Under:
No attachments for this post
An alternative method of reading an MS Excel Spreadsheet.
Original Author: BigP
Inputs
You must supply the full path and file name of the Excel Sheet you wish to read.
Returns
It returns the Specified Sheets' data in an ADO recordset.
Code
Dim 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
Comments on this post
No comments have been added for this post.
You must be logged in to make a comment.