CODEWITHSUNDEEP
excelvba
TheFoxHasRisen
TheFoxHasRisen

Reputation: 5

Save file in chosen file path

This code asks the user to select a file, then to select the file path to save the PDFs the macro will create.

The issue is in the part where it asks to user to choose the file path.

For instance, let's say the user selected "C:\Users\Tom.James\Desktop\Tom\Macros".
The PDFs get saved in "C:\Users\Tom.James\Desktop\Tom".

I assume the issue is with the "-1" part in that area of the code.

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

Upvotes: 0

Views: 496

Answers (1)

Toddleson
Toddleson

Reputation: 4457

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.

Upvotes: 1

Related Questions