Excel VBA – Read Data from a Closed Excel File or Workbook
without Opening it
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.
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.
Related Post: How to Read Multiple Excel Files and Merge Data into a Single
File using VBA
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
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.
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.
