无编程背景Excel初阶进阶用户求多生产线数据自动化合并方案：Power Query/VBA

Hey there! As someone who’s worked with tons of production line data automation, I get exactly what you’re trying to do. Let’s break this down for you—since you’re comfortable with Excel but don’t have coding experience, I’ll give you a straightforward VBA solution that doesn’t require Power Query (though I’ll also touch on Power Query briefly just in case you want to explore that later).

Should You Use Power Query? #

Power Query is a great no-code option for this task, but since you asked for VBA if possible, I’ll focus on a robust, easy-to-set-up VBA script. It’s designed for users without coding background, with clear comments and steps to follow.

VBA Code to Merge All Production Data #

This script will:

• Let you select the root folder holding all 10 production line folders
• Automatically scan through all monthly and date subfolders
• Merge all Excel files (.xlsx/.xls) into one sheet, with a column showing the source file path (so you know exactly which line/date each data entry comes from)

Step-by-Step to Use the Code #

1. Open a blank Excel workbook
2. Press Alt + F11 to open the VBA Editor
3. Right-click your workbook in the Project Explorer → Insert → Module
4. Paste the code below into the module
5. Press F5 to run it, or assign it to a button in Excel for one-click access later

Sub MergeProductionData()
    Dim rootFolder As String
    Dim ws As Worksheet
    Dim mergedLastRow As Long
    Dim sourceWb As Workbook
    Dim sourceWs As Worksheet
    
    ' Let you pick the root folder with all production line folders
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select Root Folder (Contains All 10 Production Line Folders)"
        If .Show = -1 Then
            rootFolder = .SelectedItems(1) & "\"
        Else
            MsgBox "No folder selected. Exiting."
            Exit Sub
        End If
    End With
    
    ' Create a new sheet for merged data
    Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    ws.Name = "Merged Production Data"
    
    ' Add your custom headers here (match columns in your production files!)
    ws.Range("A1").Value = "Source File Path"
    ws.Range("B1").Value = "Production Quantity" ' Example column
    ws.Range("C1").Value = "Downtime Minutes"    ' Example column
    ws.Range("D1").Value = "Shift"               ' Example column
    
    ' Make headers bold for readability
    ws.Rows(1).Font.Bold = True
    
    ' Speed up the process by turning off screen updates
    Application.ScreenUpdating = False
    
    ' Start scanning all folders and files
    Call ScanFolder(rootFolder, ws)
    
    ' Turn screen updates back on
    Application.ScreenUpdating = True
    
    ' Auto-fit columns so everything is visible
    ws.Columns.AutoFit
    
    MsgBox "Data merge done! Check the 'Merged Production Data' sheet.", vbInformation
End Sub

Sub ScanFolder(folderPath As String, ws As Worksheet)
    Dim fileSystem As Object
    Dim folder As Object
    Dim subFolder As Object
    Dim file As Object
    Dim lastRow As Long
    
    Set fileSystem = CreateObject("Scripting.FileSystemObject")
    Set folder = fileSystem.GetFolder(folderPath)
    
    ' Loop through every file in the current folder
    For Each file In folder.Files
        ' Only process Excel files (add .xlsm here if you use those)
        If LCase(fileSystem.GetExtensionName(file.Name)) = "xlsx" Or LCase(fileSystem.GetExtensionName(file.Name)) = "xls" Then
            ' Open the source file in read-only mode (no risk to original data)
            Set sourceWb = Workbooks.Open(file.Path, ReadOnly:=True)
            
            ' Assume data is in the first sheet of each file (change if needed!)
            Set sourceWs = sourceWb.Sheets(1)
            
            ' Find the last row with data in the source file
            lastRow = sourceWs.Cells(sourceWs.Rows.Count, "A").End(xlUp).Row
            
            ' Find the next empty row in the merged sheet
            mergedLastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
            
            ' Copy data from source (skip header row) to merged sheet
            If lastRow > 1 Then
                sourceWs.Range("A2:" & sourceWs.Cells(lastRow, sourceWs.Columns.Count).Address).Copy _
                    Destination:=ws.Range("B" & mergedLastRow)
                
                ' Add the source file path to column A
                ws.Range("A" & mergedLastRow & ":A" & mergedLastRow + lastRow - 2).Value = file.Path
            End If
            
            ' Close the source file without saving changes
            sourceWb.Close SaveChanges:=False
        End If
    Next file
    
    ' Recursively scan all subfolders (monthly, date folders)
    For Each subFolder In folder.SubFolders
        Call ScanFolder(subFolder.Path, ws)
    Next subFolder
End Sub

Key Customization Tips #

• Adjust Headers: Update the header lines (B1, C1, etc.) in the MergeProductionData sub to match the actual column names in your production files.
• Data Location: If your data isn’t in the first sheet of each file, change sourceWb.Sheets(1) to sourceWb.Sheets("Your Sheet Name").
• File Types: If you use .xlsm files, add Or LCase(fileSystem.GetExtensionName(file.Name)) = "xlsm" to the file type check.

Quick Note on Power Query (No-Code Alternative) #

If you ever want to try a no-code approach later:

1. Go to the Data tab → Get Data → From File → From Folder
2. Select your root folder, then click Transform Data
3. Add custom columns to extract production line, month, and date from the file path
4. Use Combine Files to load all data into a single table
5. Load the merged data back into Excel

But the VBA script above is perfect for your current needs—just test it on a copy of your data first to be safe!

内容的提问来源于stack exchange，提问作者hopeful_positive