VBA Code for saving as PDF on PC and MAC

[terryjmatthews84]

Hi guys, I'm new to the forum and forums altogether so excuse any mistakes, I've read the rules so fingers crossed.

I have a macro which enables me to save the current worksheet as a PDF using a couple of cells for the file name and saving to my business drop box location however, this does not work on the MAC.

With employees using PC's and MAC's, I wondered if there was a code I can use which will work on both or, a code that will work on the MAC and I can just add another button in the sheet for MAC users. The code that works on the PC is as follows:

Sub PDFPO()
With ActiveSheet
.ExportAsFixedFormat 0, Environ("USERPROFILE") & "\Dropbox (TJM)\TJM Team Folder\TM\TJM Filing Structure\6. Financial Management\1. TJM\4. Financial Workbook\Purchase Orders OUT\" & _
.Range("L13").Value & "_" & .Range("W2").Value & "_" & Format(Date, "dd-mm-yyyy") & ".pdf", OpenAfterPublish:=True
End With
End Sub

The above code saves the worksheet as PDF under the file name given in cells L13 and W2 with spaces and todays date. It then saves the file in the location "Purchase Orders OUT".

Any help would be greatly appreciated.

Thank you

 

[Tetra201]

Give a try to the following sub. It has been tested to work on both Win Excel 2010 and Mac Excel 2011. Modify the "Desktop" and "Test" in the file path as needed.
Note: OpenAfterPublish:=True has no effect on Mac.

Sub PDFPO()
    Dim FileName As String
    #If Mac Then
        FileName = MacScript("return (path to home folder) as string") & "Desktop:Test:"
    #Else
        FileName = Environ("USERPROFILE") & "\Desktop\Test\"
    #End If
    With ActiveSheet
        FileName = FileName & .Range("L13").Value & "_" & .Range("W2").Value & "_" & Format(Date, "dd-mm-yyyy") & ".pdf"
        .ExportAsFixedFormat 0, FileName, OpenAfterPublish:=True
    End With
End Sub

 

[Jon Peltier]

All kinds of VBA stuff used to work in Excel 2011 on the Mac, including file operations like this.

Excel 2016+ on the Mac is a totally different beast. Apple makes software (i.e., Microsoft Office) run within a sandboxed area to prevent malware to run amok. Safe, but it makes it very difficult to work with the file system. I would suggest looking at Ron de Bruin's Mac Excel Tips.