I have a workbook application for which I want to allow co-authoring, but it's not working after a simple share to an external company account, or when I logon to OneDrive with my own account on both machines. I continue to get the message when loading on the second machine, telling me my updates could not be merged and do I want to save a copy, or discard my changes. Choosing the discard option causes the first machine to produce the same error message and just go around in circles with each machine in turn.
My setup is a combination of 2 workbooks, one for the application and one for the data (date/time stamped records in a simple flat worksheet). Opening the primary workbook causes through Worbook_Open macros to open the second, data workbook. The primary workbook does have a pivot table referring to the data workbook (which is not refreshing at this time). On opening the primary workbook, the Workbook_Open routine opens the secondary, data workbook and initialises some string ranges based on the data workbook and some OPublic variables - some from the data workbook and some relating to initial size values for the userforms that are loaded later.
The primary workbook presents a userform with a list of activities and records the selected activity details to the data workbook, but again, not at the loading stage, only after the userform is activated later on. The data recording, when it happens, is driven by macros that use the command format Workbook(DataFilename).Worksheets("Records").Range(CurrentRow) = value, with additional similar commands including the ".Offset(0, col) to right to adjacent cells in the row. However, these macros don't execute until the userform is activated and actions taken.
I also use the Workbook_AfterRemoteChange() event routine in the data workbook to flag that the other machine that a change has been made, which subsequently calls a routine (same as called from the workbook_open) to update the public variables and reinitialise a userfom if is it being displayed. The current row number of the data is stored in a variable in memory, so a refresh of that value is essential along with an update of the last task selected on the other machine to automatically reflect on the userform, which would also be refreshed. That said, none of this happens are load time, only the redefinition of the named ranges on the data worksheet.
The problem I encounter is as follows:
- I successfully load the workbook on machine 1;
- I load the workbook (shared in OneDrive and using the same account);
- I get an error on one or both machines telling me Excel was unable to upload/merge the workbook and prompting me to save a copy or discard my changes. This then ping-pongs between machines if I select discard my changes (saving a copy would be futile)
I have found that if I load the workbook on machine 1 (successfully), then load it on machine 2 with a stop command at the top of the workbook_open, then use the F8 step to step through the initialisation routines, the workbook will sometimes load correctly. On occasion, using this technique, I've even been able to get the workbook application to work for a short while, with updates on the userform on machine 2, properly triggering and executing the update/refresh on machine 1 through the AfterRemoteChange routine in the data workbook. But it will eventually fail with the upload error message - Save a copy/Discard Changes.
The workbook is heavily macro driven as the user generally doesn't interact with the worksheets directly and certainly not to record any data - this is all controlled through userforms. The upload error can occur at any time as far as I can tell - maybe as soon as the second machine loads it, or slightly later, perhaps even after it has run properly on both machines for a very short while (< 1 minute I'm estimating).
Is it possible that the periodic Autosave may somehow be triggering the upload fail error condition? I've done quite a bit of debugging, inserting various stop commands and stepping through my macros,
My workbook uses and Ontime function to launch the main userform at 15minute intervals, or I can launch it manually. The userform also employs an Ontime routine to update the date and time in a label field on the userform every minute, but that's all it does - it doesn't change the workbooks.
Both machines are running the same Office version - Version 2102 (Build 13801.20969 Click-to-Run). They are one different company domains, but the access to the OneDrive shared files is via the same user account, belonging to the Office account of the primary machine. Is it possible that because the workbook is running on the second machine in the same user context that OneDrive is getting confused? My intent is to allow the workbook to be loaded on a machine on the person's own company PC, then to be able to access the workbook, using that same account from a machine on another company's system. To this end, the secondary machine uses the same Office credentials of the first machine to access both the shared workbooks.
I've setup a simple shared workbook with only the Workbook_AferRemoteChange() to produce a MsgBox whenever a change is made on the other machine and this works great, with no problems at all. I've even tried setting up simple pivot tables and other tables to see if they produce a problem - they don't - refreshes and update on one reflect almost simultaneously on the other as does the AfterRemoteChange event trigger. Other than the AfterRemoteChange macro, this test workbook has no other macros and no userforms.
The workbooks and the macros would be too large and complex, not to mention the issue of intellectual property to share at this stage, but any ideas you may have through your own experiences would be great to hear about.
Many Thanks,
Max