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

objConnection.Open strConnectionString

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.

Range(“YourRangeReference”).CopyFromRecordset objRecordset

Download working file

Excel To Excel Connection (131 downloads)
1 reply
  1. Siva
    Siva says:

    I would like to write a sql query – one excel file having City Code and other details and another excel file (City Master) having City Code, City Name, etc.

    I would like to get a few fileds from fist excel file (Sheet1) and get the city name and update another excel file with these records based on certain criteria.

    In the ADODB Connection and Recordset I select the file in browse mode and read the records and write in another excel file with City Code now and it is working fine. In the query it is a few fields I select from the single file (selected and opened). If I want to get City Name from CityMaster, should I keep this file also selected and opened to use in sql with 2 files. Please clarify and gudie me.

    StrQuery = “Select Name, Age, Gender, CityCode from Sheet1 Where Age <=50 Order By Name " – This is the current query.

    If I want to use 2 files can I write –
    "Select Name, Age, Gender, CityMaster.CityCode from Sheet1, CityMaster Where Age <=50 And Sheet1.CityCode = CityMaster.CityCode Order By Name"

    My query – First file Sheet1 is opened in the code. Should I also open CityMaster before using this query or not required. If yes, how to open the second file. Need help on this area please.

    You may reach me at


Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *