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
Download Practice File
Secrets of Excel Data Visualization: Beginners to Advanced Course
Here is another best rated Excel Charts and Graph Course from ExcelSirJi. This courses also includes On Demand Videos, Practice Assignments, Q&A Support from our Experts.
This Course will enable you to become Excel Data Visualization Expert as it consists many charts preparation method which you will not find over the internet.
So Enroll now to become expert in Excel Data Visualization. Click here to Enroll.
Excel VBA Course : Beginners to Advanced
We are offering Excel VBA Course for Beginners to Experts at discounted prices. The courses includes On Demand Videos, Practice Assignments, Q&A Support from our Experts. Also after successfully completion of the certification, will share the success with Certificate of Completion
This course is going to help you to excel your skills in Excel VBA with our real time case studies.
Lets get connected and start learning now. Click here to Enroll.
Use Automated Excel Utility Tools to increase productivity
Hope you are enjoying learning Excel with us, if you want any support related to this article, please do comment else you can ask questions in Excel Community
Excellent, this was a great guide to get me started. Thank you very much! ***