Auto-Populate Multiple Excel Files from Another Excel File List/Table
In many production environments, data is stored and managed in Excel files. This article will provide a step-by-step guide on how to auto-populate multiple Excel files using a list or table of file names from another Excel file. This method can save time and reduce the chance of errors when managing large amounts of data.
Prerequisites
Before proceeding, ensure that you have the following:
- Microsoft Excel installed on your computer
- A basic understanding of Microsoft Excel functions and features
Template Worksheet
Start by creating a template worksheet for the data you want to populate. This worksheet will act as a basis for all the files you will auto-populate. For our example, let's assume the template worksheet has columns for ‘Date’, ‘Name’, ‘Worked Hours’, ‘Work Phase’, and ‘Product’.
Production Factory Data Source
Next, create a source file that contains a list or table of file names for the Excel files you want to auto-populate. This file should include the full file path for easy reference.
VBA Macro for Auto-Population
Now, let's create a VBA (Visual Basic for Applications) macro that will handle the auto-population process. Press ‘Alt + F11’ to open the Visual Basic Editor. Insert a new module and paste the following code:
Sub AutoPopulateFiles()
Dim wb As Workbook
Dim sourceFile As String
Dim filePath As String
Dim fileNames As Range
Set fileNames = Workbooks("Production_Factory_Data.xlsx").Worksheets("Sheet1").Range("A2:A10")
For Each cell In fileNames
filePath = cell.Value
Set wb = Workbooks.Open(filePath)
With wb.Worksheets("Sheet1")
.Range("A1:E1").Copy Destination:=ThisWorkbook.Worksheets("Template").Range("A1")
.Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Copy Destination:=ThisWorkbook.Worksheets("Template").Range("A2")
End With
wb.Close savechanges:=False
Next cell
End Sub
This code uses a ‘For Each’ loop to iterate through the file names listed in the source file. It opens each file in the loop, copies the header row and data rows, and pastes them into the template worksheet.
Running the Macro
To run the macro, press ‘Ctrl + F8’, select ‘AutoPopulateFiles’, and click ‘Run’. Ensure the source file and template workbook are open before running the macro.
This article provided a detailed guide on how to auto-populate multiple Excel files using a list or table of file names from another Excel file. By following the steps outlined in this article, you can save time and reduce the chance of errors in your data management process.
References
-
Microsoft Excel 2019 Bible
Author: John Walkenbach
Publisher: Wiley -
"Excel VBA Programming for Dummies"
https://www.dummies.com/software/microsoft/excel/excel-vba-programming-for-dummies/ -
"Excel VBA Macro Examples"
https://www.excel-easy.com/vba/examples.html