• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 161
  • Last Modified:

Is there a way to edit a Google Sheet using VBA?

I have written some VBA code that reads emails, deciphers what I need and updates (edit and insert rows) into an Excel spreadsheet. This works great but because the sheet is shared I am running into sync conflicts. These wouldn't happen if I could use my VBA to edit Excel. I am fairly confident with VBA but outside of that my skills are limited. Does anyone know of a way of achieving what I need?
0
Rob4077
Asked:
Rob4077
  • 2
1 Solution
 
Shaun VermaakTechnical Specialist/DeveloperCommented:
No, you need to use Google Apps Script
https://developers.google.com/apps-script/
0
 
Rob4077Author Commented:
Hi Shaun,

I actually found a sample (http://ramblings.mcpher.com/Home/excelquirks/exceldocsintegration/excelsheetsv4) that enable me to copy data from an Excel Sheet to a Google Sheet and back, and it does that well. Only trouble is that it is too technical for me to dissect and understand how it does it and if there is a way to read and write one line/cell at a time so I can read and check it on the way through. I don't want to run an Excel sheet and a Google sheet and try to keep them in sync, I'd rather just work with the Google sheet. I've included the code for the top level of the two functions that read and write. Of course these call other functions but I think all it's doing is copying the whole sheet in one go rather than by line.

I could use the Google script for some of what I need to do but then I run into the problem of how to read Outlook emails from Google and how to run a function I have that relies on a DLL created for use with VBA.

'// this wil get the active sheet from google
'// go can replace the active sheet name with some other sheets, or a list of sheets separated by commas
Public Sub getThisSheet()
    Dim result As cJobject
   
    '// get the data from a given sheet id, and match to the active excell sheet
    '// OAUTH2 will be taken care of if required
    Set result = getStuffFromSheets(getMySheetId(), ActiveSheet.NAME)
   
    '// success will tell us if it worked
    If (Not result.child("success").value) Then
        MsgBox ("failed on sheets API " + result.child("response").stringify)
        Exit Sub
    End If
   
    '// now write that data to the current sheet, clearing it first
    writeToSheets result.child("data").children(1).child("valueRanges"), True
   
End Sub
'// this puts the active sheet to google
'// go can replace the active sheet name with some other sheets, or a list of sheets separated by commas
Public Sub putThisSheet()
    Dim result As cJobject, clearFirst As Boolean
   
    '// set this to true if you want to always clear the sheet first
    clearFirst = True
   
    '// put the data from a given sheet id, and match to the active excel sheet
    '// OAUTH2 will be taken care of if required
    Set result = putStuffToSheets(getMySheetId(), ActiveSheet.NAME, clearFirst)
   
    '// just check that all went fine
    If (Not result.child("success").value) Then
        MsgBox ("failed on sheets API " + result.child("response"))
        Exit Sub
    End If
   
End Sub
0
 
Rob4077Author Commented:
Well, try as hard as I am capable and I can't get it to work the way I had hoped so, not surprisingly, you were right. Thanks for taking the time to comment.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now