Excel Co-authoring Problems

Max Rockliff 1 Reputation point
2021-10-27T23:54:25.137+00:00

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:

  1. I successfully load the workbook on machine 1;
  2. I load the workbook (shared in OneDrive and using the same account);
  3. 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

Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
4,245 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Max Rockliff 1 Reputation point
    2021-10-28T23:00:08.977+00:00

    Update:

    Empirically what I have found is:
    I load the workbook on machine 2 (my account accessing OneDrvie)
    Everything initialises and runs as expected
    I load the workbook on machine 2 (also my account)
    I get an error telling me I can't upload my changes
    I choose discard my changes and the workbook seems to reload
    I get the upload error again and I choose discard again
    This repeats several times and the workbook eventually loads and seems to run properly
    In fact the AfterRemoteChange event triggers on machine 2 whenever I make a change on
    machine 1 directly or via the userform it presents
    I can't explain why I get these multiple upload errors only then to have the workbook finally load without error and work properly. I would expect either no errors (ideal) or continuous errors.

    When I close the workbook on machine 1, all the data in the data workbook appears to be up to date and recording all changes made via the userform on either machine 1 or 2, I also notice that the indicator next to the files in my OneDrive folder have a red cross indicating that there is sync error, requiring me to load the files in Excel to correct that sync error. I do (with the other machine unloaded) and all appears to be ok.

    As an aside, I've found that because when I make changes using my userform, several cells in the data workbook are changed in succession, the AfterRemoteChange triggers on the other machine a corresponding number of times. It would be good if I could cause it to only trigger the one time after all the changes have been made, to eliminate the multiple refresh cycles on the other machine.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.