VBA宏导出PDF无法保存到用户所选目标路径问题求助

问题根因 #

你猜测的文件夹选择对话框返回值-1判断逻辑不存在问题，Application.FileDialog.Show方法返回-1代表用户点击了确认按钮，返回0代表用户点击了取消，这部分逻辑完全正确。
真正的问题是：你将用户选择的保存路径存入sFolderPathForSave变量后，调用ExportAsFixedFormat方法导出PDF时，完全没有将该路径拼接到文件名参数中，Excel会默认使用当前活动工作簿的所在路径作为保存根路径，最终导致PDF文件没有保存到你指定的目录下。

修正后的完整代码 #

With Application.FileDialog(msoFileDialogFilePicker)
    '限制用户仅能选择单个文件
    .AllowMultiSelect = False
    '设置仅显示Excel格式文件
    .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
    .Show
    fullpath = .SelectedItems.Item(1)
    Workbooks.Open fullpath
End With

Dim dlgSaveFolder As FileDialog
Dim sFolderPathForSave As String

'打开文件夹选择对话框
Set dlgSaveFolder = Application.FileDialog(msoFileDialogFolderPicker)
With dlgSaveFolder
    .Title = "选择保存PDF的文件夹"
    .AllowMultiSelect = False
    .InitialFileName = ThisWorkbook.Path & "\"
    If .Show <> -1 Then GoTo CancelFolderSelection
    sFolderPathForSave = .SelectedItems(1)
End With
Set dlgSaveFolder = Nothing

'统一处理路径末尾的反斜杠，避免拼接错误
If Right(sFolderPathForSave, 1) <> "\" Then
    sFolderPathForSave = sFolderPathForSave & "\"
End If

'导出PDF时拼接选中的保存路径
Sheets("Balance Sheet").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
  sFolderPathForSave & "02 - Balance Sheet", Quality:= _
  xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
  OpenAfterPublish:=False

Sheets("Reserve Statement").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
  sFolderPathForSave & "03 - Statement of Reserves", Quality:= _
  xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
  OpenAfterPublish:=False

Sheets("Income Statement").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
  sFolderPathForSave & "04 - Income Statement", Quality:= _
  xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
  OpenAfterPublish:=False

'补充取消文件夹选择后的退出逻辑标签，避免跳转报错
CancelFolderSelection:
If Not dlgSaveFolder Is Nothing Then Set dlgSaveFolder = Nothing

修改说明 #

1. 保留了原有文件夹选择的返回值判断逻辑，无需调整
2. 新增了路径末尾反斜杠的统一处理逻辑，避免不同场景下路径格式不一致导致的拼接错误
3. 所有导出PDF的文件名参数前都拼接了用户选中的目标文件夹路径，确保文件保存到指定位置
4. 补充了取消文件夹选择后的跳转标签和资源释放逻辑，避免代码运行时出现未定义标签的报错

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