0

We have been using some VBA Code in microsoft Excel 2011 with no faults for a long time. We are in the process if upgrading to Office 2019 but the VBACode keeps giving me errors. Eg when using the very simple code shown below to save the current file as WRS.xls

I have tried saving the file as a .xlsm

Private Sub CommandButton14_Click()
UserForm1.Hide
ChDir "/Volumes/frontoffice/Trading Activity"
    ActiveWorkbook.SaveAs Filename:="/Volumes/frontoffice/Trading Activity/WRS.xls" _
        , Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    Application.Run "WRS.xls!Back_to_Switcboard"
End Sub

I get a

Runtime Error 1004 Cannot access read only document message

If I delete the existing file called WRS.xls before executing the code the code works - but that is not what i need to achieve

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
user12144559
  • 1
  • 1
    What do you need to achieve if you don't intent to overwrite the existing file? – John Alexiou Sep 30 '19 at 18:21
  • Try explicitly deleting the existing file first? A number of things had to change in Excel for Mac, because of how Apple now forces a sandboxed environment for applications - this looks like it could be one of them. – Mathieu Guindon Sep 30 '19 at 18:30
  • I am scared to see that trading activity is handled with Excel/VBA and the myriad of quirks and bugs it has. Yikes! – John Alexiou Sep 30 '19 at 19:06
  • Thanks Mathieu How can I explicitly delete the existing file first please ? – user12144559 Oct 01 '19 at 08:06
  • Thanks ja72 - You are right the idea is to overwrite the file, I mean i do not want to have to manually delete it each time – user12144559 Oct 01 '19 at 08:07
  • If you are in windows environment you have access to a [FileSystemObject](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/filesystemobject-object) by importing the [Microsoft Scripting Runtime](https://stackoverflow.com/a/5798392/380384) library and using operations such as `.DeleteFile()` – John Alexiou Oct 01 '19 at 17:09
  • HI ja72 We are using excel for Mac, – user12144559 Oct 03 '19 at 07:20

0 Answers0