修改Access VBA导出脚本，实现用户选择Excel输出目录

Here's a modified version of your VBA code that replaces the fixed C:\temp directory with a user-selectable folder. We'll use Access's built-in FileDialog object to let users pick their desired output location:

Private Sub Command3_Click()
    Dim fd As FileDialog
    Dim selectedPath As String
    Dim fullExportPath As String
    
    ' Create a folder picker dialog
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    
    With fd
        .Title = "Select Output Directory for Excel Export"
        ' Show the dialog and check if user selected a folder
        If .Show = -1 Then
            ' Get the selected folder path
            selectedPath = .SelectedItems(1)
            
            ' Ensure the path ends with a backslash to avoid filename issues
            If Right(selectedPath, 1) <> "\" Then
                selectedPath = selectedPath & "\"
            End If
            
            ' Combine the selected path with your fixed filename
            fullExportPath = selectedPath & "text.xlsx"
            
            ' Execute the export (same as your original code, but with dynamic path)
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Fields", _
                fullExportPath, True
                
            MsgBox "Export completed successfully! File saved to:" & vbCrLf & fullExportPath, vbInformation
        Else
            ' User clicked Cancel
            MsgBox "Export cancelled by user.", vbExclamation
        End If
    End With
    
    ' Clean up the dialog object
    Set fd = Nothing
End Sub

Key Details: #

• Folder Picker Dialog: msoFileDialogFolderPicker specifically lets users choose a directory (not a file), which matches your requirement.
• Path Handling: We add a check to ensure the selected path ends with a backslash (\)—this prevents messy combinations like C:\MyFoldertext.xlsx if the user's selected path doesn't already have a trailing slash.
• Error Prevention: We handle the case where the user clicks "Cancel" by showing a clear message and exiting the procedure, avoiding unexpected runtime errors.
• Confirmation Message: Added a success popup to let the user know exactly where their exported file was saved.

Just replace your existing Command3_Click code with this version, and it'll work as expected.

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