Excel Co-authoring problems

Question

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).

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 suspect it may be something that happens when I load the primary workbook, but no data is written to the data workbook at that stage. The only thing that does happen when I open the data workbook is that the Workbook_Open routine will reinitialize some named ranges in the data workbook - ultimately these named ranges are only used to build/refresh primary workbook pivot table - again not at load time. This is essential as the workbook does not keep the data in a table and must figure out where the last row of the data is, especially when more than one machine is updating that data adding new rows. In fact my intent is to use the Workbook_AfterRemoteChange() event routine to flag to the other machine that a change has been made. 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.

Just one more thing/complication: To avoid users trying to manually enter data or edit records etc. stored in the data workbook (requiring specific formats, formulas, etc. after I load the data workbook from the primary workbook, I also hide its window using Windows(DataFailename).visible = False. I have to say that anecdotally I have found that the AfterRemoteChange can (not always as per the above) fail to trigger and this may be if the data workbook is invisible in this way. I just don't know because the aforementioned problem is happening far too frequently to test this part.

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.

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

Answer 1

Ok. Here’s and update:

I’m still experiencing the same problem.

I’ve eliminated the use and redefinition of named ranges during the workbook_open routine which had no effect unfortunately

But I inserted a stop command at the beginning of the workbook_open routine and tried stepping through to see if I could find when the condition (failed to merge - save a copy/discard) occurred and I found two interesting results:

1. The condition seems to be triggered when in my primary workbook’s Workbook_open routine executes the command, Set DataWkBook = Workbooks.Open(RecordsName), where RecordsName is string representing the path and file name.
2. At that point my F8 VBE stepping seems to continue as if I’d pressed F5 to run.

The RecordsName file does appear to open but it’s at this time I get the error on the other machine telling me I can merge my changes. Because of the loss of VBE stepping at this stage I can’t see if it’s a later vba command that may be causing the problem so it’s al but weird.

Edit

Ok. Hold that thought. I put a stop command either side of the workbooks.open command and got hold of my stepping in VBE and the error didn’t trigger. However, it did trigger elsewhere in the code at a point where I’m initialising a userform that uses data from the secondary workbook.

My code is quite complex so I’m going to have to dig/debug further if I am to find a single command or function that is triggering this New Version available condition.

It’s all getting weirder.

Answer 2

Hi

Not it’s not yet resolved. I am in the process (slow as it is) to eliminate the reliance on named ranges and deferring to strings that define a cell range. Unfortunately my code has a lot of such references so the edit will take some time

I am only working on the theory that the process of redefinition the named ranges in the data workbook as part of the workbook_open routine in the primary application workbook.

Answer 3

Hi,

I am following up on this thread to know if your problem has been solved. Feel free to share updates at your convenience time.

Regards,

Christophe

Answer 4

It’s a business account and I can share externally but I have also had the exact same when logging on to OneDrive with the same primary sharing account on both machines to eliminate access possibilities.

Answer 5

You stated:

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

Question number 1:

Is this a personal or a work account that you are sharing from?

If its a work account ask your IT Administrator about your problem, they might have Group Policies in place relative to sharing to outside company domains.

If personal account, please indicate which subscription you have, is it enterprise grade or business grade or just plain personal grade?