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
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. 🙂