VBA File Save Path Issue-CodePudding

I have this code that asks the user to select a file, it then asks the user to select the file path to save the PDF's the macro will create into. The issue with the code is in the part where it asks to user to choose the file path the save the PDF's into.

For instance, lets say the user selected this file path to save all the PDF's into "C:\Users\Tom.James\Desktop\Tom\Macros" the PDF's all get saved in the following file path "C:\Users\Tom.James\Desktop\Tom" I am not sure why it does not save them in the final part of the file path, but rather one spot prior. I assume the issue is with the "-1" part in that area of the code but I cannot quite troubleshoot it.

  With Application.FileDialog(msoFileDialogFilePicker)
    'Makes sure the user can select only one file
    .AllowMultiSelect = False
    'Filter to just the following types of files to narrow down selection options
    .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
    'Show the dialog box
    .Show
    'Store in fullpath variable
    fullpath = .SelectedItems.Item(1)
    Workbooks.Open fullpath
End With
Dim dlgSaveFolder As FileDialog
Dim sFolderPathForSave As String
'''
'Open a Folder picker dialog box.
Set dlgSaveFolder = Application.FileDialog(msoFileDialogFolderPicker)
With dlgSaveFolder
.Title = "Select a Folder to save the PDF's to"
.AllowMultiSelect = False
.InitialFileName = ThisWorkbook.Path & "\"
If .Show <> -1 Then GoTo CancelFolderSelection
sFolderPathForSave = .SelectedItems(1)
End With
Set dlgSaveFolder = Nothing
Sheets("Balance Sheet").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "02 - Balance Sheet", Quality:= _
    xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=False
Sheets("Reserve Statement").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "03 - Statement of Reserves", Quality:= _
    xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=False
Sheets("Income Statement").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "04 - Income Statement", Quality:= _
    xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

CodePudding user response：

From the documentation on WorkSheet.ExportAsFixedFormat : FileName - "...include a full path, or Excel saves the file in the current folder.".

To save in a target directory you just need to include that directory path in the FileName. Your code already saves this value in sFolderPathForSave so you just need to change all of your FileName arguments to include sFolderPathForSave

Filename:= sFolderPathForSave & "\02 - Balance Sheet"
Filename:= sFolderPathForSave & "\03 - Statement of Reserves"
Filename:= sFolderPathForSave & "\04 - Income Statement"

Also, in the documentation's example, they include the file extension in the FileName. So you may want to add ".pdf" if you are having issues with the file types not being applied properly.