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.ADODB.12.0”
- For 97-2003 excel file you can use the “Microsoft.ACE.ADODB.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.