VBA按部分文件名导入指定CSV文件的问题求助

It sounds like you’re stuck adapting your fixed-filename CSV import to handle multiple files that share a common prefix (Newdata_Files_LMBN_) and have varying numeric endings. The key here is using VBA’s Dir function to find all files matching your pattern, then looping through each one to import.

Here’s a revised version of your code that should work, with explanations of the critical parts:

Sub zzandand(Optional opt As String)
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False ' Optional: Suppress overwrite prompts
    
    Dim compd1 As String, compd2 As String ' Explicitly declare variable types (fix from your original code)
    Dim ws As Worksheet
    Dim targetPath As String
    Dim fileName As String
    
    ' Set your target directory here (ensure it ends with a backslash)
    targetPath = "C:\Your\CSV\File\Path\" ' Replace with your actual file path
    
    ' Get the first file matching the prefix pattern
    fileName = Dir(targetPath & "Newdata_Files_LMBN_*.csv")
    
    ' Loop through all matching CSV files
    Do While fileName <> ""
        ' Create a new worksheet for each imported CSV
        Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        
        ' Import the CSV data using QueryTables
        With ws.QueryTables.Add(Connection:="TEXT;" & targetPath & fileName, Destination:=ws.Range("A1"))
            .TextFileParseType = xlDelimited
            .TextFileCommaDelimiter = True ' Adjust if your CSV uses tabs/other delimiters
            .Refresh BackgroundQuery:=False
            .Delete ' Optional: Remove query table after import to clean up
        End With
        
        ' Optional: Rename the worksheet to match the CSV file (minus .csv extension)
        ws.Name = Left(fileName, Len(fileName) - 4)
        
        ' Fetch the next matching file
        fileName = Dir
    Loop
    
    ' Re-enable Excel features
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    MsgBox "All matching CSV files imported successfully!", vbInformation
End Sub

Key Fixes & Explanations: #

• Dir Function: Dir(targetPath & "Newdata_Files_LMBN_*.csv") finds the first file matching your prefix pattern. Calling Dir again without arguments retrieves the next matching file until none are left.
• Explicit Variable Declarations: Your original code had Dim compd1, compd2 As String which only declared compd2 as a String—compd1 would default to a Variant. I corrected this to ensure both variables are typed properly.
• Loop Through Files: The Do While loop ensures every matching CSV is processed, no matter how many there are.
• CSV Import: Using QueryTables is a reliable way to pull CSV data into Excel. Adjust the delimiter setting (e.g., .TextFileTabDelimiter = True) if your CSV uses tabs instead of commas.
• User Experience: Disabling screen updating and alerts makes the process faster and less distracting—just remember to re-enable them at the end.

Additional Tips: #

• If you want to import all files into a single worksheet (appending data), modify the code to find the next empty row instead of creating a new sheet each time.
• Add error handling with On Error Resume Next or On Error GoTo to catch issues like missing files or permission errors.
• Double-check that your targetPath ends with a backslash (\) to avoid path concatenation bugs.

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