May 02 2024 03:57 AM
I have a big xlsm file (18mb) which a user updates on a daily basis.
It doesn't come onto my OneDrive for a considerable amount of time, it was timed at 23 minutes today, I don't have other historical times. OneDrive says "Your files are synced".
However, if I pause and restart OneDrive, the file comes immediately.
My IT Support don't know why this is happening and they have removed OneDrive & re-installed, reset OneDrive and they think its some combination of being a large file and/or being a Microsoft Macro-enabled workbook, so xlsm. It has to be a Macro-enabled workbook as it does stuff with external data and puts it into a certain format, via VBA code.
Anyone seen something like this before ?
May 02 2024 05:26 AM
May 02 2024 10:01 AM
@Mike Williams Hi Mike, thanks for the response. There's no images in the file, its full of data. Its akin to a "Data Warehouse".
I think we trialled to see how it synced in the building and found its slow on multliple users OneDrive, but I would have to research that. I would also have to research if there are other big files that take a long time to sync, or is it just this one we happened to notice ?
Do you have some tests we could do to narrow it down, what's the cause ?
May 02 2024 10:11 PM
@Pierino I confess I get chills seeing so much data inside a spreadsheet, rather than referenced through data queries to more robust stores. I've seen a **lot** of spreadsheet corruption issues in banking contexts. I know Microsoft used to exercise Excel on Wall Street spreadsheets but still...
With respect to the size, 18MB is not big - it's a handful of smart phone photos. I've synced multi-gigabyte files that are literally a thousand times larger.
If you have multiple devices syncing this file (particularly in a keep local copy scenario), then Excel has to martial all of them to check currency. It would only take one device with network or file contention issues to slow down everyone.
Other random thought: there is perhaps some "signature" that is being checked in the file XML and data that might be slowed by security algorithms - or even just the nature of it being a macro-enabled file triggers additional checks. (Multiply this across all copies of the file).
See also: https://www.reddit.com/r/excel/comments/112ahmw/solution_enabling_macros_on_workbooks_from/