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

[ollyhughes1982]

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, as the debugger seems to be landing at the SaveAs lines?

The script is as follows (debug parts highlighted):

Sub RunSaveAllWorksheetsAsSeparatexlsxFilesMacroAndSaveAllWorksheetsAsSeparatecsvFilesMacro()
'Olly Hughes, 30-07-2023
Call SaveAllWorksheetsAsSeparatexlsxFilesMacro
Call SaveAllWorksheetsAsSeparatecsvFilesMacro
End Sub

Sub SaveAllWorksheetsAsSeparatexlsxFilesMacro()
'Olly Hughes, 24-05-2023
    Dim xPath As String
    xPath = Application.ActiveWorkbook.Path
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each xWs In ActiveWorkbook.Sheets
        xWs.Copy
        Application.ActiveWorkbook.SaveAs FileName:=xPath & Application.PathSeparator & xWs.Name & ".xlsx"
        Application.ActiveWorkbook.Close False
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Sub SaveAllWorksheetsAsSeparatecsvFilesMacro()
'Olly Hughes, 24-05-2023
   Dim xPath As String
   Dim xWs As Worksheet
   xPath = ThisWorkbook.Path 'Use ThisWorkbook instead of Application.ActiveWorkbook
   Application.ScreenUpdating = False
   Application.DisplayAlerts = False
   For Each xWs In ThisWorkbook.Sheets 'Use ThisWorkbook instead of ActiveWorkbook
       xWs.Copy
       Dim newWorkbook As Workbook
       Set newWorkbook = ActiveWorkbook 'Assign the copied workbook to a variable
       newWorkbook.SaveAs FileName:=xPath & Application.PathSeparator & xWs.Name & ".csv", FileFormat:=51
       newWorkbook.SaveAs FileName:=xPath & Application.PathSeparator & xWs.Name & ".csv", FileFormat:=6
       newWorkbook.Close False
   Next
   Application.DisplayAlerts = True
   Application.ScreenUpdating = True
End Sub

Thanks in advance!

Olly.

 

[ollyhughes1982]

Definitely seems to be the Save aspect of the macros, as others (such as below) are still working without issue.

Sub PasteAllCellsAsValuesInAllWorksheetsMacro()
'Olly Hughes, 24-05-2023
    Dim wsh As Worksheet
    For Each wsh In ThisWorkbook.Worksheets
        wsh.Cells.Copy
        wsh.Cells.PasteSpecial xlPasteValues
    Next
    Application.CutCopyMode = False
End Sub

Sub SelectCellA1InAllWorksheetsMacro()
'Olly Hughes, 24-05-2023
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Sheets
        ws.Activate
        ws.[a1].Select
    Next ws
    ActiveWorkbook.Worksheets(1).Activate
End Sub

 

[ollyhughes1982]

Also posted on Excel Forum: Mac OS Sonoma - Excel Macros failing since upgrade - SaveAs seems to be failing

 

[ollyhughes1982]

Apologies, I forgot to include what the initial error reads - as below:

 

[MARK858]

Does the below make a difference?

Application.ActiveWorkbook.SaveAs FileName:=xPath & Application.PathSeparator & xWs.Name & ".xlsx", fileformat:= xlOpenXMLWorkbook

 

[ollyhughes1982]

Where would I put this in both scripts, please? Could you maybe post the entire script as it should be, please? Thanks for looking at this.

 

[ollyhughes1982]

I tried the following (not sure if I've done it right!), but it gave the same error.

Sub SaveAllWorksheetsAsSeparatexlsxFilesMacro()
'Olly Hughes, 24-05-2023
    Dim xPath As String
    xPath = Application.ActiveWorkbook.Path
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each xWs In ActiveWorkbook.Sheets
        xWs.Copy
        Application.ActiveWorkbook.SaveAs FileName:=xPath & Application.PathSeparator & xWs.Name & ".xlsx", FileFormat:=xlOpenXMLWorkbook
        Application.ActiveWorkbook.Close False
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

 

[Alex Blakenburg]

I tend to use the immediate window a lot (Ctrl+G if you can't see it in the VBA editor)
In your original or Mark's code before the SaveAs line put this line:

debug.print xPath & Application.PathSeparator & xWs.Name & ".xlsx"

Eyeball what you see in the immediate window to see if it looks ok.
If nothing is visually obvious, copy what appears in the immediate window and try a manual save as pasting that in as the path.
Trouble shoot what you need to change to make it save.

 

[ollyhughes1982]

I tend to use the immediate window a lot (Ctrl+G if you can't see it in the VBA editor)
In your original or Mark's code before the SaveAs line put this line:

debug.print xPath & Application.PathSeparator & xWs.Name & ".xlsx"

Eyeball what you see in the immediate window to see if it looks ok.
If nothing is visually obvious, copy what appears in the immediate window and try a manual save as pasting that in as the path.
Trouble shoot what you need to change to make it save.

Thanks. OK, I'm a bit of an amatuer with VBA so not sure if I'll be to crack it myself! It's odd as nothing has changed except for updating to Sonoma, the macros worked fine until then, for months on end. Guessing something must have changed in the back-end of MacOS somewhere - to do with filepaths maybe?

 

[ollyhughes1982]

I tend to use the immediate window a lot (Ctrl+G if you can't see it in the VBA editor)
In your original or Mark's code before the SaveAs line put this line:

debug.print xPath & Application.PathSeparator & xWs.Name & ".xlsx"

Eyeball what you see in the immediate window to see if it looks ok.
If nothing is visually obvious, copy what appears in the immediate window and try a manual save as pasting that in as the path.
Trouble shoot what you need to change to make it save.

Just tried it and got the same error as usual. All that appeared in the Immediate Window is this:

/Users/Olly/Desktop/All E(V) - Map.xlsx