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

Copper Contributor

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.

6 Replies

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.

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

 

 

 

 

 

 

 

Yep. 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

@Ploughguy 

>>It then:
>>- changes the name of the main workbook to the CSV path;

 

I am working on the same overall problem with saving as .csv on a Mac, and while working with your script modifications i also encountered the issue where the main workbook has its name changed. 

 

A couple notes:

  1. I am working out of an XLSM, call it Master.XLSM.
  2. The first half of the script copies a worksheet, edits the copy, and then moves the copy into a new book.
  3. Second half of the script attempts to name the new book Child.csv.

Here's what happened:

  1. I attempted to use your code to first save as Child-xl.xlsx, then save a second time as Child-cs.csv. In my attempts, I noticed that the "-xl" and "-cs" didn't affect anything for me.
  2. If I run the script without the MsgBox lines, I can save the new file as Child.xslx, but once I attempt Child.csv, I get an error.
  3. If I run the script with the MsgBox lines, Master.XLSM gets saved under the name Child.xlsx, saving as Child.csv triggers the error, and the new file remains simply "Book1".

My guess is that, for the scenario above, when a MsgBox is triggered, the sheet containing the macro becomes the ActiveSheet. Which would explain why my PERSONAL.XLSB got corrupted when I attempted running the script from there instead of Master.XLSM.

 

 

@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.

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"