I've written VBA that reads emails when they arrive on my PC at home, extracts relevant details, looks up necessary info and saves or updates records in an Excel sheet. This enables me to leave my PC running and have it automatically load new orders while I am on the road. Because two of us (part-timers) take turns at deliveries (and I don't want the other user to have access to other personal files on my OneDrive account), the spreadsheet resides in a OneDrive account (I'll Call it QFM), and QFM has shared the folder with me, ROB, enabling me to save a local copy on my PC so my VBA can write to it.
I also have an AppSheet app that allows me and the other user to progressively record details to the same spreadsheet as we fulfill orders. Both of us log into AppSheet using the QFM account.
The problem I am having is that if my VBA adds/edits a record around the same time a user edits another record in the same spreadsheet, the Local PC version is saved on the web and destroys recent records saved by the AppSheet app. So at the end of every day I have to spend ages copying and pasting records from old versions from OneDrive.
I am after suggestions on how to either improve the sync with Excel, or use VBA to read and write to a cloud version of a Google spreadsheet (or the Excel spreadsheet). I am moderately proficient at VBA and a total dummy at Google Script (hence my reluctance to re-write the whole lot in Google Script)
I also have an AppSheet app that allows me and the other user to progressively record details to the same spreadsheet as we fulfill orders. Both of us log into AppSheet using the QFM account.
The problem I am having is that if my VBA adds/edits a record around the same time a user edits another record in the same spreadsheet, the Local PC version is saved on the web and destroys recent records saved by the AppSheet app. So at the end of every day I have to spend ages copying and pasting records from old versions from OneDrive.
I am after suggestions on how to either improve the sync with Excel, or use VBA to read and write to a cloud version of a Google spreadsheet (or the Excel spreadsheet). I am moderately proficient at VBA and a total dummy at Google Script (hence my reluctance to re-write the whole lot in Google Script)
