SaveAs CSV on Mac

[matt767]

I am trying to save a new opened workbook as a csv on a Mac but keep running into error 1004: application or object defined error

here is the code:

Dim directory As String
directory = "/Users/johnsmith/Desktop"
Dim mystring, clientname
mystring = Range("B2").Text
clientname = Mid(mystring, WorksheetFunction.Find("#", WorksheetFunction.Substitute(mystring, "/", "#", 3)) + 1, WorksheetFunction.Find("#", WorksheetFunction.Substitute(mystring, "/", "#", 4)) - WorksheetFunction.Find("#", WorksheetFunction.Substitute(mystring, "/", "#", 3)) - 1)
Dim wbname As String
wbname = WorksheetFunction.Substitute(Date, "/", ".") & " (" & clientname & ") " & "Upload"
Workbooks.Add
ChDir directory
ThisWorkbook.ActiveSheet.SaveAs Filename:=directory & "/" & wbname & ".csv", _
FileFormat:=xlCSVUTF8, CreateBackup:=False

I've tried Application.ThisWorkbook.SaveAs, ThisWorkbook.Sheets("Sheet1").SaveAs, ActiveWorkbook.ActiveSheet.SaveAs, ActiveWorkbook.Sheets("Sheet1").SaveAs, and ActiveWorkbook.SaveAs and all fail. office version is 365.

When I record a macro to do the same thing the code is like what I already tried:

    Workbooks.Add
    ChDir "/Users/johnsmith/Desktop/"
    ActiveWorkbook.SaveAs FileName:="/Users/cameronspiller/Desktop/Book2.csv", _
        FileFormat:=xlCSVUTF8, CreateBackup:=False

 

[MARK858]

Have you tried removing the ActiveSheet from the code line?

ThisWorkbook.ActiveSheet.SaveAs Filename:=directory & "/" & wbname & ".csv", _
FileFormat:=xlCSVUTF8, CreateBackup:=False

 

[matt767]

@MARK858 I did and no luck

 

[MARK858]

Assuming it is the SaveAs line that is erring, if you add the line in red does what prints in the Immediate Window look correct for the filepath?
Also are you sure you want ThisWorkBook (that is the workbook the code resides in)?
I would think you need ActiveWorkbook

ChDir directory
Debug.Print directory & "/" & wbname & ".csv"
ActiveWorkbook.SaveAs Filename:=directory & "/" & wbname & ".csv", _
FileFormat:=xlCSVUTF8, CreateBackup:=False

 

[matt767]

ActiveWorkbook didn't work. This code runs fine on pc btw. Path looks fine. @MARK858

 

[MARK858]

What happens with

    Dim directory As String
    directory = Application.PathSeparator & "Users" & Application.PathSeparator & "johnsmith" & Application.PathSeparator & "Desktop"
    Dim mystring, clientname
    mystring = Range("B2").Text
    clientname = Mid(mystring, WorksheetFunction.Find("#", WorksheetFunction.Substitute(mystring, "/", "#", 3)) + 1, WorksheetFunction.Find("#", WorksheetFunction.Substitute(mystring, "/", "#", 4)) - WorksheetFunction.Find("#", WorksheetFunction.Substitute(mystring, "/", "#", 3)) - 1)
    Dim wbname As String
    wbname = WorksheetFunction.Substitute(Date, "/", ".") & " (" & clientname & ") " & "Upload"
    Workbooks.Add
    ChDir directory
    ActiveWorkbook.SaveAs Filename:=directory & Application.PathSeparator & wbname & ".csv", _
                       FileFormat:=xlCSVUTF8, CreateBackup:=False

 

[matt767]

@MARK858 same error

 

[MARK858]

Only thing that I can think of is if you are having the same issue as in the thread in the link below

Mac OS Sonoma - Excel Macros failing since upgrade - SaveAs seems to be failing

Hi all, I have the following macro, which has worked without issue for around 4-5 months, Since upgrading my Mac to Sonoma last night, my macros are now failing. As per the attached screenshots, could anyone please help?! Wonder whether it might be something to do with file paths or something...

www.mrexcel.com