Excel宏适配任意文件类型批量移动：从表格读取路径与文件名

Got it, let's tweak your macro to hit both of your requirements perfectly. Below is the updated code that handles any file type, pulls filenames/paths directly from your spreadsheet, and creates missing target folders automatically:

Sub MoveFiles()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim sourceFile As String
    Dim targetFolder As String
    Dim fso As Object
    
    ' Set the worksheet to use (change to your specific sheet name if needed)
    Set ws = ActiveSheet
    ' Initialize FileSystemObject for folder and file operations
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    ' Find the last row with data in column A (source files)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each row starting from row 2 (assuming row 1 is headers)
    For i = 2 To lastRow
        ' Pull source file path from column A, target folder from column B
        sourceFile = ws.Cells(i, "A").Value
        targetFolder = ws.Cells(i, "B").Value
        
        ' Skip rows with empty source or target fields
        If sourceFile = "" Or targetFolder = "" Then
            MsgBox "Row " & i & " has missing data — skipping.", vbExclamation
            GoTo NextRow
        End If
        
        ' Check if the source file actually exists
        If Not fso.FileExists(sourceFile) Then
            MsgBox "Source file not found: " & sourceFile & " (Row " & i & ")", vbCritical
            GoTo NextRow
        End If
        
        ' Create target folder if it doesn't exist
        If Not fso.FolderExists(targetFolder) Then
            fso.CreateFolder targetFolder
            MsgBox "Created new folder: " & targetFolder, vbInformation
        End If
        
        ' Move the file to the target folder
        On Error Resume Next
        fso.MoveFile sourceFile, targetFolder & "\"
        If Err.Number <> 0 Then
            MsgBox "Failed to move file: " & sourceFile & vbCrLf & "Error: " & Err.Description, vbCritical
        Else
            MsgBox "Successfully moved: " & sourceFile & " → " & targetFolder, vbInformation
        End If
        On Error GoTo 0
        
NextRow:
    Next i
    
    ' Clean up objects
    Set fso = Nothing
    Set ws = Nothing
    MsgBox "File move process finished!", vbInformation
End Sub

Key Updates & Breakdown: #

• Any File Type Support: We removed all hardcoded file extensions (like .csv). The macro now uses the full file path/name from column A, so it works for JPGs, PDFs, CSVs, or any other file you need to move.
• Dynamic Paths from Spreadsheet: No more hardcoded folder names—we read directly from column A (source file path) and column B (target folder path). The loop runs until the last row with data in column A, so you can add as many files as you want.
• Auto-Create Folders: Uses Scripting.FileSystemObject to check if the target folder exists. If it doesn't, the macro creates it on the fly—no manual folder setup required.
• Error Handling: Added checks for missing files, empty cells, and move failures, with clear message boxes to help you troubleshoot issues quickly.
• Flexible Worksheet: Uses ActiveSheet by default, but you can swap it out for a specific sheet by changing Set ws = ActiveSheet to Set ws = ThisWorkbook.Sheets("YourSheetName").

Quick Usage Steps: #

1. Set up your spreadsheet with:
   • Column A: Full path to the source file (e.g., C:\Downloads\vacation.jpg or C:\Docs\sales.csv)
   • Column B: Full path to the target folder (e.g., C:\Sorted\VacationPhotos or C:\Sorted\SalesReports)
2. Run the macro—it will process each row, create folders as needed, and move your files.

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