VBA - Save as CSV - error 1004 cannot access file

Question

Excel 15 for mac

Im trying to run an edit on a file and save the file as a CSV. I once was able to run the code but now I receive "Runtime error 1004: cannot access 'filename'"

Neither the files nor the directories have changed so I'm not sure how to debug this. Where is my issue?

Sub Macro5_eggplant()

'

' Macro4_eggplant Macro

' prep and save as

'

'

    Dim newname As String

    With ActiveSheet

        newname = Left(.Parent.Name, InStrRev(.Parent.Name, ".") - 1)

        .Rows(1).Delete Shift:=xlUp

        .Columns("C:E").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

        .Range("C2").FormulaR1C1 = "replace" ' or = .Parent.Name ?

        .Range("C2").AutoFill Destination:=.Range("Table4[Column1]")

        .Parent.SaveAs Filename:=("/Users/matthewhughes/desktop/txtlogsheets/") & newname & ".csv", _

                        FileFormat:=xlCSV, _

                        CreateBackup:=False

    End With

End Sub

***Post moved by the moderator to the appropriate forum category***

Answer 1

Go to the Help menu and choose Check for Updates. In AutoUpdate app choose Insider Fast. Install the beta build.

The VB Editor has been fixed for the problem you described in this build. If you don't want to run a beta build, then wait until 15.32 reaches general release.

Answer 2

I tested your code in Excel 15.32 and confirm that there is a bug in VBA. Your syntax is correct. This should work.

Please click the Smile button in Excel and alert Microsoft's Excel team that they have a bug to fix.

Thanks.

Answer 3

So this partially solved the problem with the debugger. Now it tells me the issue is with the code

.Parent.SaveAs Filename:="/Users/matthewhughes/desktop/txtlogsheets" & "/" & newname & ".csv", _

                        FileFormat:=xlCSV, _

                        CreateBackup:=False

Other than that i am still lost since it worked before and now doesn't.

Answer 4

Thanks for checking! 

I believe the issue is with the save as line but the debugging wont show me where it is.

This Code was never run on a PC.

As far as version goes excel v15 is part of office 2016 for mac.

Answer 5

You have not indicated which line of code you get the error so I am assuming that it is probably the "Save as" line.

At the top of the post it indicates you are using Excel in Office 2016 and then in the post you have Excel 15 for mac.

I am wondering if you were previously running this on a PC and now on a Mac in which case I would suggest that the "Save as" code needs editing for the Mac. 

Please confirm if previously run on a PC and if it is the "Save as" line where you get the error. If it is the PC to Mac problem then sure someone with a Mac will be able to edit the code.