Message bar detection.

[paulked]

I am using Office 365 and my file is saved to OneDrive. During general activity on the sheets everything is fine and AutoSave works ok.

But sometimes, eg when I update from an Access database and a macro can be running for longer that the AutoSave takes to kick in, the yellow message bar with an 'Unable to save' message comes up and then asks you to Save As. The file can't be saved with it's original name, presumably because the lock file is there, so I have to manually save as a temp file name and then back to its original name... a lot of faff!

Is there a way to detect when the yellow message bar pops up in VBA? I can then run a script to do the saving programatically.

Best regards

Paul Ked

[paulked]

I've cross posted this here https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-mso_o365b/message-bar-with-there-was-a-problem-saving-and/3e106611-a78a-49c7-8dd5-69079a0a6ef4?tm=1557341779226

I've also noticed it happens more frequently when there are Charts in the workbook.

[Jan Karel Pieterse]

Does it need the autosave when your macro runs? I mean, are you co-authoring the file with others? If you're 'alone' with the file, why not turn off autosave temporarily?

[paulked]

Can you do that in vba?

[p45cal]

Application.AutoRecover.Enabled = False
I haven't found anything about AutoSave intervals as yet.

I'm not sure whether autorecover info is the same as autosave (I see no intervals are specifiable for Autosave).
There's also a workbook-only version:
ActiveWorkbook.EnableAutoRecover = True/False

There's also an AutoSaveOn property of a workbook and Help on it goes to: https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.autosaveon?f1url=https%3A%2F%2Fmsdn.microsoft.com%2Fquery%2F dev11.query%3FappId%3DDev11IDEF1%26l%3Den-US%26k%3Dk(vbaxl10.chm199287)%3Bk(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue

which talks about OneDrive. Maybe you'll be able to:
If ActiveWorkbook.AutoSaveOn Then ActiveWorkbook.AutoSaveOn = False

If that fails you might try something along the lines of setting a boolean global variable, call it, say UpdatingFromAccess, and when doing your updating, set it to True, after it's finished, set it to False.
Then add a Before_Save event:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If UpdatingFromAccess Then Cancel = True
End Sub

[Jan Karel Pieterse]

It is AutosaveOn you're after if it comes to controlling synching for co-authoring. There is no interval, it autosaves after almost every change to the worksheet.

[paulked]

Thanks for the info. AutoSaveOn does work but only if the user is using OneDrive.

I've managed to get around this by checking the path of the workbook and then only using it if they are using OneDrive, hopefully this will cure it.

It would be nice if there were a way of detecting the error though, just in case :whistle:

Thank you

[p45cal]

It would be nice if there were a way of detecting the error though, just in caseWhat's the error?, and on what line?
I had hoped my suggestion:
If ActiveWorkbook.AutoSaveOn Then ActiveWorkbook.AutoSaveOn = Falseavoided one error. This is because if you're not using OneDrive (and perhaps SharePoint Online too) then the value of .AutosaveOn is False by default.

[paulked]

It kinda works, but only if using OneDrive, as trying to set it to True when not using it errors. I’ve been told that the message sometimes comes up when not using PneDrive, although I’ve never witnessed that myself!

thanks though, as I said, I’ve got round it.

[Jan Karel Pieterse]

You should save the current setting before turning it off and then at the end of your code test whether the saved setting was "on" and only then restore the setting.