ADODB Connection – Read excel data without opening file
Ever wondered if you can read excel without opening file? well there is a way. you can have VBA treat excel file like a database and worksheets as tables by using ADODB Connection. this is a quit simple process. you need just four things 1. A Connection String 2. A Connction Object 3. A Recordset 4. A Query
How to connect excel with another excel file?
Most importantly, you need a connection string that will contain the connection information. A connection string has couple parts of it.
- Provider – Provider is the driver which will create the connection to excel file.
- For xlsx/xlsm files you will need to use “Microsoft.ACE.OLEDB.12.0”
- For 97-2003 excel file you can use the “Microsoft.ACE.OLEDB.12.0” but then you will need to specify excel version in extended property.
- For standard excel files (Excel 95) you will need to use Microsoft.Jet.OLEDB.4.0
- Data Source – Data Source is your excel file path with file name.
- Extended Properties – Here you will specify which version of excel you want to connect, If you want to treat first row as header and the data type of each field.
- Excel Version – Later than 2007, you should write “Excel 12.0”, earlier than 2007, you will write “Excel 8.0”
- HDR = HDR=Yes if you want to treat first row as header. HDR=No, if you dont.
- IMEX =if you want to treat all field as string data type use IMEX=1, If you want driver to automatically detect the data type use IMEX=0 or IMEX=2. it will look at first 8 rows in data will automatically decide the data type. it might cause an issue not importing data correctly if you data comes in different data type after 8 rows.
Example of Connection String
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\user\excelfile.xlsx;Extended Properties =””Excel 12.0;HDR=Yes;IMEX=1″”
Provider=Microsoft.JET.OLEDB.8.0;Data Source=C:\user\excelfile.xlsx;Extended Properties =””Excel 8.0;HDR=Yes;IMEX=1″”
How to create ADODB connection object?
A connection object will be an ADODB connection. this is how you can create object
Dim objConnection as object
Set objConnection = CreateObject(“ADODB.Connection”)
Once you have your connecting string and connection object ready, just open the connection
How to read data from excel?
You will need a a variable that will hold the data, this is going to be a recordset object. this is how you can create recordset object.
Dim objRecorset as object
Set objRecordset = CreateObject(“ADODB.Recordset”)
Creating a select query
Now you are all set with the required objects to make a connection and read the data from an excel file without opening it. It will not open excel file but it will sure open a connection the excel file. Now you just need a select query which you will pass into recordset open method to read data.
Dim strQuery as String
strQuery =”Select * From sheet1$”
objRecordset.Open strQuery, ObjConnection
And thats it!! After running this last line recorset object will retrieve all data from sheet1. now you need to copy this data into the output table.
Download working fileExcel To Excel Connection (12 downloads)