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.

 

[Alex Blakenburg]

So what happens when you manually go File > Save As and try to use that name or a slight variation ie Map2.
Does it let you ?

 

[ollyhughes1982]

So what happens when you manually go File > Save As and try to use that name or a slight variation ie Map2.
Does it let you ?

It just lets me save as normal, in any format xlsx or csv, it's just when the macro tries to save it. previously it would just save the files to my desktop and then I could move to wherever I wanted to store them.

Do you have a mac with Sonoma to possible try it for yourself?

 

[ollyhughes1982]

A copy of the file is here: Maps2.xlsm

 

[RoryA]

Did you grant access to the folder?

 

[ollyhughes1982]

Did you grant access to the folder?

How would I do that? I have only ever seen that pop-up and I always click Allow, but don't know how to locate that option manually. I haven't seen such a message pop-up since upgrading

 

[RoryA]

Which build of Office are you on? I had problems earlier but my Office just upgraded to 16.79 and then I was prompted for access when the code ran.

 

[ollyhughes1982]

Access seems to be fine. My Excel version is 16.77.1

 

[ollyhughes1982]

It's saying I'm up-to-date, but not on your version. Are you on the BETA program? I'm only on the Current Channel (Preview) insider program.

 

[RoryA]

Yes, I am on the beta. (So I was on 16.78 earlier I think)

 

[ollyhughes1982]

Ah, ok. I may just have to wait then! Thanks for looking. Could you maybe try my file in the link and see if it works now? I don't really want to be changing anything, if it's just a case of waiting for the update. Like I said, nothing at all has changed in them, they were working for months beforehand and then just stopped when I upgraded to Sonoma