VBA google docs

[Marco82]

Hi everybody,
I am VBA newbie programmer winky smile... I would like access (in read/write mode) to spread sheet published by google docs suite, all in VBA code. At the moment, my code is like (sorry for Italian comments...):

Option Compare Database
' MainModule

' Variabili di connessione
'----------------------------

' stringhe di connessione per i vari workbook creati con google docs
Public Const connectionWorkbook As String = _
"

https://..."

' Varibile per gestire l'applicazione excel
'----------------------------

' oggetto di tipo Application
Dim excelApplication As Excel.Application

' workbook excel
Dim excelWorkbook As Excel.workbook

' worksheet excel
Dim excelWorksheet As Excel.Worksheet


' Routine per la gestione dei documenti creati con google docs e access
'----------------------------

' imorta il file excel in base al percorso specificato
' connectionString rappresenta l'url del file da importare
Public Sub ImportExcel(connectionString As String)
On Error GoTo ErrorMessage:
' crea l'oggetto excel
Set excelApplication = CreateObject("Excel.Application")

' ottiene il file desiderato
Set excelWorkbook = GetObject(connectionString)

' imposta il worksheet attivo da utilizzare
Set excelWorksheet = excelWorkbook.Worksheets(1)

' cancella variabili inutilizzate
Set excelApplication = Nothing
Set excelWorkbook = Nothing
Set excelWorksheet = Nothing

' gestisce gli errori
ErrorMessage:
MsgBox "Error number: " & Err.Number & " Description: " & Err.Description, vbOKCancel
End Sub

Thanks a lot for any help

 

[kjv1611]

Best I can tell, it looks like you'd have to do a lot of extra work to do what you're talking about. See this discussion:

http://www.google.com/support/forum/p/Google Docs/thread?tid=55044d589d2e0bc3&hl=en

What you might could do is just import the Google spreadsheet data into an Excel workbook, and do whatever code you need there, then re-upload to Google Docs, and allow it to convert back to Google's format.

 

[Gruuuu]

I actually held off on replying to this because I didn't have enough time to test the export feature to see if it was scriptable.

One course of action available to you is using the Google Doc as a data source. Once the document is created, you can send an XMLHTTPRequest to a specific URL. I know for a fact that you can retrieve data this way, not really certain on how to WRITE the data though.

Also this will only apply to the data, not the formatting.

http://code.google.com/apis/spreadsheets/

is going to be your best resource for this.
Believe me, I would really like to get into the details of this for my own purposes, but I just can't, I don't have the time.

 

[Marco82]

Thanks for your suggestions , I am writing VBA code to solve this problem... if I will solve it, I will post solution here ;-)