VBA Save entire sheet to pdf. Prompt for location

[ScousePete]

I'd like to have the entire sheet saved to pdf, with a prompt for the user to select the location.

I have found similar scripts that save the sheet, but not one that prompts for location

Thanks

 

[Fluff]

Do you just want a prompt for the location or the file name as well?

 

[ScousePete]

file name can be preset (or the workbookname), just the file location should prompt

 

[Fluff]

Ok how about

Sub ScousePete()
   Dim Fldr As String
   
   With Application.FileDialog(4)
      .AllowMultiSelect = False
      If .Show Then Fldr = .SelectedItems(1)
   End With
   With ActiveSheet
      .ExportAsFixedFormat xlTypePDF, Fldr & "\" & .Name, , , 1, , , 0
   End With
End Sub

This will save the active sheet as a pdf

 

[ScousePete]

The sheet is locked, if that is relevant

 

[Fluff]

You will not get that error with the code I provided. Did you copy/paste the code or type it out?

Also what version of Excel & what platform are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[ScousePete]

copy/paste.

I have updated my info.
Excel 16.55. OSX

 

[Fluff]

Ok, the code I posted does not work on a Mac. Have a look here for how to select a folder Select Folder | Mac Excel Automation

 

[ScousePete]

Thanks. Is there a way to incorporate that code into your code?

 

[Fluff]

You can try

Sub Select_Folder_On_Mac()
    Dim FolderPath As String
    Dim RootFolder As String
    Dim Scriptstr As String

    On Error Resume Next
    
    'Enter the Start Folder, Desktop in this example,
    'Use the second line to enter your own path
    RootFolder = MacScript("return POSIX path of (path to desktop folder) as String")
    
    'RootFolder = "/Users/rondebruin/Desktop/TestFolder/"
    
    'Make the path Colon seperated for using in MacScript
    RootFolder = MacScript("return POSIX file (""" & RootFolder & """) as string")
    'Make the Script string
        Scriptstr = "return POSIX path of (choose folder with prompt ""Select the folder""" & _
            " default location alias """ & RootFolder & """) as string"
    
    'Run the Script
    FolderPath = MacScript(Scriptstr)
    On Error GoTo 0
    
    If FolderPath <> "" Then
      With ActiveSheet
         .ExportAsFixedFormat xlTypePDF, Fldr & Application.PathSeparator & .Name, , , 1, , , 0
      End With
    End If
End Sub

but I have no idea if it works as I don't have a Mac.