从多工作簿汇总数据至主工作簿：单元格路径的VBA实现问题

Hey there! I see you're dipping your toes into VBA and building a data import tool—great work taking on this project! Let's walk through your current code, spot the kinks in folder path handling and loop logic, then fix it up so it runs reliably.

First, Let's Break Down the Key Issues in Your Current Code #

• Uninitialized i for path retrieval: You set strPath = Cells(i, 6).Value before the For i = 7 To 37 loop starts. At that point, i defaults to 0, so you're pulling from a non-existent cell F0 and strPath starts empty. That breaks your loop right out the gate.
• Incorrect Dir usage: You define strExtension = Dir("*.xls*") once at the top, which looks for files in Excel's default working directory—not the folders listed in your F7:F37 cells. You need to run Dir inside each folder loop to target the right directory.
• Confused loop structure: The Do While strPath <> "" loop is misplaced. Your goal is to process each folder in F7:F37, then grab all Excel files in that folder. The current structure mixes up folder paths and file paths, leading to messy, unexpected behavior.
• Unnecessary ChDir: Using ChDir can cause bugs if your folders are on different drives. It's safer to use full file paths directly instead of changing the working directory.

Revised Working Code #

Here's a fixed version of your macro with comments explaining key changes:

Sub ImportRAGData()
    ' Turn off screen updating to speed up the macro
    Application.ScreenUpdating = False
    
    Dim i As Integer, targetCol As Integer
    Dim wkbDest As Workbook, wkbSource As Workbook
    Dim folderPath As String, fileName As String
    
    ' Set destination workbook to the one running this macro
    Set wkbDest = ThisWorkbook
    ' Start pasting data in column K (column 11)
    targetCol = 11
    
    ' Loop through each folder path in cells F7 to F37
    For i = 7 To 37
        ' Get the folder path from current cell (column F = 6), with explicit sheet reference
        folderPath = Trim(wkbDest.Sheets("RAG Raw Data").Cells(i, 6).Value)
        
        ' Skip empty cells in F7:F37 to avoid errors
        If folderPath <> "" Then
            ' Make sure the folder path ends with a backslash to avoid broken file paths
            If Right(folderPath, 1) <> "\" Then
                folderPath = folderPath & "\"
            End If
            
            ' Find the first Excel file in the target folder
            fileName = Dir(folderPath & "*.xls*")
            
            ' Loop through all Excel files in the current folder
            Do While fileName <> ""
                ' Open the source workbook with full path
                Set wkbSource = Workbooks.Open(folderPath & fileName)
                
                ' Copy values from "ALL RAGs" sheet E3:E236 to destination sheet
                wkbSource.Sheets("ALL RAGs").Range("E3:E236").Copy
                wkbDest.Sheets("RAG Raw Data").Cells(7, targetCol).PasteSpecial xlPasteValues
                
                ' Clean up copy mode and close source workbook without saving
                Application.CutCopyMode = False
                wkbSource.Close savechanges:=False
                
                ' Get the next file in the folder
                fileName = Dir
            Loop
            
            ' Move to the next column for the next folder's data
            targetCol = targetCol + 1
        End If
    Next i
    
    ' Turn screen updating back on and notify user
    Application.ScreenUpdating = True
    MsgBox "Data import complete!", vbInformation
End Sub

Key Improvements Explained #

• Explicit sheet references: I added wkbDest.Sheets("RAG Raw Data") when reading folder paths to avoid relying on the active sheet (which can cause bugs if you switch sheets mid-macro).
• Empty cell check: Skips any blank cells in F7:F37 so the macro doesn't waste time on invalid paths.
• Backslash handling: Ensures the folder path always ends with a backslash, so combining it with a file name doesn't create broken paths like C:\Folderfile.xlsx.
• Clear loop separation: First loops through each folder, then loops through each file in that folder—matches your intended workflow perfectly.
• User feedback: Added a message box at the end to let you know when the import finishes.

Quick Tips for Future VBA Projects #

• Always use explicit references for workbooks and sheets (avoid ActiveWorkbook or ActiveSheet unless you specifically need them).
• Add error handling (e.g., On Error Resume Next or On Error GoTo) to handle cases where a folder doesn't exist or a sheet is missing.
• Test with a small subset of folders first (e.g., For i =7 To 9) to debug faster.

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