Read Excel using ADODB connection

Read Excel using ADODB connection

   

To open and read Excel data using ADODB connection , use following code:

Steps-

1) Create an ADODB connection

2) Open connection

3) check if Error then Exit Function

4) Create a Recordset

5) Execute SQL and store results in reocrdset

6) Read all fields data

7) Close and Discard all variables 


Code-
Dim objAdodbCon, objAdodbRecSet
'1) Create an ADODB connection'
Set objAdodbCon = CreateObject("ADODB.Connection")

'2) Open connection
objAdodbCon.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ="&strFileName & ";Readonly=True"

'3) check if Error then Exit Function'
If Err <> 0 Then
      Reporter.ReportEvent micFail,"Create Connection", "[Connection] Error has occurred. Error : " & Err
   Exit Function
End If

'4) Create a Recordset'
Set objAdodbRecSet = CreateObject("ADODB.Recordset")
objAdodbRecSet.CursorLocation=3                        ' set the cursor to use adUseClient – disconnected recordset

'5) Execute SQL and store results in reocrdset'
strSQLStatement= "Select * from [Sheet1]"
objAdodbRecSet.Open strSQLStatement, objAdodbCon, 1, 3

'6) Read all fields data'
While objAdodbRecSet.EOF=false

           For i=0 to objAdodbRecSet.Fields.count
       
            Msgbox objAdodbRecSet.fields(i)
       
        Next
       
objAdodbRecSet.moveNext

Wend


If Err<>0 Then
      Reporter.ReportEvent micFail,"Open Recordset", "Error has occured.Error Code : " & Err
     Exit Function
End If

'7) Close and Discard all variables '
Set objAdodbRecSet.ActiveConnection = Nothing
objAdodbCon.Close

Set objAdodbCon = Nothing

 

Note:- To work with MS Excel 2007 use following Connection string command

    Set cnDBA = CreateObject("ADODB.Connection")
    cnDBA.connectionstring = "PROVIDER=MICROSOFT.ACE.OLEDB.12.0;DATA SOURCE=" & strDBNameA & "; Extended Properties=""Excel 12.0;HDR=Yes;"";"
    cnDBA.open