PDF | Mac Excel Automation

Make and Mail PDF files with VBA code on your Mac

Save as PDF in Mac Excel VBA examples

Below you can find example VBA code to save as PDF in Mac Excel. Microsoft fixed a few bugs but there are still a few of bugs to fix, so I will update the code when needed. The code will create a folder named PDFSaveFolder in the Office folder to save the PDF files in, read the info on this page why I use a folder in that location. Check out this page if you want an easy way to open this folder in finder : Setup your Mac for Mac Office

If you want to create a PDF and also mail it check out this two pages :
Send Mail from Mac Excel with Mac Mail
Send Mail from Mac Excel with Mac Outlook

NOTE: Be aware that if you use VBA code to make a PDF in Mac Excel you are not able to print to paper after it anymore with VBA code like: ActiveSheet.PrintOut, I have reported this bug a long time ago, but it is still not fixed.

'Note: the 4 macros below use the CreateFolderinMacOffice function that is on the bottom of this module

Sub SaveActiveWorkbookAsPDFInMacExcel()
    'Ron de Bruin : 11-Dec-2020
    'Test macro to save the ActiveWorkbook as pdf with ExportAsFixedFormat
    'If set it save the printareas. All sheets seems to follow the orientation of the activesheet(Bug)
    Dim FileName As String
    Dim FolderName As String
    Dim Folderstring As String
    Dim FilePathName As String

    'If my ActiveSheet is landscape, I must attach this line
    'for making the PDF also landscape, seems to default to xlPortait
    ActiveSheet.PageSetup.Orientation = ActiveSheet.PageSetup.Orientation

    'Name of the folder in the Office folder
    FolderName = "PDFSaveFolder"
    'Name of the pdf file
    FileName = ActiveWorkbook.Name & " " & Format(Now, "dd-mmm-yyyy hh-mm-ss") & ".pdf"

    Folderstring = CreateFolderinMacOffice(NameFolder:=FolderName)
    FilePathName = Folderstring & Application.PathSeparator & FileName

    'expression A variable that represents a Workbook, Sheet, Chart, or Range object.
    'the parameters are not working like in Excel for Windows
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
    FilePathName, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False
  
    MsgBox "You find the PDF file in this location : " & FilePathName
End Sub


Sub SaveActiveSheetAsPDFInMacExcel()
    'Ron de Bruin : 11-Dec-2020
    'Test macro to save the ActiveSheet as pdf with ExportAsFixedFormat
    'Note : if set it save the printarea
    Dim FileName As String
    Dim FolderName As String
    Dim Folderstring As String
    Dim FilePathName As String

    'If my ActiveSheet is landscape, I must attach this line
    'for making the PDF also landscape, seems to default to xlPortait
    ActiveSheet.PageSetup.Orientation = ActiveSheet.PageSetup.Orientation

    'Name of the folder in the Office folder
    FolderName = "PDFSaveFolder"
    'Name of the pdf file
    FileName = ActiveSheet.Name & " " & Format(Now, "dd-mmm-yyyy hh-mm-ss") & ".pdf"

    Folderstring = CreateFolderinMacOffice(NameFolder:=FolderName)
    FilePathName = Folderstring & Application.PathSeparator & FileName

    'expression A variable that represents a Workbook, Sheet, Chart, or Range object.
    'the parameters are not working like in Excel for Windows
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
    FilePathName, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False
  
    MsgBox "You find the PDF file in this location : " & FilePathName
End Sub


Sub SaveRangeAsPDFInMacExcel()
    'Ron de Bruin : 11-Dec-2020
    'Test macro to save the Range A1:C20 as pdf with ExportAsFixedFormat
    Dim FileName As String
    Dim FolderName As String
    Dim Folderstring As String
    Dim FilePathName As String

    'If my ActiveSheet is landscape, I must attach this line
    'for making the PDF also landscape, seems to default to xlPortait
    ActiveSheet.PageSetup.Orientation = ActiveSheet.PageSetup.Orientation

    'Name of the folder in the Office folder
    FolderName = "PDFSaveFolder"
    'Name of the pdf file
    FileName = ActiveSheet.Name & " Range " & Format(Now, "dd-mmm-yyyy hh-mm-ss") & ".pdf"

    Folderstring = CreateFolderinMacOffice(NameFolder:=FolderName)
    FilePathName = Folderstring & Application.PathSeparator & FileName

    'expression A variable that represents a Workbook, Sheet, Chart, or Range object.
    'the parameters are not working like in Excel for Windows
    Range("A1:C20").ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
    FilePathName, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False
  
    MsgBox "You find the PDF file in this location : " & FilePathName
End Sub


Sub PublishEachWorkSheetToPDFInMacExcel()
    'Ron de Bruin : 11-Dec-2020
    'Test macro to publish each worksheet to pdf with ExportAsFixedFormat
    'Note : if set it save the printarea
    'It will create a new folder for you with the files
    Dim FolderName As String
    Dim Folderstring As String
    Dim Fstr As String
    Dim TestStr As String
    Dim sh As Worksheet
    Dim FileName As String
    Dim FilePathName As String

    'Name of the Root folder in the Office folder, and create the folder
    FolderName = "PDFSaveFolder"
    Folderstring = CreateFolderinMacOffice(NameFolder:=FolderName)

    'Create folder in the Root folder with the name of the ActiveWorkbook
    Fstr = Mid(ActiveWorkbook.Name, 1, InStrRev(ActiveWorkbook.Name, ".", , 1) - 1) & Format(Now, " dd-mmm-yyyy hh-mm-ss")
    On Error Resume Next
    TestStr = Dir(Folderstring & "/" & Fstr, vbDirectory)
    On Error GoTo 0
    If TestStr = vbNullString Then MkDir Folderstring & "/" & Fstr

    'Loop through all worksheets
    For Each sh In ActiveWorkbook.Worksheets
        'If the sheet is visible then publish it to PDF
        If sh.Visible = -1 Then
            sh.PageSetup.Orientation = sh.PageSetup.Orientation

            'File name is the sheet name and a date/time stamp
            FileName = sh.Name & " " & Format(Now, "dd-mmm-yyyy hh-mm-ss") & ".pdf"

            'Publish the Worksheet to pdf
            FilePathName = Folderstring & Application.PathSeparator & Fstr & Application.PathSeparator & FileName
            'expression A variable that represents a Workbook, Sheet, Chart, or Range object.
            'the parameters are not working like in Excel for Windows
            sh.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
            FilePathName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False
        End If
        Next sh
        
    MsgBox "You find the PDF files in this location : " & Folderstring & "/" & Fstr
End Sub


Function CreateFolderinMacOffice(NameFolder As String) As String
    'Function to create folder if it not exists in the Microsoft Office Folder
    'Ron de Bruin : 13-July-2020
    Dim OfficeFolder As String
    Dim PathToFolder As String
    Dim TestStr As String

    OfficeFolder = MacScript("return POSIX path of (path to desktop folder) as string")
    OfficeFolder = Replace(OfficeFolder, "/Desktop", "") & _
        "Library/Group Containers/UBF8T346G9.Office/"

    PathToFolder = OfficeFolder & NameFolder

    On Error Resume Next
    TestStr = Dir(PathToFolder & "*", vbDirectory)
    On Error GoTo 0
    If TestStr = vbNullString Then
        MkDir PathToFolder
        'You can use this msgbox line for testing if you want
        'MsgBox "You find the new folder in this location :" & PathToFolder
    End If
    CreateFolderinMacOffice = PathToFolder
End Function