Forum Discussion

Ploughguy's avatar
Ploughguy
Copper Contributor
Apr 12, 2021

Mac: Excel 365 macro's SaveAs CSV gives "Run-time error '1004'" "Cannot access read-only document"

This problem is as old as VBA on Mac OS, it seems, but I cannot find a solution that applies in my case.   I have a macro that wants to write a sheet as a VBA script.  It fails with error 1004: Can...
excel
Excel on Mac
Macros and VBA
office 365
Jonathan_B830's avatar
Jonathan_B830
Copper Contributor
Jul 16, 2021

Ploughguy 

I've found *a* solution: Save to the Excel directory.

 

There are two ways you can go about doing this:

 

OPTION 1: If you are running the script from PERSONAL.XLSB, use ThisWorkbook.Path as the file path (solution stolen from http://learnexcelmacro.com/wp/2017/09/save-excel-range-data-as-csv-file-through-excel-vba/, Option 1):

 

myCSVFileName = ThisWorkbook.Path & "\" & "CSV-Exported-File-" & VBA.Format(VBA.Now, "dd-MMM-yyyy hh-mm") & ".csv"

 

 

OPTION 2: If you are running the script from another location, locate the file directory that contains PERSONAL.XLSB and include that in your filename. This is what it looks like for me:

 

csvname = "/Users/user_name/Library/Group Containers/UBF8T346G9.Office/User Content.localized/Startup.localized/" & ActiveSheet.Name & ".csv"

 

 

Excel apparently has permission to write .CSVs to this location, so you need to bypass whatever dreams you had of saving these files to your Documents or Desktop folders.

 

Note that don't need to save a dummy .XLSX in order to save the .CSV. I was able to save directly to .CSV in this location. I also created a subfolder and was able to save to that subfolder.

Resources