Forum Discussion
Excel365 and SPO - I despair
Looking for some feedback from our resident boffins.
I have many Excels that link to other Excels. All these Excels are stored on Sharepoint Online (SPO). All our Excels use VBA, so working in TEAMS / online Excel is not an option. So all users synchronize the SPO libraries that hold those Excels and manipulate the files in local Excel. OneDrive (OD) keeps SPO updated.
Yesterday I was travelling again and using these Excels offline amounted to a total car crash.
What happens is this: Although we point the source for these links (Data/Edit Links/Change Source) to OD, when working with SPO present, Excel morphs these links to point to SPO. When you attempt to Open Source on the road, you don't get anywhere. Excel just hangs attempting to open SPO.
I noticed something else. I use Auto_Open in VBA to set AutoSaveOn=False. This property should be available at all times. It is not. When offline, the assignment blows up. I now trap this blow-up in my code as a proxy for "you are offline" and where ordinarily I would attempt to open the linked Excel, I now warn the travelling user they must repoint all links.
(There is a way for your machine to map from OD to SPO but there is no way to map from SPO to OD, so I cannot repoint these SPO links from VBA to OD.)
This was annoying enough yesterday, but today I am back in the office and am livid. The same Excels which I repointed offline to OD yesterday no longer work in the presence of SPO. Check Status says "Error: Source not found" even though the OD source IS OPEN. To make things work again, I need to repoint each source literally to itself.
This is utterly insane. Anyone got any experience with this? Recommendations?
Update: Having reflected on this insanity overnight, it seems to me that there are two Excels or two modes in which the Excel app operates. There is clearly a code base for working offline that does not know AutoSaveOn=false. On reflection, this would actually be my preferred code base for our environment. This code base probably does not allow live spreadsheet sharing and versioning and whatever, but that would be ok. OD is good enough. Which prompts a question: In the presence of an internet connection, how can I trick Excel into the offline mode?
1 Reply
- By pure fluke, I stumbled upon the answer to my question. When you go into One Drive/Settings under Office there is a flag "Use Office applications ...". When you uncheck this, SPO/OD revert to a working model that is akin to Dropbox, Google Drive et al. Unchecked the AutoSaveOn property simply disappears. I am "offline". Obviously, all the fancy real-time collaboration features disappear but so does all the grief bemoaned above.
In the ultimate ironic twist of fate, many of the frustrations that led us to abandon Dropbox (and switch to SPO) however also reappear. Office apps establish locks which they are VERY reluctant to release. So when you are in an Excel workbook and press Save in the deliberate attempt to upload an image, this DOES NOT happen until Excel releases the lock on that workbook. If you are VERY LUCKY, Excel will release that lock upon you closing the workbook. If OD were not so sluggish to respond to changes on the local drive, it would start uploading the second you close the file and you reopen it right away and all would be well.
Given how sluggish OD is, you might find you need to fetch a coffee before you reload. Given the uncertainty when these locks are dropped, however, you might find that after you got the coffee, nothing happened. To be positive that these locks are dropped, you would really need to quit Excel altogether.
An easier workaround concerns downloading new copies of linked workbooks. When you close the workbook, OD will likely not sync because the links from other open workbooks do not move Excel to drop the lock on the linked (and now closed) workbook. But, incongruously (I would argue), Windows does allow you delete the workbook. So, as fortune favours the brave, I delete the workbook from my local drive. With my heart in my mouth I then wait a moment after which OD will normally restore the file from SPO. On the odd occasion where this operation goes wrong (and I actually delete the file from SPO), I need to restore the file to SPO...
