VBA to save all worksheets as separate .csv files - macOS

[ollyhughes1982]

Hi,

I have the following code, which saves / exports all worksheets within a single .xlsx file to separate .xlsx files in the same file path location:

Sub Splitbook()
'Updateby20140612
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

Is there a way that I can amend this to work so that it saves the files in .csv format instead. I have tried a few things (see a related post here: VBA to save all worksheets as separate .xlsx files - macOS), but none have worked so far - despite working for the suggesting user on Windows OS.

Below is one of the suggestions, but despite working on Windows, it didn't work for my Mac. Guessing there must be a different file type part to make it work, rather than 'xlCSVUTF8'.

Sub Splitbook()
'Updateby20140612
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, FileFormat:=xlCSVUTF8
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Thanks in advance!

 

[Fluff]

You're welcome.

 

[ollyhughes1982]

This has now been solved by somebody I found online, Ron de Bruin (Ron de Bruin & Home | Mac Excel Automation). He gave me the following VBA script, which has worked perfectly:

Sub Splitbook_2()
   'Updateby20140612
   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

Highly recommend visiting his site if you have time! He also does Windows solutions, here: Excel Automation - Ron de Bruin

Thanks, all.

 

[Ron de Bruin]

Great that it is working for you. Here is the page with some info Save Range or Worksheet as CSV | Mac Excel Automation