Can I use VBA to update my on-line spreadsheet and bypass constantly failing sync?

New Contributor

I have some extensive VBA code that reads emails, deciphers information, looks up additional data and updates the information in an Excel spreadsheet that is shared between two user Office 365 accounts.  The other user also updates the same spreadsheet on line (actually it's done via an AppSheet app). 


The problem is that I frequently lose data. When the online user updates information, and at a similar time my VBA updates data on my PC and then syncs, the PC version over-rides the on line version and on-line updates are lost. At the end of each day I need to laboriously go through version histories on line copying data from one copy to another to come up with a final result for the day.


AppSheet suggest I use Google Sheets instead of Excel, but that would mean a major re-write of my VBA, so I was hoping someone might have a better solution.


Ideally I would like to create a virtual drive link to the on-line version of the spreadsheet and get the VBA to update that. I know that could be done if the spreadsheet was not shared between two users, but because it is, Microsoft won't support the virtual drive link.

6 Replies
I'd expect things would work if you would each update a different area (different table or sheet) of the file. That way you avoid most trouble I think.

Thanks for the suggestion. That may work but it doesn't solve my problem.


In my case the VBA processes orders. So it either add records to the spreadsheet so the other user can process, or it updates records such as when an existing order is called up for delivery. I can receive as many as 40 orders during a business day. The other user (using AppSheet) updates those records as each order is processed. That means as many as 25 updates per day. 


Unless I don't understand your suggestion correctly, it sounds like your idea of having them on different sheets would mean the process loses its integration. 

Hmm. This problem calls for a database solution if you ask me.

Yeah, that's what I was afraid of. Unfortunately I can't justify the expense, and my expertise is limited to moderate VBA. Looks like I may need to dump Microsoft, learn Google Script, and re-write the VBA in Google Script, since that updates direct to cloud and avoids sync issues. 

I've heard MSFT are working on improving the syncing, which is soon to be released.

I hope it's soon! I only do about 40 - 50 jobs a week and it's painful having to keep manually fixing