VBA to Read Excel Data using Connection String

VBA to Read Excel Data using Connection String

Sometimes as a programmer you need to read heavy (more then 5 MB) Excel files. There are two ways you can read data from Excel files:

  • Open the file using VBA and read the data. Click Here
  • Stablish ADODB connection with Excel file and read data using SQL queries

Here I will be explaining how you can read heavy files with ADODB connection with Excel File and read data using SQL queries:

VBA Code to Read Excel Data using Connection String

Below is the VBA code which uses ADODB connection and reads data from Excel file:

'This function reads data from Excel file using ADODB connection
'Note: Microsoft ActiveX Data Objects 2.0 Library reference is required to run this code
Sub ReadFromExcel()
    Dim strConString As String
    Dim strQuery As String
    Dim objCon As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strDataSource As String
    Dim lCounter As Long
    'Full path of the Excel file from which data needs to be read
    strDataSource = "E:WorkExcelSirJiPosts29. VBA Code to Read Excel Data using Connection StringDummy Data.xlsx"
    'Define Connection string
    strConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & strDataSource & "';Extended Properties=""Excel 12.0;HDR=YES;IMEX=1;"";"
    'Set the SQL query
    'Things to note here: Data is the name of the sheet which needs to be followed by $ in the query
    '[Created At] > #01-01-2000# is the where clause which is optional
    strQuery = "SELECT * FROM [Data$] WHERE [Created At] > #01-01-2000#"
    'Set the new instance of Connection and Recordset
    Set objCon = New ADODB.Connection
    Set rs = New ADODB.Recordset
    'Open the connection
    objCon.Open strConString
    'Run the SQL query and store the result in rs variable
    rs.Open strQuery, objCon, adOpenDynamic, adLockOptimistic
    'Set the initial counter to 2nd row to paste the data
    lCounter = 2
    'Read the data from recordset until it is not empty
    While rs.EOF = False
        Sheet1.Range("A" & lCounter).Value = rs.Fields(0) 'User Id
        Sheet1.Range("B" & lCounter).Value = rs.Fields(1) 'Full Name
        Sheet1.Range("C" & lCounter).Value = rs.Fields(2) 'Email
        Sheet1.Range("D" & lCounter).Value = rs.Fields(3) 'Department ID
        Sheet1.Range("E" & lCounter).Value = rs.Fields(4) 'Country
        Sheet1.Range("F" & lCounter).Value = rs.Fields(5) 'Created At
        Sheet1.Range("F" & lCounter).NumberFormat = "[$-en-US]d-mmm-yy;@" 'Change the cell format to date format
        Sheet1.Range("G" & lCounter).Value = rs.Fields(6) 'Created Time
        Sheet1.Range("G" & lCounter).NumberFormat = "[$-x-systime]h:mm:ss AM/PM" 'Change the cell formate to time format
        lCounter = lCounter + 1 'Increase the counter by 1
        rs.MoveNext 'Move the recordset to next record
    rs.Close 'Close the connect
    objCon.Close 'Close the recordset
    Set objCon = Nothing 'Release the variable from memory
    Set rs = Nothing 'Release the variable from memory
    'Show the confirmation to user
    MsgBox "Done"
End Sub

To use this code in your Excel file, follow below steps:

  • Open the Excel file in which you wa