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
    Wend
    '
    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