SaveAs function works on Microsoft PC but not on M

Question

I have VBA code that controls the user from saving the file in any other format than .xls, .xlsm or .pdf. This is to prevent the stripping out of macros during the save process.

I have inserted a line to check if the operating system is OSx (... Like "Mac") which works in other macros but not this one. The process fails with "Can;t find the file object or library" with 'msoFileDialogSaveAs' highlighted.

Here is my code:

    Option Explicit
    Option Compare Text

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
        Cancel As Boolean)

      Dim fso As Object 'FileSystemObject
      Dim PdfSave As Boolean
      Dim SheetName As String
      If Not Application.OperatingSystem Like "*Mac*" Then
      SheetName = ActiveSheet.Name
      'Save-As action?
      If SaveAsUI Then
        Set fso = CreateObject("Scripting.FileSystemObject")
        'Abort excel's dialog
        Cancel = True
        'Create our own
        With Application.FileDialog(msoFileDialogSaveAs)
          'Select the XLSM filter by default
          .FilterIndex = 2
    Again:
          'Ok clicked?
          If .Show = -1 Then
            'Which extension should we save?
            Select Case fso.GetExtensionName(.SelectedItems(1))
              Case "xlsm"
                'Okay
              Case "xls"
                'Okay
              Case "pdf"
                PdfSave = True
                'Okay
              Case Else
                MsgBox "Invalid file type selected!" _
                  & vbCr & vbCr & "Only the following file formats are   permitted:" _
                  & vbCr & "   1. Excel Macro-Enabled Workbook (*.xlsm)" _
                  & vbCr & "   2. Excel 97-2003 Workbook (*.xls)" _
                  & vbCr & "   3. PDF (*.pdf)" _
                  & vbCr & vbCr & "Please try again." _
                  & vbCr & vbCr & "NOTE: 'Excel 97-2003 Workbook (*.xls)' format should be used for" _
                  & vbCr & "backwards compatability only!", vbOKOnly + vbCritical
                GoTo Again
            End Select
            'Prevent that we call ourself
            Application.EnableEvents = False
            'Save the file
            If PdfSave = True Then
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF,   Filename:=ActiveWorkbook.Path & "\" & SheetName & ".pdf",  Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
            Else
                ThisWorkbook.SaveAs .SelectedItems(1)
            End If
            Application.EnableEvents = True
          End If
        End With
      End If
      End If
    End Sub

Can anyone suggest changes so that this code works for Office on both PC and MAC, or have different code that achieves the same thing.

Thanks

Mike

Answer 1

You're heading off the edge of the map when working in Mac and PC environments, I have to do it a lot and its choppy seas thats for sure! My advice is persist, you are on the right track.

Firstly, I have a similar OS check: -

BlnIsAPC = IIf(Left(Trim(UCase(Application.OperatingSystem)), 1) = "M", False, True)

This is simply trying to have the most future proofed way of getting the OS right.

Secondly, it's good you're late binding to Scripting.FileSystemObject as that is not in Mac Office (Its part of Windows not Office).

Thirdly, neither is FileDialog, hence the error 'Can't find the file object or library'. There is an alternative and you will end up needing to refer into it a fair bit. Its a built-in function called MacScript.

You'll want to work out how to do it in AppleScript, and then create that script and run it through MacScript in VBA. Below is an stripped down example of my work in which I have code that either uses Application.FileDialog(msoFileDialogOpen) on a PC or MacScript on a Mac, specifically this is only showing the Mac side.

Public Function GetFilePath(ByVal StrTitle As String, ByVal StrButtonName As String, ByVal BlnMultiSelect As Boolean, ByVal StrFilters As String) As String
'               StrTitle        = The title to go on the dialog box
'               StrButtonName   = What to show on the OK button
'               BlnMultiSelect  = Can the user select more than one file
'               StrFilters      = What can be selected pipe and colon delimited i.e. [name]:[suffix]|[name]:[suffix]

If Procs.Global_IsAPC Then
    GetFilePath = GetFilePath_PC(StrTitle, StrButtonName, BlnMultiSelect, StrFilters)
Else
    GetFilePath = GetFilePath_Mac(StrTitle, StrButtonName, BlnMultiSelect, StrFilters)
End If

End Function

Private Function GetFilePath_PC(ByVal StrTitle As String, ByVal StrButtonName As String, ByVal BlnMultiSelect As Boolean, StrFilters As String) As String
...
End Function

Private Function GetFilePath_Mac(ByVal StrTitle As String, ByVal StrButtonName As String, ByVal BlnMultiSelect As Boolean, StrFilters As String) As String
Dim AryTemp2()      As String
Dim LngCounter      As Long
Dim StrContainer    As String
Dim StrPath         As String

StrContainer = "tell application " & """" & "Finder" & """" & Chr(13)
StrContainer = StrContainer & "choose file with prompt " & """" & StrTitle & """"

If StrFilters <> "" Then
    StrContainer = StrContainer & " of type {"
    'Code was here that prepared the filters into AryTemp2 
    For LngCounter = 0 To UBound(AryTemp2, 1)
        If Right(StrContainer, 1) <> "{" Then StrContainer = StrContainer & ", "
        StrContainer = StrContainer & """" & AryTemp2(LngCounter2) & """"
    Next
    StrContainer = StrContainer & "} " 
End If

StrContainer = StrContainer & "without invisibles" & IIf(BlnMultiSelect, "", " and multiple selections") & " allowed" & Chr(13)
StrContainer = StrContainer & "end tell"
StrPath = MacScript(StrContainer)

If Left(StrPath, 6) = "alias " Then StrPath = Right(StrPath, Len(StrPath) - 6)

GetFilePath_Mac = StrPath

End Function

FYI at point of execution in MacScript, StrContainer looked as below: -

tell application "Finder"
choose file with prompt "Select the required Config stub" of type {"Config_Stub"} without invisibles and multiple selections allowed
end tell

Finally, VBA is not available on all versions of Office for Mac and there are subtle differences in ways of working between them that unfortunutly you'll only come to find via experience. Like I say 'You're heading off the edge of the map' into uncharted waters.