Excel VBA code to work on Mac, Create PDF Function

Question

I have coded the following function. However, I cannot get it to work on office Mac. I am not sure of the procedure to find the EXP_PDF.DLL mac equivalent

Function Create_PDF(Myvar As Object, FixedFilePathName As String, _                    
OverwriteIfFileExist As Boolean, OpenPDFAfterPublish As Boolean) As String    

Dim FileFormatstr As String    
Dim FName As Variant

'Test If the Microsoft Add-in is installed    
If Dir(Environ("commonprogramfiles") & "\Microsoft Shared\OFFICE" _& Format(Val(Application.Version), "00") & "\EXP_PDF.DLL") <> "" Then
        If FixedFilePathName = "" Then            
           'Open the GetSaveAsFilename dialog to enter a file name for the pdf            
           FileFormatstr = "PDF Files (*.pdf), *.pdf"            
           FName = Application.GetSaveAsFilename("", filefilter:=FileFormatstr, _                                                                                       Title:="Create PDF")
            'If you cancel this dialog Exit the function            
            If FName = False Then Exit Function        
         Else            
            FName = FixedFilePathName        
        End If
        'If OverwriteIfFileExist = False we test if the PDF        
         'already exist in the folder and Exit the function if that is True        
        If OverwriteIfFileExist = False Then            
            If Dir(FName) <> "" Then Exit Function        
        End If

       'Now the file name is correct we Publish to PDF        
       On Error Resume Next        
       Myvar.ExportAsFixedFormat _                
                Type:=xlTypePDF, _                
                FileName:=FName, _                
                Quality:=xlQualityStandard, _                
                IncludeDocProperties:=True, _                
                IgnorePrintAreas:=False, _                
                OpenAfterPublish:=OpenPDFAfterPublish        
        On Error GoTo 0

        'If Publish is Ok the function will return the file name        
         If Dir(FName) <> "" Then Create_PDF = FName    

End If
End Function

score 2 · Answer 1 · edited May 23 '17 at 12:15

There is no need to check for the existence of that specific DLL, because under MacOS, PDF export support is native. Your code simply works if you remove the Add-in check and remove the FileFilter string:

Function Create_PDF(Myvar As Object, FixedFilePathName As String, _
OverwriteIfFileExist As Boolean, OpenPDFAfterPublish As Boolean) As String

Dim FileFormatstr As String
Dim FName As Variant

    If FixedFilePathName = "" Then
       'Open the GetSaveAsFilename dialog to enter a file name for the pdf
       FName = Application.GetSaveAsFilename("", Title:="Create PDF")
        'If you cancel this dialog Exit the function
        If FName = False Then Exit Function
     Else
        FName = FixedFilePathName
    End If
    'If OverwriteIfFileExist = False we test if the PDF
     'already exist in the folder and Exit the function if that is True
    If OverwriteIfFileExist = False Then
        If Dir(FName) <> "" Then Exit Function
    End If

   'Now the file name is correct we Publish to PDF
   On Error Resume Next
   Myvar.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=FName, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=OpenPDFAfterPublish
    On Error GoTo 0

    'If Publish is Ok the function will return the file name
     If Dir(FName) <> "" Then Create_PDF = FName

End Function

But GetSaveAsFilename is crippled on MacOS and does not allow filtering files by filetype. If you need to restrict users to a certain filetype, you can resort to AppleScript and do the following:

Function Create_PDF_Mac(Myvar As Object, FixedFilePathName As String, _
OverwriteIfFileExist As Boolean, OpenPDFAfterPublish As Boolean) As String

Dim FileFormatstr As String
Dim FName As Variant

    If FixedFilePathName = "" Then
       'Open the GetSaveAsFilename dialog to enter a file name for the pdf
       'FName = Application.GetSaveAsFilename("", ".PDF", Title:="Create PDF")

        On Error Resume Next
        ThePath = MacScript("return (path to documents folder) as String")

        TheScript = _
        "set applescript's text item delimiters to "","" " & vbNewLine & _
        "set theFile to (choose file name with prompt ""Save As File"" " & _
            "default name ""untitled.pdf"" default location alias """ & _
            ThePath & """ ) as string" & vbNewLine & _
        "if theFile does not end with "".pdf"" then set theFile to theFile & "".pdf"" " & vbNewLine & _
        "set applescript's text item delimiters to """" " & vbNewLine & _
        "return theFile"

           FName = MacScript(TheScript)
        On Error GoTo 0

        'If you cancel this dialog Exit the function
        If FName = False Then Exit Function
     Else
        FName = FixedFilePathName
    End If
    'If OverwriteIfFileExist = False we test if the PDF
     'already exist in the folder and Exit the function if that is True
    If OverwriteIfFileExist = False Then
        If Dir(FName) <> "" Then Exit Function
    End If

   'Now the file name is correct we Publish to PDF
   On Error Resume Next
   Myvar.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=FName, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=OpenPDFAfterPublish
    On Error GoTo 0

    'If Publish is Ok the function will return the file name
     If Dir(FName) <> "" Then Create_PDF = FName

End Function

The you can use an OS selector switch to run the appropriate function for each OS:

#If Mac Then
    savedFileName = Create_PDF_Mac(...)
#Else
    savedFileName = Create_PDF_PC(...)
#End If

Given the limitations of default VB functions in MacOS, this is Microsof't suggested method as well.

Thanks for this. It almost works. Sent I go to execute the function. All it brings up is the save window. It does not default to PDF and even when I select PDF and save it there is a compile run timer error. I created button that uses the function and it fails. The point where the MAcro editor cites the error is in the last line. If Dir(FName) <> "" Then Create_PDF = FName — DTest, Dec 16 '14 at 18:19
I basically want to be able to execute the function and by default the selected area that I have define in the sheet should save as a pdf automatically. — DTest, Dec 16 '14 at 18:22
What version of Excel for Mac are you using? Have you tried setting the filename manually when you call Create_PDF? Something like: `CommandButton1.Caption = Create_PDF(Worksheets(1), "Internal:Users:Mahmood:Desktop:jambalaya.pdf", True, True)`. Do you still get an error on the last line? Please note that the directories are separated with ":" rather than "/". And by the way, that last line only returns the file address, so you should be able to replace the whole line with `Create_PDF = ""` for testing purposes and it should not affect the main functionality of your code. — retrography, Dec 16 '14 at 19:03
`GetSaveAsFilename` does not support filtering files by extension on MacOS. If you need to contain the user, you can do it using AppleScript (see the updated code). Both scripts above run fine on my computers (Yosemite + Office 2011). If you run into errors, maybe the parameters you are passing to the function have a role in it. The code seems to run fine otherwise. — retrography, Dec 16 '14 at 20:44

medoc · Answer 2 · 2019-01-15T19:47:10.800

Here is a guide on how you can do it in newer versions of Mac Excel: https://www.rondebruin.nl/mac/mac034.htm

What's important is that you can't save your file to a location that is selected by you.

It has to be saved to the folder Library/Group Containers/UBF8T346G9.Office under the current user's home dir, so /Users/[current user]/Library/Group Containers/UBF8T346G9.Office in most of the cases. If the folder is not there, you have to create it. (See the code example on the page linked above)

Kudos to Ron!

Please vote for this to be fixed by MS here: https://excel.uservoice.com/forums/304933-excel-for-mac/suggestions/36531559-fix-exportasfixedformat-method

Excel VBA code to work on Mac, Create PDF Function

2 Answers2

Linked