VBA – Read Data from a closed Excel file without Opening it

Last updated: 22nd June 2024

I don't want to manually copy data from the source to the destination. So, I needed a method, which will automate the process of copying data from multiple files repeatedly without even opening the files physically. It will save time, reduce errors and avoid duplicates.

Similar example: How to Copy data (including formulas) from one Excel file to another using VBA

So, let's do it.

Open Excel file (the destination file, where you want to copy the data) and press Alt + F8. Under "Microsoft Excel Objects" in "Project Explorer", you will find ThisWorkbook module. Double click to open it.

From the object dropdown list (top left inside the module), choose "Workbook".

To ensure instant data updates when an Excel file opens, write the code inside the Workbook_Open() event within the ThisWorkbook object in VBA. This method automatically refreshes figures upon opening the workbook, streamlining data retrieval.

The VBA Code

Option Explicit

Private Sub Workbook_Open()
    ' When the file opens...
    Call readDataFromCloseFile
End Sub

Sub readDataFromCloseFile()
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    
    Dim src As Workbook
    
    ' OPEN THE SOURCE EXCEL WORKBOOK IN "READ ONLY MODE".
    Set src = Workbooks.Open("C:\Q-SALES.xlsx", True, True)
    
    ' GET THE TOTAL ROWS FROM THE SOURCE WORKBOOK.
    Dim iTotalRows As Integer
    iTotalRows = src.Worksheets("sheet1").Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row).Rows.Count
   
    ' COPY DATA FROM SOURCE (CLOSE WORKGROUP) TO THE DESTINATION WORKBOOK.
    Dim iCnt As Integer         ' COUNTER.
    For iCnt = 1 To iTotalRows
        Worksheets("Sheet1").Range("B" & iCnt).Formula = src.Worksheets("Sheet1").Range("B" & iCnt).Formula
    Next iCnt
    
    ' CLOSE THE SOURCE FILE.
    src.Close False             ' FALSE - DON'T SAVE THE SOURCE FILE.
    Set src = Nothing
    
ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
📋

Similar example: How to read multiple files and merge data into a Single file using a simple VBA macro.

Let me explain the code.

Property Application.ScreenUpdating

The first line inside the procedure is Application.ScreenUpdating property. I have set it false. This would speed up the code that I have written. Read this MSDN blog to understand more about the property.

🚀 Similar example: 3 Effective Methods to Extract Data from Another Excel Workbook Using VBA

Open and Read Data from the Source File

Next, I am opening the source Excel Workbook to read the data from it. The Excel file will not physically open and it is in a readonly state.

Set src = Workbooks.Open("C:\Q-SALES.xlsx", True, True)

Once I get the data, I’ll count the number of rows in the source workbook. This would help me iterate through all the data from the source and write the data to the destination Workbook (the current Workbook in this case).

' COPY DATA FROM SOURCE (CLOSE WORKGROUP) TO THE DESTINATION FILE.
For iCnt = 1 To iTotalRows
    Worksheets("Sheet1").Range("B" & iCnt).Formula = src.Worksheets("Sheet1").Range("B" & iCnt).Formula
Next iCnt 
📋

Finally, close the source file and set the property Application.ScreenUpdating to true.

Similar example: How to merge multiple rows into a single cell repeatedly using VBA?

We just learnt how to read data from a Closed Excel file without opening it. This is a very simple procedure. You can now simultaneously update multiple files, using data from a single source, without worrying about updating data manually.

This would virtually solve another situation, where users want to update the destination Excel files without opening it. Since, you have written the above procedure inside Workbook_Open() event, your users will actually see an updated data when they open the file.

← PreviousNext →