How to write to a "Google Spreadsheet" from Excel 2003 VBA

vba google-docs excel-2003 google-sheets
Diego Castro picture Diego Castro · Mar 16, 2012 · Viewed 23.9k times · Source

I Have an Excel 2003 file with a line similar to this:

enter image description here

I need to click "the button" and it adds that line as the last one on a Google Spreadsheet

Similar to:

enter image description here

Is it possible?

Should I use the command-line Google tools?

Is there a better way? Easier way?

How would you do it?

(once I know how to add "stuff" from VBA to Google Docs, how the f do i add it to the last line?)

More info: I have an Excel 2003 "program" that saves all of the company's sales (with the customer info), and I'd like do make a global address book that's easily updated by my (non it) co-workers.

Answer

ChrLipp picture ChrLipp · Mar 26, 2012

You don't need OAuth or the spreadsheet API. Google Spreadsheet allows data entry with a simple form, which means also that a HTTP POST will do the trick. You just need to prepare your spreadsheet to accept data entries via a form as follows:

  • Login to your Google Docs account
  • Create a spreadsheet or open an existing one
  • Click on Tools / Create a form
  • Add anything in the form description just to enable the Save button
  • Save the form
  • Copy the formkey value displayed in the link at the bottom of the form creation page
  • Now you can issue a simple post into the spreadsheet without OAuth

You can test the entry now with curl if you have it on your system (replace the formkey placeholder with the formkey from your table):

curl.exe -v -k "http://spreadsheets.google.com/formResponse?formkey=<formkey>" -d "entry.0.single=test&entry.1.single=test2&pageNumber=0&backupCache=&submit=Submit"

Next we try to execute the form POST from our Excel sheet via the following code. Add a reference to "Microsoft XML, v3.0" before. Replace column1 with your desired values.

Dim httpRequest as XMLHTTP
Set httpRequest = New XMLHTTP
httpRequest.Open "POST", "http://spreadsheets.google.com/formResponse?formkey=<formkey>&amp;ifq", False
httpRequest.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
httpRequest.Send "entry.0.single=" + column1 + "&entry.1.single=" + column2 + "&pageNumber=0&backupCache&submit=Submit"

'Check result in the following vars
httpRequest.status
httpRequest.statusText

Hope that helps

Related questions

Stepping through code in Google Apps Script (equivalent VBA-GAS )

When writing my VBA macros I often used "F8" so as to debug the macro line by line. Is there a similar feature in Google Apps Script?

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
How do I declare a global variable in VBA?

I wrote the following code: Function find_results_idle() Public iRaw As Integer Public iColumn As Integer iRaw = 1 iColumn = 1 And I get the error message: "invalid attribute in Sub or Function" Do you know what I did wrong? I tried …

Read More