• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Read Excel as Recordset

Read Excel as Recordset

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.

Returns
It returns the Specified Sheets' data in an ADO recordset.

Rate Read Excel as Recordset

• 1
• 2
• 3
• 4
• 5

(11(11 Vote))

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