How to write a Google spreadsheet from Excel 2003 VBA

Question

I have an Excel 2003 file with a line like this:

I need to press the "button" and add this row as the last in the Google spreadsheet

Similarly:

Is it possible?

Should I use google command line tools?

Is there a better way? Easy way?

How do you do this?

(as soon as I know how to add “stuff” from VBA to Google Docs , how can I add it to the last line?)

Additional information: I have an Excel 2003 program that saves all company sales (with customer information), and I would like to make a global address book that is easily updated by my employees (not this).

Answer 1

You do not need OAuth or table APIs. Google Spreadsheet allows you to enter data in a simple form, which also means that HTTP POST will do the trick. You just need to prepare a table for receiving data through the form as follows:

• Log in to your Google Docs account
• Create a table or open an existing one
• Click "Tools" / "Create Form"
• Add something to the form description to enable the save button.
• Save form
• Copy the form value displayed in the link at the bottom of the form creation page.
• Now you can send a simple column to a table without OAuth

Now you can test the curl entry if you have one on your system (replace the form placeholder with the form key from the 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" 

Then we will try to execute the POST form from our Excel sheet through the following code. Add the link to "Microsoft XML, v3.0" earlier. Replace column 1 with the 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

Answer 2

I started answering your question, but realized that it was much less trivial than I thought when I started playing with the OAuth 2.0 API . I think it would be a lot easier if you could publish your Google spreadsheet, but I doubt it makes sense with sales data.

The reason this is not trivial is part of authentication. The OAuth ASP below can probably be used with some work, but I noticed that it uses session variables and some other ASP objects, so you will need to make a lot of settings.

In this light, here is my original answer if it helps.

There is a google spreadsheet API: https://developers.google.com/google-apps/spreadsheets/#adding_a_list_row

The OAuth 2.0 link to which the document table belongs is out of date. You can play with OAuth requests here , which should help you get started.

API functions are called with GET / POST requests with XML, which you can call using the XMLHTTP object.

First specify the Microsoft XML link in the Excel project (Tools-> Links-> Microsoft XML, v6.0)

In your VBA, you essentially use the following to send XML requests:

 Dim x as MSXML2.XMLHTTP Set x = New MSXML2.XMLHTTP x.Open "POST", "http://example.com/", False x.Send "<xmldata></xmldata>" 

You should be able to adapt this OAuth 2.0 ASP library for your VBA code.

This is an ASP example on how to use this OAuth library; again, since ASP and VBA use VBScript syntax, perhaps it would be adaptable.

Answer 3

• Instead of using VBA in Excel, use a full-fledged VB.NET application with Excel Interop COM. This syntax is much similar to VBA commands, but it will be much easier to make the transfer to Google documents in VB.NET.

• Use the Google Docs API List to create a new spreadsheet.

• Use the Google Spreadsheets API to move data to your spreadsheet.

Both Google APIs are REST APIs, so you will need to use the HttpRequest objects in VB.NET. Here's a great example on how to use them, just change the URLs to match Google. Google Sheets even offers a library that abstracts out many of these steps.