Edit a Google Sheet line by line with VBA

51 views
Skip to first unread message

Rob atYahoo

unread,
Dec 29, 2017, 9:55:41 PM12/29/17
to Excel Liberation
I downloaded and implemented the code from http://ramblings.mcpher.com/Home/excelquirks/exceldocsintegration/excelsheetsv4 that enables me to copy Google Sheets to Excel and back. It works well and looks like it has potential as a starting point for what I need to build but I am after a little help. If this is not possible, or too difficult, then please let me know and I will pursue an alternative solution.

I have written VBA that reads incoming emails (from Outlook), dissects orders from those emails that are correctly formatted, opens an Excel spreadsheet, checks to see if the order is already there and, if not, the VBA then adds it to the end of the list. If it finds the record I am after it updates it if necessary, and as it goes through the spreadsheet it also checks for a few possible errors in the records (in case a user has incorrectly edited the spreadsheet). The code works well but I need the sheet to collaborate with other apps and Excel is not good at that.

What I want to do is replace the Excel spreadsheet with a Google Sheet. So basically I was wondering if/how to modify the above code (or find alternative code) to read a Google sheet line by line (Column A contains a unique Key). If the Key I am looking for isn't found then add a new record updating columns A to AN with variable values from VBA. If the Key has already been entered then it would be handy if I could check to see if any of the fields need to be updated. 

Unfortunately I have to stick with VBA because it uses a custom built DLL to calculate some info that is saved in two of the columns and I can't run that DLL with Google scripts.
Reply all
Reply to author
Forward
0 new messages