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.

 

[RoryA]

Yes it works for me. Prompts for access, then runs without error.

 

[ollyhughes1982]

Brilliant! My solution then, is to wait! Thanks very much for the advice. It must be a known bug they’ve encountered and promptly fixed

 

[ollyhughes1982]

I've just moved to the beta channel and it still has the same issue on 16.79 for me I didn't get a pop up re allowing access

 

[ollyhughes1982]

Ok, this is really strange. If I go in and remove access to the folders in privacy and security settings, it then works. Surely these should need to be toggled on for it to work, not the other way around? Very strange!

 

[RoryA]

What happens if you turn those off then back on?

(this is why I never install a new Mac OS on an important machine for at least a month after it comes out )

 

[ollyhughes1982]

Yip, tried that. It reverts to not working. Very odd!

 

[toaruScar]

Thank you for this workaround. I'm having this problem too.