如何修改Excel VBA合并文件代码以指定文件夹路径？

Got it, let's adjust your mergeFiles macro so it launches the file picker directly in your target folder instead of the system default location. Here's the modified code with straightforward explanations:

Modified Working Code #

Sub mergeFiles()
    'Merges all files in a folder to a main file.
    'Define variables:
    Dim numberOfFilesChosen, i As Integer
    Dim tempFileDialog As FileDialog
    Dim mainWorkbook, sourceWorkbook As Workbook
    Dim tempWorkSheet As Worksheet
    ' --- ADD THIS TO SET YOUR DEFAULT FOLDER ---
    Const TARGET_FOLDER As String = "C:\Your\Custom\Folder\Path\" ' Update this to your actual folder
    ' -------------------------------------------
    
    Set mainWorkbook = Application.ActiveWorkbook
    Set tempFileDialog = Application.FileDialog(msoFileDialogFilePicker)
    
    ' Force the file picker to open at your specified folder
    tempFileDialog.InitialFileName = TARGET_FOLDER
    
    'Allow the user to select multiple workbooks
    tempFileDialog.AllowMultiSelect = True
    numberOfFilesChosen = tempFileDialog.Show
    
    'Loop through all selected workbooks
    For i = 1 To tempFileDialog.SelectedItems.Count
        'Open each workbook
        Workbooks.Open tempFileDialog.SelectedItems(i)
        Set sourceWorkbook = ActiveWorkbook
        
        'Copy each worksheet to the end of the main workbook
        For Each tempWorkSheet In sourceWorkbook.Worksheets
            tempWorkSheet.Copy after:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count)
        Next tempWorkSheet
        
        'Close the source workbook
        sourceWorkbook.Close
    Next i
End Sub

Key Changes Breakdown #

• Added a constant for easy path updates: Using Const TARGET_FOLDER lets you quickly change the default location later without digging through the rest of the code. Just swap the example path with your actual folder.
• Set the InitialFileName property: This is the magic line that tells the file picker to open directly at your specified folder. Make sure to include a trailing backslash (\) at the end of the path—this ensures it opens the folder itself, not tries to pre-select a file with that name.
• Preserved all your original merging logic: Everything you had working for combining files stays exactly the same; we only added the default folder behavior.

Quick Tips #

• If you want the default folder to match where your main workbook is saved, replace the constant with ThisWorkbook.Path & "\" instead of a hardcoded path.
• If the target folder doesn't exist, the picker will fall back to the system default—double-check your path spelling to avoid this!

内容的提问来源于stack exchange，提问作者Eric the Viking