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:

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:\Work\ExcelSirJi\Posts\29. VBA Code to Read Excel Data using Connection String\Dummy 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 VBA Code Follow below steps:

Open the Excel file in which you want to read data from another Excel file
Press Alt+F11
Insert a Module (Insert>Module) from menu bar
Paste the code in the module
Replace the following highlighted file path from actual file path from which you want to read data
Replace the following highlighted SQL query with actual SQL query. If you want to just read entire data from the sheet then write Select * from Sheet1$. Note that here Sheet1 is the name of the sheet in the Excel
Here we have 7 columns that we are reading from the Excel file. If you have more or less columns in your Excel file then you need to modify below highlighted code. Note that my Excel file has date data in column F (Created At) and time data in column G (Created Time); hence I have made few changes in below code to change the format of the cell
Add Microsoft ActiveX Data Objects 2.0 Library from the reference in your code
If you do not know how to add reference in the code, you may read following post
Now add a shape in Excel sheet
Give a name to the shape like ‘Read Excel Data’
Right click on the shape and select ‘Assign Macro…’
Select ‘ReadFromExcel’ from the list and click on ‘Ok’ button
Done, click on the shape to read data from Excel file