I have a rather complex Excel workbook with many complicated array formulas, pivot tables and charts, and macros that I share on Teams using the Excel co-authoring capability. Of course, since the workbook contains macros, my concurrent users are opening it in the desktop app. The workbook operates quite normally when only one person has it open, but when 2 or more people have it open, we get Upload Failed - Discard you changes, Save a copy intermittently, but annoyingly frequently.
I have posted on answers.microsoft.com, but apart from some standard suggestions (e.g. remove workbook and worksheet protection, disable pivot table refresh on opening, etc), all of which I've implemented as advised, the workbook continues to get the intermittent Upload Failed messages meaning we keep losing our changes. The workbook does take 30-40secs to open and 30-40secs to recalculate, so I've disabled automatic calculation. It will even trigger the upload failed when one of the users has it open, but minimised and another user is actively working (usually for the active user).
The workbook is just shy of 20MB and has several tabs, pivot tables and spill functions. It also contains, as its main information store on one worksheet, a large-ish table (585 rows and about 100 columns. I use structured referencing in all my formulas as they are either used to calculate column values in the table, or create separated pivot tables and charts. Some of my table formulas refer to entire columns.
Does anyone know what could be causing this problem? I'm getting to the point where I'm thinking about just having it on a shared drive as a single-user access just because we keep losing our updates.
At this stage, I'm only reasonably sure of one thing that will trigger an Upload Failed and that is if one of the active users makes changes to the macros in the VBE. This makes sense to some degree, as I understand the Excel dynamically compiles all the macros on load. So a change to a macro on one user machine, would likely confuse the others - This is avoided.
As I mentioned, we do know that the workbook is quite stable when only one person has it open at any time.
I have to admit that the workbook has become larger and contains more spill formulas (to create reporting tables) than it did 12-months ago, and, anecdotally, I'd have to say the occurrence of the Upload Failed has increased as the workbook as grown and become more complex.
Seems to me that co-authoring may not be all it's cracked up to be and may be buggy.
Unfortunately, the workbook is my company's intellectual property, and it cannot be shared. Being so large it would most likely exceed the upload limits anyway.
Thx
Max
I have posted on answers.microsoft.com, but apart from some standard suggestions (e.g. remove workbook and worksheet protection, disable pivot table refresh on opening, etc), all of which I've implemented as advised, the workbook continues to get the intermittent Upload Failed messages meaning we keep losing our changes. The workbook does take 30-40secs to open and 30-40secs to recalculate, so I've disabled automatic calculation. It will even trigger the upload failed when one of the users has it open, but minimised and another user is actively working (usually for the active user).
The workbook is just shy of 20MB and has several tabs, pivot tables and spill functions. It also contains, as its main information store on one worksheet, a large-ish table (585 rows and about 100 columns. I use structured referencing in all my formulas as they are either used to calculate column values in the table, or create separated pivot tables and charts. Some of my table formulas refer to entire columns.
Does anyone know what could be causing this problem? I'm getting to the point where I'm thinking about just having it on a shared drive as a single-user access just because we keep losing our updates.
At this stage, I'm only reasonably sure of one thing that will trigger an Upload Failed and that is if one of the active users makes changes to the macros in the VBE. This makes sense to some degree, as I understand the Excel dynamically compiles all the macros on load. So a change to a macro on one user machine, would likely confuse the others - This is avoided.
As I mentioned, we do know that the workbook is quite stable when only one person has it open at any time.
I have to admit that the workbook has become larger and contains more spill formulas (to create reporting tables) than it did 12-months ago, and, anecdotally, I'd have to say the occurrence of the Upload Failed has increased as the workbook as grown and become more complex.
Seems to me that co-authoring may not be all it's cracked up to be and may be buggy.
Unfortunately, the workbook is my company's intellectual property, and it cannot be shared. Being so large it would most likely exceed the upload limits anyway.
Thx
Max
