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

ollyhughes1982

Well-known Member
Joined
Nov 27, 2018
Messages
759
Office Version
  1. 365
Platform
  1. MacOS
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):

VBA Code:
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

Save As csv File macro Debug.jpeg
Save As xlsx File macro Debug.jpeg


Thanks in advance!

Olly.
 
Yes it works for me. Prompts for access, then runs without error.
 
Upvote 1
Solution

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
 
Upvote 0
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
 
Upvote 0
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!
Screenshot 2023-09-28 at 13.43.51.png
 
Upvote 0
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 ;))
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top