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

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