Use VBA to read and edit Google sheet or create reliable way to update to Excel on the cloud


New Member
Mar 3, 2019
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)

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...