Code works on Windows but not Mac

[stroffso]

Hi All,

I have the below piece of code which runs fine on a Windows PC but I need it to run on a mac and when I run it it keeps erroring at the save file stage. The issue seems to be with the directory, this is the one I use: /Users/stroffso/desktop

Any help would be greatly appreciated

Sub ExportNatalieAndPoppy()
    Dim wb As Workbook
    Dim wsNatalie As Worksheet
    Dim wsControls As Worksheet
    Dim saveDirectory As String
    Dim natalieFileName As String
    Dim selectedName As String
    Dim namesArray As Variant
    Dim i As Integer
    
    Application.DisplayAlerts = False
    
    ' Set references to workbook and worksheets
    Set wb = ThisWorkbook
    Set wsNatalie = wb.Sheets("Summary")
    Set wsControls = wb.Sheets("Controls")
    
    ' Get the save directory from Cell D18 on the Controls tab
    saveDirectory = wsControls.Range("D18").Value
    
    ' Define an array of names
    namesArray = Array("Natalie James", "Frank Lee")
    
    ' Loop through the names
    For i = LBound(namesArray) To UBound(namesArray)
        selectedName = namesArray(i)
        
        ' Set the output Excel filename
        natalieFileName = selectedName & ".xlsx"
        
        ' Update the name in Cell C1
        wsNatalie.Range("C1").Value = selectedName
        
        ' Copy the "Summary" tab to a new workbook
        wsNatalie.Copy
        ActiveWorkbook.SaveAs fileName:=saveDirectory & "\" & natalieFileName, FileFormat:=xlOpenXMLWorkbook
        ActiveWorkbook.Close SaveChanges:=False
    Next i
    
    ' Clean up
    Set wsNatalie = Nothing
    Set wb = Nothing
    Application.DisplayAlerts = True
End Sub

 

[noveske]

What's the error?

 

[MARK858]

Maybe if it is breaking on the SaveAs line

ActiveWorkbook.SaveAs Filename:=saveDirectory & Application.PathSeparator & natalieFileName, FileFormat:=xlOpenXMLWorkbook

 

[stroffso]

What's the error?

 

[Alex Blakenburg]

We don't have visibility of what is in wsControls.Range("D18") ie "saveDirectory", so how about:

    Dim FullFileName As String
    FullFileName = Replace(saveDirectory & "\" & natalieFileName, "\", Application.PathSeparator)
    ActiveWorkbook.SaveAs Filename:=FullFileName, FileFormat:=xlOpenXMLWorkbook

 

[stroffso]

We don't have visibility of what is in wsControls.Range("D18") ie "saveDirectory", so how about:

    Dim FullFileName As String
    FullFileName = Replace(saveDirectory & "\" & natalieFileName, "\", Application.PathSeparator)
    ActiveWorkbook.SaveAs Filename:=FullFileName, FileFormat:=xlOpenXMLWorkbook

Thanks all for your comments, the string in Cell D18 is /Users/stroffso/desktop

 

[Alex Blakenburg]

Did you try @MARK858's line ?
If it didn't work then before that line add this line:

Debug.Print saveDirectory & Application.PathSeparator & natalieFileName

Then have a look in the immediate window (Ctrl+G if its not visible) and see if the file path and name make sense.
If it visually make sense but its not working, copy the line from the immediate window and try it by pasting it manually in a save as dialogue box and see what you need to change to make it save.

 

[stroffso]

I have gone with an alternative approach which was removing the save to specific directory as the above didnt work, the export still works though so a little bit of manual intervention is ok, thanks to all for your help

 

[MARK858]

A bit surprised the code by @Alex Blakenburg in post 5 didn't work (would have been nice to see what path it produced), but happy you have found a workaround