0

I would like to split an excel workbook into one file for each worksheet using VBA on mac. I tried many different makro-codes I found in different forums but I never got them to work. Does anyone have a code that works for him? The best code I found so far was the following:

Sub ExportSheetsToCSV()
Dim xWs As Worksheet
Dim xcsvFile As String
For Each xWs In Application.ActiveWorkbook.Worksheets
    xWs.Copy
    xcsvFile = CurDir & "/" & xWs.Name & ".csv"
    Application.ActiveWorkbook.SaveAs Filename: = xcsvFile, _
    FileFormat: = xlCSV, CreateBackup: = False
    Application.ActiveWorkbook.Saved = True
    Application.ActiveWorkbook.Close
Next
End Sub

I found this code here: https://github.com/hami-gitgud/Excel_split_to_csv/blob/master/VBA%20Code

I replaced the blackslash by "/" to adjust it for mac. However it is still not perfect as I would like to save .xlsx to desktop. Does anyone know how to get this done ?

P.s. curDir does not work properly on mac. https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/curdir-function

brauer-t
  • 45
  • 7

1 Answers1

0

Replace the FileFormat: = xlCSV with FileFormat: = xlOpenXMLWorkbook and give it the proper extension.

Franz Reischl
  • 190
  • 8
  • Thank you, this already helps a lot. Do you also know how to define the output directory on mac? – brauer-t Sep 06 '21 at 16:30
  • Ok apparently its not really possible on mac without interaction to grant permission: https://macexcel.com/examples/setupinfo/sandbox/ – brauer-t Sep 06 '21 at 16:56