Read Data from a Closed Excel file Without Actually Opening it using VBA

Let us assume, I have an Excel file in d:\ folder named sales.xlsx". It has two sheets named "April" and "May". Both the sheets have sales data. I want to extract and copy data from the May sheet in the "Sales" workbook into another Excel file, without having to physically open the Sales workbook each time.

The Macro

Follow these steps.

1) Open an Excel file and save the file as Macro Enabled file.

2) Press Alt + F11 keys to open VB editor.

3) You can write the macro in a Module or in any sheet. I am writing the code in "Sheet1".

In this example, I am using the Late binding approach with ADO. This ensures compatibility across different versions of Excel without requiring specific references to be set in advance.

What is Late Binding?

This is a method where "objects" are created and "methods" are resolved at runtime, rather than at compile time. By "resolved," I mean the point when VBA understands what the method is and how it runs.

Note: There are few drawbacks in using the Late binding method. One of the drawbacks, you won't get IntelliSense (or auto complete) while coding. For "IntelliSense" you can use the Early binding method, like adding a "reference". See how to add reference in VBA.

Let's see the Macro.

Open Sheet1 in your VBA editor. Copy the below code and paste it in Sheet1.

Option Explicit

Sub GetDataFromClosedWorkbook()
    Dim myConn As Object, rs As Object      ' Connection and recordset objects.
    Dim strFile As String, strSQL As String
    Dim targetSheet As Worksheet
    
    strFile = "d:\sales.xlsx"       ' The closed file (sales).
    
    ' SQL query to select data from May sheet and its range.
    strSQL = "SELECT * FROM [May$A1:B10]"
    
    ' Create the connection.
    Set myConn = CreateObject("ADODB.Connection")

    myConn.ConnectionString = _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & strFile & ";" & _
        "Extended Properties=""Excel 12.0;HDR=NO;IMEX=1"";"

    myConn.Open    ' Open the connection.
    
    ' Execute query and fill recordset with data.
    Set rs = myConn.Execute(strSQL)
    
    ' Paste results into current workbook.
    Set targetSheet = ThisWorkbook.Sheets("Sales 2025")
    targetSheet.Range("A1").CopyFromRecordset rs
    
    ' Clean up.
    rs.Close
    myConn.Close
    
    Set rs = Nothing
    Set myConn = Nothing
End Sub

Run the Macro. If everything is right, it will copy data from "May" sheet in "Sales.xlsx" and write it in your workbook, without opening it.

👉 To verify this, you can check whether the source file appears as an active process while the macro is running. If you look at the list of running applications in Task Manager, you'll notice that the source workbook is not referenced there.

Important Points in the Macro

1) First I have declared two objects. One each for connection and recordset. The recordset object (or "rs") will hold the data extracted from the source file.

2) Used a SQL query SELECT * FROM [May$A1:B10] to extract data from "May" sheet, range A1:B10.

3) Assigned file name and its path. Must be accurate. strFile = "d:\sales.xlsx"

4) Created a connection using the CreateObject() method.
Set myConn = CreateObject("ADODB.Connection")

5) Assigned a connection string using a "Provider" and the "Data Source" (the source file).

👉 In the connection string, I have assigned "HDR=NO", which means, do not treat the first row as header. I want to extract data as it is. You can also assign "HDR=YES".

IMEX=1 (Import mode) tells the driver to treat all data in a column as text. IMEX=0 means Export mode. This is for writing data.

← Previous