尝试复制文件时出现Type mismatch error的问题求助

Hey there! Let’s dig into that type mismatch error you’re hitting on the line Set fils = fso.GetFolder(filOrigin).Files. Since your code works when you hardcode paths, the issue almost certainly lies in how you’re pulling the source path from your worksheet cells during the loop. Let’s break down the fixes step by step.

Common Causes of the Error #

1. Your filOrigin variable isn’t a valid string path: Cells might contain hidden spaces, empty values, or formula results that aren’t plain text.
2. Missing validation checks: You’re not verifying if the path exists or is non-empty before passing it to GetFolder.
3. Inconsistent variable initialization: Even if you initialized fso correctly, the loop might be passing a non-string value (like a numeric error or empty variant) to GetFolder.

Corrected Code Example #

Here’s a revised version of your code with safeguards and fixes to eliminate the type mismatch:

Sub BatchCopyFiles()
    Dim fso As Object
    Dim sourceFiles As Object
    Dim singleFile As Object
    Dim targetSheet As Worksheet
    Dim lastRow As Long
    Dim currentRow As Long
    Dim sourcePath As String
    Dim destPath As String
    
    ' Initialize FileSystemObject (late binding, no references needed)
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    ' Set your target worksheet (update to your sheet name)
    Set targetSheet = ThisWorkbook.Worksheets("FilePaths")
    
    ' Find the last row with data in column A (source paths)
    lastRow = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each row (start at 2 if row 1 is a header)
    For currentRow = 2 To lastRow
        ' Extract paths, trim extra spaces, and force to string type
        sourcePath = CStr(Trim(targetSheet.Cells(currentRow, "A").Value))
        destPath = CStr(Trim(targetSheet.Cells(currentRow, "B").Value))
        
        ' Skip rows with empty paths
        If sourcePath = "" Or destPath = "" Then
            MsgBox "Skipping row " & currentRow & ": Empty source/destination path", vbExclamation
            GoTo NextRow
        End If
        
        ' Verify source folder exists
        If Not fso.FolderExists(sourcePath) Then
            MsgBox "Source folder not found: " & sourcePath, vbCritical
            GoTo NextRow
        End If
        
        ' Create destination folder if it doesn't exist
        If Not fso.FolderExists(destPath) Then
            fso.CreateFolder destPath
        End If
        
        ' This line should now work without type mismatch
        Set sourceFiles = fso.GetFolder(sourcePath).Files
        
        ' Copy each file to the destination
        For Each singleFile In sourceFiles
            ' Overwrite existing files (set to False if you want to skip duplicates)
            singleFile.Copy Destination:=destPath & "\", OverWriteFiles:=True
        Next singleFile
        
NextRow:
    Next currentRow
    
    MsgBox "Batch file copy completed!", vbInformation
    ' Clean up objects
    Set fso = Nothing
    Set targetSheet = Nothing
End Sub

Key Fixes Explained #

• CStr(Trim(...)): Ensures the path from the cell is a clean string, removing any accidental leading/trailing spaces that might break GetFolder.
• Validation checks: We skip empty rows and warn you if the source folder doesn’t exist, preventing invalid values from reaching the error-prone line.
• Late binding: Using CreateObject("Scripting.FileSystemObject") avoids needing to enable the "Microsoft Scripting Runtime" reference, making the code more portable.
• Clear variable names: Renamed variables like fils to sourceFiles for readability—this helps catch typos that might cause unexpected errors.

Additional Troubleshooting Tips #

• If your cells use formulas to generate paths, try using .Value2 instead of .Value to pull the raw result (avoids formatting issues).
• Double-check that your paths don’t contain special characters or unescaped spaces (though Trim should handle most space issues).
• For network paths (e.g., \\ServerName\SharedFolder), ensure you have read access to the source and write access to the destination.

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