VBA Script: Saving worksheet as CSV on MacOS

[ruinedelf]

Hiya, me again! Been having a real headache trying to figure this out.

I've searched around and found topics like this one by @ollyhughes1982 and this excellent topic from @Ron de Bruin, but I can't quite figure out what the limitations are on file paths.

Currently, I have a bunch of scripts that all run in sequence, with the topical script being the last one in the series. What I'd like it to do is copy all data from the current sheet (where the button was clicked), copy it to a new workbook/sheet, delete row 2 and everything under row 60, then save it as the name of the original worksheet it came from in CSV format. It needs to work in both Windows and Mac. It also needs to save the new CSV file into the same directory as the original XLSM file.

Below is the script I've managed to find, it works perfectly for Windows, but doesn't work for Mac at all in certain folders such as Google Drive or File Maker's temporary folders (1004 run-time error on SaveAs):

Sub ExportAsCSV()

    Dim MyFileName As String
    Dim CurrentWB As Workbook, TempWB As Workbook
    Dim DataRange As Range

    ' Set a reference to the active workbook
    Set CurrentWB = ActiveWorkbook

    ' Create a new workbook
    Set TempWB = Workbooks.Add

    ' Copy all data from the active sheet to the new workbook
    Set DataRange = CurrentWB.ActiveSheet.UsedRange
    DataRange.Copy TempWB.Sheets(1).Range("A1")

    ' Delete row 2 in the new workbook
    TempWB.Sheets(1).Rows(2).Delete
    TempWB.Sheets(1).Rows("61:" & TempWB.Sheets(1).Rows.Count).Delete
    
    'MyFileName defined by OS
    Dim os As String
    os = Application.OperatingSystem
    
    ' Check if the operating system is Mac
    If InStr(1, os, "Mac") > 0 Then
    '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 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

    '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")
    SaveFolder = CurrentWB.Path
   
    'Save the workbook with the name MyCSV1 first as xlsx and then as csv file
    TempWB.SaveAs SaveFolder & Application.PathSeparator & CurrentWB.ActiveSheet.Name & ".csv", FileFormat:=51  'xlOpenXMLWorkbook
    TempWB.SaveAs SaveFolder & Application.PathSeparator & CurrentWB.ActiveSheet.Name & ".csv", FileFormat:=6  'xlCSV

    'Close the destination workbook
    TempWB.Close False

    'Turn on ScreenUpdating and DisplayAlerts
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Else
        ' Code to run on Windows or other operating systems
        ' Specify the file name with the ".csv" extension
        MyFileName = CurrentWB.Path & "\" & CurrentWB.ActiveSheet.Name & ".csv"
        ' Save the new workbook as CSV
        Application.DisplayAlerts = False
        TempWB.SaveAs Filename:=MyFileName, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
        Application.DisplayAlerts = True
        TempWB.Close SaveChanges:=False
    End If
End Sub

As this works perfectly in Windows, all I need to do is change the Mac section of things. However, while using Mr. Ron's Macscript for Desktop works, as stated I don't know how to find the current folder. Any help would be much appreciated!

 

[temphelp]

After a lot of research (and failed answers), I came up with a simple solution. You must save the file into the official Office folder on your computer (this is a known working solution mentioned many times on various forums). BUT... you can then use the following command to "move" the file out of this folder to wherever you originally wanted it. For example, I wanted the .csv files to be saved to the desktop. I got the 1004 runtime error all the time. Now, I save to the Office folder as a .csv and then just move it to the desktop via this code. No need to first save it as an .xls as some solutions have shown.

** Please note that your office folder location might be different **

Name "/Users/[Username]/Library/Group Containers/UBF8T346G9.Office/User Content.localized/Startup.localized/Excel/[filename].csv" As "/Users/[Username]/Desktop/[filename].csv"