基于文件名列表批量导入工作表的VBA代码修改需求

I’ll help you revamp your VBA code to handle batch imports from a dynamic filename list starting at cell A6, plus add proper error handling for missing files. Here’s the revised code and a breakdown of how it works:

Sub BatchImportSheets()
    Dim ws As Worksheet
    Dim fullFilePath As String
    Dim currentFileNameCell As Range
    Dim fixedFolderPath As String
    
    ' Set your fixed folder path here (easy to update later)
    fixedFolderPath = "E:\MyFolder\Manipulated Data\"
    
    ' Start from cell A6 on the first sheet (adjust sheet name if needed)
    Set currentFileNameCell = ThisWorkbook.Sheets(1).Range("A6")
    
    ' Loop through all non-empty cells in column A starting at A6
    Do While currentFileNameCell.Value <> ""
        ' Combine folder path, filename from cell, and .csv extension
        fullFilePath = fixedFolderPath & currentFileNameCell.Value & ".csv"
        
        ' Check if the file exists before trying to import
        If Dir(fullFilePath) <> "" Then
            ' Import the CSV as a new worksheet
            Sheets.Add Type:=fullFilePath
            ' Move the new sheet to the end of the workbook
            ActiveSheet.Move After:=Worksheets(Worksheets.Count)
            ' Mark success in column B
            currentFileNameCell.Offset(0, 1).Value = "Imported Successfully"
        Else
            ' File not found: add error message in column B
            currentFileNameCell.Offset(0, 1).Value = "Unable to Locate File"
        End If
        
        ' Move to the next row in column A
        Set currentFileNameCell = currentFileNameCell.Offset(1, 0)
    Loop
End Sub

Key Updates & Explanations: #

• Dynamic List Support: Uses a Do While loop to iterate through cells starting at A6 until it hits an empty cell—this works with any length of filename list, no need to set a fixed range.
• Error Prevention: The Dir() function checks if the file exists before attempting to import it, which avoids annoying runtime errors from missing files.
• Status Tracking: Writes clear feedback in column B for each row, so you can quickly see which files imported successfully and which didn’t.
• Maintainable Path: The folder path is stored in a top-level variable fixedFolderPath, making it simple to update if your folder location changes later.
• Consistent Sheet Placement: Keeps your original logic of moving imported sheets to the end of the workbook.

Quick Notes: #

• If your filename list is on a specific sheet (not the first one), replace ThisWorkbook.Sheets(1) with the actual sheet name, e.g., ThisWorkbook.Sheets("FileList").
• Ensure the filenames in column A don’t already include the .csv extension—the code adds it automatically.

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