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:\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 this code in your Excel file, 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 dataVBA Code to Read Excel Data using Connection String
  • 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 ExcelVBA Code to Read Excel Data using Connection String
  • 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 cellVBA Code to Read Excel Data using Connection String
  • Add Microsoft ActiveX Data Objects 2.0 Library from the reference in your codeVBA Code to Read Excel Data using Connection String
  • 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
Before:

VBA Code to Read Excel Data using Connection String

After:

VBA Code to Read Excel Data using Connection String

Leave a Reply

avatar
VBA Code to Delete File
EXCEL VBA TRICKS
VBA Code to Delete File

In this article we are going to show you how you can delete file or files using a single line of VBA code. 1. Delete a specific file from the folder, 2. Delete specific type of files from the folder, 3. Delete all files from the folder

VBA Code to Add Border to Excel Range
EXCEL VBA TRICKS
VBA Code to Add Border to Excel Range

Through formatting the cells, you can make your Excel data more beautiful and easier to understand. If you record a macro to add borders to Excel range, you will notice that it generates 30 plus lines of code for just a small work. Here we are sharing one line of code which does the same thing.

Protect Excel Sheet for Manual Input
EXCEL VBA TRICKS
VBA Code to Protect Excel Sheet for Manual Input but Allow Programming Inputs

Did you come across any requirement where you want the user to interact with a sheet only through VBA Form? Here is a simple code which can help you.