Save as PDF Macro working for Windows but not MAC OS

[jhirst001]

I am new to VBA and have found several solutions to creating a form control button that works on Windows OS. currently, it is able to work on my personal PC as well as co-workers using Windows OS. Where I have run into trouble is getting this to work for co-workers using a Mac device. Any help adapting this code to work for our resident Mac users is greatly appreciated.

The goal is for the Mac user to click the "save as PDF" button and have it save to their local downloads folder.

The code:

 Sub PDFActiveSheet()
        'www.contextures.com
        'for Excel 2010 and later
        Dim wsA As Worksheet
        Dim wbA As Workbook
        Dim strTime As String
        Dim strName As String
        Dim ID As String
        Dim strPath As String
        Dim strFile As String
        Dim strPathFile As String
        Dim myFile As Variant
        On Error GoTo errHandler
        
        ID = Range("A2")
        Set wbA = ActiveWorkbook
        Set wsA = ActiveSheet
        strTime = Format(Now(), "mmmm")
        
        'get active workbook folder, if saved
        strPath = wbA.Path
        If strPath = "" Then
          strPath = Application.DefaultFilePath
        End If
        strPath = strPath & "\"
        
        'replace spaces and periods in sheet name
        strName = Replace(wsA.Name, " ", "")
        strName = Replace(strName, ".", "_")
        
        'create default name for savng file
        strFile = ID & "_" & strName & "_" & strTime & ".pdf"
        strPathFile = strPath & strFile
        
        'use can enter name and
        ' select folder for file
        myFile = Application.GetSaveAsFilename _
            (InitialFileName:=strPathFile, _
                FileFilter:="PDF Files (*.pdf), *.pdf", _
                Title:="Select Folder and FileName to save")
        
        'export to PDF if a folder was selected
        If myFile <> "False" Then
            wsA.ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=myFile, _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=False
            'confirmation message with file info
            MsgBox "PDF file has been created: " _
              & vbCrLf _
              & myFile
              
        End If
        
exitHandler:
            Exit Sub
errHandler:
            MsgBox "Could not create PDF file"
            Resume exitHandler
        End Sub

 

[6StringJazzer]

What happens when you try it on a Mac? Always tell us the symptoms.

 

[jhirst001]

What happens when you try it on a Mac? Always tell us the symptoms.

Mac users get the “Could not create PDF” message box. So they are able to click the button

 

[6StringJazzer]

The way you are doing error handling throws away the information needed to diagnose the error. Comment out the On Error statement, then tell us which line of code is raising the error. When this occurs, also capture the values of all variables referenced on that line of code and include those in your description.

I suspect the problem is that you are hardcoding "\" as the path separator. It is "/" on a Mac but I do not have Mac for testing to see if it is robust enough to accept "\". To insulate against this use Application.PathSeparator instead of hardcoding the "\". I only see it used once:

        strPath = strPath & Application.PathSeparator

This is such an easy change I would try it. Then if you still get an error report as described above.

 

[jhirst001]

The way you are doing error handling throws away the information needed to diagnose the error. Comment out the On Error statement, then tell us which line of code is raising the error. When this occurs, also capture the values of all variables referenced on that line of code and include those in your description.

I suspect the problem is that you are hardcoding "\" as the path separator. It is "/" on a Mac but I do not have Mac for testing to see if it is robust enough to accept "\". To insulate against this use Application.PathSeparator instead of hardcoding the "\". I only see it used once:

        strPath = strPath & Application.PathSeparator

This is such an easy change I would try it. Then if you still get an error report as described above.

I commented out the On Error Line.

After making the adjustment you suggested, the code continues to work on Windows OS.

Mac users are now getting the message box:

"Run Time Error '1004':
Method 'GetSaveAsFileName' of object '_Application' failed

The code is failing on Line 38. When the user clicks 'debug' they see this:

Variables include:

Line 22

If strPath = "" Then
          strPath = Application.DefaultFilePath
        End If
        strPath = strPath & "\"

Line 32

 strFile = ID & "_" & strName & "_" & strTime & ".pdf"
        strPathFile = strPath & strFile

Again, I'm new to all of this, so if I've left something out, my apologies. Let me know if you need more/different information.

 

[MARK858]

In the line you have marked as Line 22 you haven't used the suggestion by @6StringJazzer to use Application.PathSeparator

 

[jhirst001]

In the line you have marked as Line 22 you haven't used the suggestion by @6StringJazzer to use Application.PathSeparator

I accidentally pasted the old code. Jazzer’s suggestion was implemented and tested.

 

[6StringJazzer]

The FileFilter parameter is not supported on a Mac.

GetSaveAsFileName | Mac Excel Automation

GetSaveAsFileName on a Mac

macexcel.com

At this point it's probably not necessary but you are showing the code that sets the variables but I wanted to see the actual values of those variables at the point where the error occurs. I already had the code.

 

[jhirst001]

The FileFilter parameter is not supported on a Mac.

GetSaveAsFileName | Mac Excel Automation

GetSaveAsFileName on a Mac

macexcel.com

At this point it's probably not necessary but you are showing the code that sets the variables but I wanted to see the actual values of those variables at the point where the error occurs. I already had the code.

Do I then need to add an "On Error" statement which accounts for the different verbiage required on Mac OS?

On my Windows machine, the actual values are as follows:

When I click the button the "save as" window pops up. It is defaults to the folder/location in which I have the document saved, and saves the file as "Athletes Name_2-WeekTrainingSummary_January.pdf".

 

[6StringJazzer]

I would suggest retrieving the OS name

Dim TheOS As String
TheOS = Application.Operatingsystem

and having one line of code when the name contains "Windows" and another when it does not (for Mac). The code will work fine without FileFilter, just shows the users more files.