4

I am running a VBA macro in Excel 2016 for Mac. The macro works on Windows platforms with Excel 2016, and on Mac platforms with earlier than the 2016 version. The issue appears specific to Excel 2016 for Mac when trying to export a CSV.

The code is supposed to allow the user to click a button, which will then export an active worksheet to a CSV file. While a similar issue was documented here (Getting "method saveas of object _workbook failed" error while trying to save an XLSM as CSV), unfortunately while changing xlCSV to 6 worked for them, this has not worked for me.

The code works up until the ActiveWorkbook.SaveAs Filename:=newFileName, FileFormat:=6, CreateBackup:=False line, which then throws the error:

Run-time error '1004':Method 'SaveAs' of object '_Workbook' failed

If I change FileFormat to 51 (.xlsx) or 53 (.xlsm) the code will successfully finish. However, if FileFormat is set to 6 (.csv) the code will throw the error above. I am unable to SaveAs xlCSV or xlCSVMac.

My full script is below:

Sub btnExportCSV_Click()
Dim oldFileName As String
Dim newFileName As String
Dim timeStamp As String
Dim fileAccessGranted As Boolean
Dim filePermissionCandidates
Dim wsPath As String

timeStamp = Format(Now, "yyyymmddhhmmss")

wsPath = Application.ThisWorkbook.Path

oldFileName = ThisWorkbook.FullName
newFileName = Mid(oldFileName, 1, InStrRev(oldFileName, ".") - 1) & timeStamp & ".csv"

' Check if software is Office 2016 for Mac
' Documentation for this comes from https://dev.office.com/blogs/VBA-improvements-in-Office-2016
#If MAC_OFFICE_VERSION >= 15 Then
    filePermissionCandidates = Array(wsPath)
    fileAccessGranted = GrantAccessToMultipleFiles(filePermissionCandidates)
#End If

Application.DisplayAlerts = False

Sheets("OfflineComments").Activate
Sheets("OfflineComments").Copy
ActiveWorkbook.SaveAs Filename:=newFileName, FileFormat:=6, CreateBackup:=False
ActiveWorkbook.Save
ActiveWindow.Close

MsgBox ("Offline comments exported to " & newFileName)

Application.DisplayAlerts = True
End Sub

I have tried:

  • Adding a full path to the input/output file names
  • Ensuring that version of Excel is checked for and permissions allowed by user
  • Various file types for the FileFormat parameter, but as mentioned, only two types actually worked.
  • Trying the code mentioned in the above-linked article, which did not help (and which is why I am posting the original code here).
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Kate
  • 133
  • 2
  • 17
  • There is some updated information here, now: http://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_mac/vba-macro-in-excel-2016-for-mac-saveas-will-not/86d6855f-241d-4b8d-b233-cccd7184c67b The issue appears to do with the new permissions issues that arise with Apple's sandbox, for those interested. – Kate Sep 16 '16 at 19:58

3 Answers3

1

I Think its a bug, i have the same problem and i use a workaround:

ActiveWorkbook.SaveAs newFileName, CreateBackup:=False
Kill newFileName
ActiveWorkbook.SaveAs newFileName, FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close

This save my Workbook first as original file (xlsx), delete this and save as csv, its works for me.

Adam
  • 11
  • 1
0

Try this:

Application.ThisWorkbook.SaveAs ("C:\User\Folder\test.csv")

Also delete the line ActiveWorkbook.Save . You are double saving it. I am using Excel 2010 and this work perfectly on my machine.

TheGuyOverThere
  • 130
  • 11
  • Thanks for the suggestion! Unfortunately it doesn't work as expected. While the code will run and save with the appropriate name and CSV file extension using this line `Application.ThisWorkbook.SaveAs (newFileName)` the actual format of the file does not appear to be a CSV. I get errors that the format and extensions do not match when I attempt to open, and all formatting is still present. Additionally, using the `ThisWorkbook` object will cause the code to actually rename the original and doesn't seem to preserve a version of the original sheet which I would like to keep. – Kate Aug 26 '16 at 15:43
  • Changing that line to `Sheets("OfflineComments").SaveAs Filename:=newFileName, FileFormat:=6, CreateBackup:=False` worked for awhile, but then randomly stopped working again. Now throwing the error `Run-time error '1004': SaveAs method of Worksheet class failed` – Kate Aug 26 '16 at 17:09
  • Hmm, well if it works at the beginning and them stop working later, my guess would be that it probably has something to do with your path. Does it have somekind of illegal characters, or double spaces maybe? – TheGuyOverThere Aug 26 '16 at 18:13
  • Thanks again for the suggestion. I have checked the paths using `MsgBox` and they looked fine to me. The only way it could be the path is if somehow the path had changed in between my attempts, which it hasn't (visibly). The only thing I can think of is that there are known permissions issues with OSX https://dev.office.com/blogs/VBA-improvements-in-Office-2016 but I'm not sure how that would influence it in the background. – Kate Aug 26 '16 at 19:00
0

Try changing the permissions to explicitly request the new filename instead of the path by changing wsPath to newFileName:

#If MAC_OFFICE_VERSION >= 15 Then
    filePermissionCandidates = Array(newFileName)
    fileAccessGranted = GrantAccessToMultipleFiles(filePermissionCandidates)
#End If
user6853416
  • 11
  • 3
  • Thanks for the suggestion @user6853416, but that didn't work unfortunately. Interestingly, it now won't prompt me for access to the folder, but I still received the same error on the `SaveAs` command. – Kate Sep 20 '16 at 20:10