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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Definitely seems to be the Save aspect of the macros, as others (such as below) are still working without issue.

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

VBA Code:
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
 
Upvote 0
Does the below make a difference?
VBA Code:
Application.ActiveWorkbook.SaveAs FileName:=xPath & Application.PathSeparator & xWs.Name & ".xlsx", fileformat:= xlOpenXMLWorkbook
 
Upvote 0
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.
 
Upvote 0
I tried the following (not sure if I've done it right!), but it gave the same error.

VBA Code:
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
 
Upvote 0
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:
VBA Code:
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.
 
Upvote 0
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:
VBA Code:
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?
 
Upvote 0
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:
VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

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