Forum Discussion
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: Cannot access read-only document.
--- Update - I think I have a solution, although every time I think that, I try it again the next day and it is broken again. However, see my replies to myself below.
--- End update
--- Update after that update - Nope - still not working properly. If I save a sheet of workbook AAA.xlsx as SheetB.xlsx, it does exactly what you expect - AAA.xlsx is unchanged and a new workbook is created named SheetB.xlsx
But! If I save a sheet of workbook AAA.xlsx as SheetC.csv, I get a csv file as expected, but it also renames AAA.xlsx to SheetC.csv, pops up a box offering to save that too, and gives the warning about possible data loss - so the original sheet has its name changed ~and~ wants to save itself as a CSV.
Game over. I give up.
---End of update.
Sub saveCSV()
'
' Save the active sheet to a file in this quarter's CSV directory
' CSVPATH, FY and QTR are named cells in the CheckList sheet.
'
' Replace all spaces in sheet's name with dash.
sheetName = Replace(ActiveSheet.Name, " ", "-")
' Make a path using the sheet name, with "-IMP" at the end to indicate it is the import version.
sheetPath = Range("CSVPATH").Value & "/" & sheetName & "-" & Range("FY").Value & "Q" & Range("QTR").Value & "-IMP.csv"
' sheetPath = "/users/russ/Desktop/" & sheetName & "-" & Range("FY").Value & "Q" & Range("QTR").Value & "-AE.csv"
' Display path so can nconfirm path is valid
MsgBox sheetPath
'Application.DisplayAlerts = False
ActiveWorkbook.Sheets(ActiveSheet.Name).Copy
ActiveWorkbook.SaveAs FileName:=sheetPath, FileFormat:=xlcsv, CreateBackup:=False
ActiveWorkbook.Close
'Application.DisplayAlerts = True
End Sub
The macro opens a new workbook with the expected data in it, then throws up a box with:
Run-time error '1004'
Cannot access read-only document
I have checked that the file it is trying to create does not already exist. I can copy the path from the MsgBox and paste it into a touch command in a terminal window:
touch /Users/russ/daynecorp/TaxReturns/2021/BAS2021Q3/QIF/Post-Expenditure-2021Q3-IMP.csv
The command succeeds and the file is created (and then I remove it.)
I can manually save the new one-sheet workbook as a CSV to the path above.
I have tried removing the ".csv" suffix so Excel can add it - no dice.
In System Preferences - Security - Privacy, Excel has full disk access.
In Excel Preferences - Security - Macro Security option "Disable all macros with notification" is set. My macros are in my PERSONAL.XLSX workbook so I presume they are not affected by this setting because they run.
Any thoughts?
Thanks, as always, for your help, (all of you!)
Excel is 16.47.1 from a Microsoft 365 subscription
MacOS is Mojave with the latest patches.
- PloughguyCopper Contributor
Woo hoo! It worked! The old "Spend an hour creating a nice detailed help request and you will be rewarded with finding the solution immediately" principle came good.
--- Update: That was yesterday. Today, we tried to use it in production, and it gave the usual 1004 error. See next reply for the latest seems-to-work solution.
--- End update
As I clicked Save for the message above, I thought of ~one~more~thing~. (See what I did there?) I changed the export file type to xlOpenXMLWorkbook which creates an xlsx file. when I ran the macro, it popped up a pop-up asking me to Grant File Access to the target directory. I granted, and the xlsx appeared. I switched the file type and suffix back to CSV, and this time I get a CSV file with no errors.
This, I believe, is a bug in Excel.- PloughguyCopper Contributor
But wait, there's more...
The solution above is not 100% complete. I extended the macro to always save an xlsx version first, then save the csv. Each files had a slightly different base name (the first part of the file name, before the dot) to make it easy to see what is going on during testing.
Turns out, Excel ignores extensions when it is checking for a potential file name collision. It is not enough that the extension is different, the bit before the dot also has to be different. This was the case when I was testing earlier, but for the production version of the macro, I gave the xlsx and csv files matching names except for the extension. These are considered to be unique file names by every operating system I have ever worked with, but Excel gives an error (cannot access read-only document) if it is saving a file with a base name that matches an existing file with a different extension..
This version (below) of the macro seems to work (as in I cannot guarantee it will be working tomorrow, since the solution above seemed to work yesterday...)
Note that the csv path ends in ...-cs.csv and the xlsx version ends in ...-xl.xlsx to make the filenames different before the extension is added.
Some things to note while you are wrestling with this:
- It creates a new workbook containing just the sheet being saved. You need to close or delete this between test runs unless they get to the "close" line in the macro.
- To protect your sanity, remove any files that it creates between runs.
Good luck, and stay strong.
Russ
Sub saveSheetToCSV() ' ' Save the active sheet to a file in a directory determined by a named cell. ' The filename is derived from the sheet name. ' Assumes CSVPATH, FY and QTR are named cells set elsewhere. ' A bug in something means Excel does not request Grant Access for CSV, but it does for xmlx ' Therefore we save as xmlx first, then save as CSV. ' To add to the excitement, Excel will give a 1004 error if the xmlx and csv have the same name prefix - it is not enough that they have different extensions. ' Replace all spaces in sheet name with dash. This removes doubt - not required? sheetName = Replace(ActiveSheet.Name, " ", "-") ' Make a path using the sheet name, with different endings for xmlx and csv versions. csvpath = Range("CSVPATH").Value & "/" & sheetName & "-" & Range("FY").Value & "Q" & Range("QTR").Value & "-cs.csv" xmlpath = Range("CSVPATH").Value & "/" & sheetName & "-" & Range("FY").Value & "Q" & Range("QTR").Value & "-xl.xlsx" MsgBox xmlpath ActiveWorkbook.Sheets(ActiveSheet.Name).Copy ActiveWorkbook.SaveAs FileName:=xmlpath, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False MsgBox csvpath ActiveWorkbook.SaveAs FileName:=csvpath, FileFormat:=xlcsv, CreateBackup:=False ActiveWorkbook.Close End Sub
- PloughguyCopper ContributorYep. Nup. Didn't even make it to tomorrow.
The macro above creates a CSV file with the correct name and contents in the expected location. Whoopee!
But...
It then:
- changes the name of the main workbook to the CSV path;
- offers to save it too with "Your changes will be lost if you don't save them.
- the message bar (below the ribbon) says "Possible data loss: Some features might be lost if you save this workbook in the comma-delimited format."
So saving a single sheet as a CSV changes the name and file type of the whole workbook. But saving a single sheet as an xlsx file leaves the name of the parent workbook unchanged.
There is something very, very sick about saving as CSV
- temphelpCopper ContributorAfter 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"- pjotarCopper Contributor
temphelp : great addition/solution!
Additionally I might add that when I move the file, I also rename it to .ics - as I want to save the text as in icalendar File. So the steps are :
1 Check if the VBA runs on a Mac2 SaveAs csv with UTF8 Format in Office Folder
3 Move the file back, also renaming it at the same time
' Define csv file in office folder officeFolder = "/Users/xxxx/Library/Group Containers/UBF8T346G9.Office/MacOfficeFiles/Kal2ICScreator_utf8.csv" If InStr(UCase(Application.OperatingSystem), "MAC") > 0 Then SaveAs FileName:=officeFolder, FileFormat:=xlCSVUTF8 Name officeFolder As Save_Filepath End if
- Jonathan_B830Copper Contributor
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.