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!

 

[ollyhughes1982]

Also posted on Excel Forum: VBA to save all worksheets as separate .csv files - macOS

 

[Zaifee]

The file format "xlCSVUTF8" that you mentioned is specific to Windows and may not be compatible with Mac operating systems. On Mac, you can use the "FileFormat:=6" parameter to save the worksheets as CSV files. Here's the modified code that should work on Mac:

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:=6
        Application.ActiveWorkbook.Close False
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

This code will save each worksheet as a separate CSV file in the same file path location. The "FileFormat:=6" parameter specifies the CSV file format.

 

[ollyhughes1982]

The file format "xlCSVUTF8" that you mentioned is specific to Windows and may not be compatible with Mac operating systems. On Mac, you can use the "FileFormat:=6" parameter to save the worksheets as CSV files. Here's the modified code that should work on Mac:

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:=6
        Application.ActiveWorkbook.Close False
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

This code will save each worksheet as a separate CSV file in the same file path location. The "FileFormat:=6" parameter specifies the CSV file format.

Hi. Thanks for the reply, but this doesn't work for me, I get the following error:

In debugger it looks like this:

 

[ollyhughes1982]

Here is a link to the file with the code you provded: Save All Worksheets As Separate csv Files Macro Example.xlsm

Here is a link to the type of file I'm trying to do the export for: Maps Example.xlsx

 

[Zaifee]

The error you encountered in the Splitbook() macro occurs because the SaveAs method is being called on the Application.ActiveWorkbook object after it has been closed in the previous line. Once you close the workbook, you no longer have access to it.

To fix this issue, you can assign the copied workbook to a variable before saving it. Here's an updated version of the Splitbook() macro:

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

In this updated code, the copied workbook is assigned to the newWorkbook variable, and then the SaveAs method is called on newWorkbook. This ensures that the workbook is still accessible for saving before closing it.

Make sure to replace the previous code with this updated version in your VBA module.

 

[ollyhughes1982]

The error you encountered in the Splitbook() macro occurs because the SaveAs method is being called on the Application.ActiveWorkbook object after it has been closed in the previous line. Once you close the workbook, you no longer have access to it.

To fix this issue, you can assign the copied workbook to a variable before saving it. Here's an updated version of the Splitbook() macro:

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

In this updated code, the copied workbook is assigned to the newWorkbook variable, and then the SaveAs method is called on newWorkbook. This ensures that the workbook is still accessible for saving before closing it.

Make sure to replace the previous code with this updated version in your VBA module.

Hi, thanks for trying, but that still gives me an error. I get the following:

 

[ollyhughes1982]

Whsatever suggested solutions I try it always seems to be an issue with the SaveAs line of code. Is there special format required on mac? I have looked everywhere I can think of and can't find a solution. Any help appreciated!

 

[Fluff]

It appears there is a bug with using saveas on a Mac. Have a look here Save Range or Worksheet as CSV | Mac Excel Automation

 

[ollyhughes1982]

It appears there is a bug with using saveas on a Mac. Have a look here Save Range or Worksheet as CSV | Mac Excel Automation

Thanks. Driving me crazy!