Excel VBA – Read Data from a Closed Excel File or Workbook without Opening it

excel vba

← PrevNext →

Tools you can use

I was in a forum recently, when I came across a question on how to get or read data from a closed excel workbook (file) without actually opening it. Let me guess a simple scenario. I have an Excel file (the source), where I regularly update the Sales figures for each region. In-addition, I have another excel file, which would automatically get the figures from the source file, without opening it.

Read Data from a Closed Excel Workbook without Opening it using VBA

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

You can write the code inside Workbook_Open() event in ThisWorkBook object in VBA. Writing the code inside Workbook_Open() evevt would ensure that it updated the figures immediately when the Excel file opens.

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()
    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.

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
Pull data 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 repeated in Excel using VBA? Check out this article.
Merge multiple rows in on cell usin VBA

Conclusion

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.

Thanks for reading. ☺

← PreviousNext →