Help with Saving Spreadsheet as PDF using VBA

[Tyrell]

Hey y'all,

For a work project I am attempting to save a spreadsheet as a pdf in a specific folder but I now that I have figured out how to navigate to that folder (or create it if it doesn't already exist), I keep getting a fail to print error for an undefined object. Due to the nature of my work I cannot post the spreadsheet I am working from, but I can point out in which line the code stops working when I go "step" through it in excel.

I am using excel 2016 for Mac.

Much Thanks

Sub SaveCheckRequestPDFIn2016FromAuditSummary()


    Dim FileName2 As String
    Dim FolderName2 As String
    Dim Folderstring2 As String
    Dim FilePathName2 As String


    ActiveWorkbook.Sheets("Audit Summary 10% Cap").PageSetup.Orientation = xlPortrait

    FileName2 = ActiveWorkbook.Sheets("Audit Summary 10% Cap").Range("N3") & "_2016_WCAudit" & ".pdf"


    Folderstring2 = CreateFolderinMacOffice2016(NameFolder:="/")
    FilePathName2 = Folderstring2 & Application.PathSeparator & FileName2


    ActiveWorkbook.Sheets("Sheet1").Range("A1:J75").ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
    FilePathName2, Quality:=xlQualityStandard, _ --> THIS IS WHERE THE CODE STOPS WORKING AND I GET THE FOLLOWING ERROR 

Run-time error '1004':Application-defined or object-defined error


    IncludeDocProperties:=True, IgnorePrintAreas:=False

    MsgBox "You can find the PDF file in this location : " & FilePathName2


End Sub






Function CreateFolderinMacOffice2016(NameFolder As String) As String


    Dim OfficeFolder As String
    Dim PathToFolder As String
    Dim TestStr As String
    Dim FolderName2 As String


    FolderName2 = ActiveWorkbook.Sheets("Audit Summary 10% Cap").Range("N3") & "_Workers Compensation"


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


    PathToFolder = OfficeFolder & NameFolder


    On Error Resume Next
    TestStr = Dir(PathToFolder, vbDirectory)
    On Error GoTo 0
    If TestStr = vbNullString Then
        MkDir PathToFolder
        MsgBox "You can find the new folder in this location :" & PathToFolder
    End If
    CreateFolderinMacOffice2016 = PathToFolder
End Function

 

[Jim Gordon]

See this page from Ron DeBruin
https://www.rondebruin.nl/mac/mac005.htm