CODEWITHSUNDEEP
vbaexcelsave
Vbasic4now
Vbasic4now

Reputation: 589

excel saving with VBA issues and ways around it

I have a relatively involved sub that transfers a bunch of data around within a workbook in excel and does a handful of other things, then saves the file at the end of the sub using activeWorkbook.save

however for reasons unknown to me, when the file has been open for a long time and I save it gives me a message saying the file has been edited by another user and prompts me to either notify the other user and not save or overwrite any changes made by the other user. Is there a way to code the sub to make it overwrite no matter what, so that if that dialog box comes up, it doesn't prevent the sub from running?

Also, there is no one else editing the file so i don't know why its giving me the message in the first place, i just want it to bypass that if it happens to come up.

Thanks for the help!

Upvotes: 0

Views: 1267

Answers (1)

Ralph
Ralph

Reputation: 9444

I guess we have finally found a solution for this problem:

ThisWorkbook.Saved = False
Application.DisplayAlerts = False
ThisWorkbook.SaveAs ThisWorkbook.FullName, FileFormat:=52
Application.DisplayAlerts = True
If Not ThisWorkbook.Saved Then MsgBox "Saving the file failed." & Chr(10) & "Please try again later..."

The .Saved property of a workbook is readable and writable. So, the first line makes sure that this file is marked as "not saved". This is important because alters are disabled and we would not know if the file has been really saved (without this property).

Once the file has been attempted to save (here as .xlsm, you might want / have to change that) the .Saved property is read to ensure that the save actually worked.

Upvotes: 1

Related Questions