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

I do not want to copy data manually from the source to the destination. This procedure would spare me from entering the data repeatedly on multiple files, reducing possible errors, duplication etc. It will save precious time too.

You can write the code inside Workbook_Open() event inside "ThisWorkBook" object in VBA. This will ensure that the figures (data) are updated instantly when the Excel file opens.

Now, open destination Excel file 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".

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

You'll like this post: How to read multiple files and merge data into a Single file using a simple VBA macro.

I'll explain the macro.

Property Application.ScreenUpdating

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

👉 See here... I have shared 3 different ways to Pull data out from another 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. Excel would not physically open the file 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, don’t forget to close the source file and set the property Application.ScreenUpdating to true.

👉 Do you know you can merge multiple rows into a single Cell repeatedly in Excel using VBA? Check out this article.

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 distributing (or updating) the 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.

Happy Coding. 🙂

← PreviousNext →