Export Worksheet as pdf on MAC

[BritsBlitz]

I have the following Macro that will export a worksheet as a pdf using "Test_Summary_MMDDYYY" as the file name. The macro will save the pdf correctly, but it always saves it in the "downloads" folder on my MAC, instead of the destination folder I selected.

If I select the Desktop as the destination folder and I look at the value of saveSummary while the macro is running, it correctly shows the information as "Macintosh HD:Users:BritsBlitz:Desktop:Test_Summary_06052024.pdf", but it will still save the pdf in the Downloads folder instead of saving it to the desktop.

Any suggestions on what needs to be changed so that the pdf is actually saved in the destination folder?

Sub SavePDF()
 
Dim saveSummary As String
Dim saveFileName As String
Dim CurrentDate As String
Dim DestinationFolder As String

CurrentDate = Format(Date, "MMDDYYYY")
saveFileName = "Test"

[COLOR=rgb(0, 0, 0)]DestinationFolder = MacScript("(choose folder with prompt ""Select the folder"") as String")
saveSummary = DestinationFolder & saveFileName & "_Summary_" & CurrentDate & ".pdf"[/COLOR]

Worksheets("SUMMARY SHEET").ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveSummary
MsgBox ("The PDF has been saved")

End Sub

 

[Tetra201]

This works OK in Excel 2011 v.14.7.7 under OS X 10.9.5.

 

[BritsBlitz]

This works OK in Excel 2011 v.14.7.7 under OS X 10.9.5.

Thanks Tetra201.

I'm running MS Excel for Mac v. 16.85 under OS X 14.5

Seems like the MacScript command doesn't work as it should in later OS versions (according to my google searches...).

 

[BritsBlitz]

I found the solution through trial an error. If you have a later version of Excel and OS, the code is slightly different. Below is the final code which now works on all versions:

Sub SavePDF()

Dim saveSummary As String
Dim saveFileName As String
Dim CurrentDate As String
Dim RootFolder As String
Dim scriptstr As String
Dim folderPath As String

CurrentDate = Format(Date, "MMDDYYYY")
saveFileName = "Test"

RootFolder = MacScript("return (path to desktop folder) as String")
If Val(Application.Version) < 15 Then
     scriptstr = "(choose folder with prompt ""Select the folder""" & " default location alias """ & RootFolder & """) as string"
Else
     scriptstr = "return posix path of (choose folder with prompt ""Select the folder""" & " default location alias """ & RootFolder & """) as string"
End If

folderPath = MacScript(scriptstr)
On Error GoTo 0

saveSummary = folderPath & saveFileName & "_Summary_" & CurrentDate & ".pdf"

Worksheets("SUMMARY SHEET").ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveSummary
MsgBox ("The PDF has been saved")

End Sub