Save Range or Worksheet as CSV | Mac Excel Automation

Save Range or Worksheet as CSV file

There is a bug in Mac Excel that not allow you to save a range or worksheet to a csv file directly like you do in Excel for Windows, you will get a 1004 error Method 'SaveAs' of object'_Workbook' failed. The workaround is to save it as FileFormat xlsx first and then as CSV. If you have another solution let me know so I can update the info on this page.

Example below save the active sheet as CSV file on your Desktop

Sub Save_ActiveSheet_As_CSV_On_Mac()
    'Ron de Bruin, 27-April-2023
    'Save first as xlsx and the second time as csv seems to be the solution to fix the problem on the Mac
    Dim wb As Workbook
    Dim SaveFolder As String

    'Turn off ScreenUpdating and DisplayAlerts
    'Note: ScreenUpdating is not working correct on this moment in Mac Excel
    'We hope they fix it so I leave the code in this macro
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    'Create destination workbook
    ActiveSheet.Copy
    Set wb = ActiveWorkbook

    'Set the folder where you want to save the csv file, Desktop now.
    SaveFolder = MacScript("return posix path of (path to desktop folder) as string")
   
    'Save the workbook with the name MyCSV1 first as xlsx and then as csv file
    wb.SaveAs SaveFolder & "MyCSV1.csv", FileFormat:=51  'xlOpenXMLWorkbook
    wb.SaveAs SaveFolder & "MyCSV1.csv", FileFormat:=6  'xlCSV

    'Close the destination workbook
    wb.Close False

    'Turn on ScreenUpdating and DisplayAlerts
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

The example below save a range from Sheet1 as CSV on the Desktop.

Sub Save_Range_As_CSV_On_Mac()
    'Ron de Bruin, 27-April-2023
    'Save first as xlsx and the second time as csv seems to be the solution to fix the problem on the Mac
    Dim Rng As Range
    Dim wb As Workbook
    Dim SaveFolder As String

    'Turn off ScreenUpdating and DisplayAlerts
    'Note: ScreenUpdating is not working correct on this moment in Mac Excel
    'We hope they fix it so I leave the code in this macro
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    'Range that you want to copy
    Set Rng = Sheets("Sheet1").Range("A1:B3")

    'Create destination workbook with one sheet
    Set wb = Workbooks.Add(1)

    'Paste the range inside thisnew workbook
    Rng.Copy
    wb.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues

    'Set the folder where you want to save the csv file, Desktop now.
    SaveFolder = MacScript("return posix path of (path to desktop folder) as string")
   
    'Save the workbook with the name MyCSV2 first as xlsx and then as csv file
    wb.SaveAs SaveFolder & "MyCSV2.csv", FileFormat:=51  'xlOpenXMLWorkbook
    wb.SaveAs SaveFolder & "MyCSV2.csv", FileFormat:=6  'xlCSV

    'Close the destination workbook
    wb.Close False

    'Turn on ScreenUpdating and DisplayAlerts
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub