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.
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.
Three different ways to Pull data from Another Workbook using a Macro
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)
' 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.
How to merge multiple Rows into a single Cell repeatedly in Excel 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 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. ☺