Access Google spreadsheet's File menu -> Download As -> Microsoft Excel (.xlsx) without using Sendkeys

[XcelMan]

FYI, I am using Microsoft Excel 2007 in Windows 7.

Is it possible to click on a Google spreadsheet's File menu -> Download As -> Microsoft Excel (.xlsx) without using VBA's Sendkeys command? So far, I know how to login to my Gmail account and navigate to a Google spreadsheet. Here's the code that I have so far:

Sub Test_GS()

Dim HTMLDoc As HTMLDocument
Dim oBrowser As InternetExplorer
Dim oHTML_Element As IHTMLElement

Set oBrowser = New InternetExplorer
oBrowser.Height = 1000
oBrowser.Width = 1000
oBrowser.Silent = False

oBrowser.Navigate "https://www.google.com/accounts/Login"
oBrowser.Visible = False

Do
Loop Until oBrowser.ReadyState = READYSTATE_COMPLETE
Set HTMLDoc = oBrowser.Document

Application.Wait DateAdd("s", 3, Now)

HTMLDoc.all.Email.Value = "EMAIL"
HTMLDoc.all.Passwd.Value = "PASSWORD"

For Each oHTML_Element In HTMLDoc.getElementsByName("signIn")
If oHTML_Element.Type = "submit" Then oHTML_Element.Click: Exit For
Next

Do
Loop Until oBrowser.ReadyState = READYSTATE_COMPLETE
Application.Wait DateAdd("s", 1, Now)

oBrowser.Navigate "https://docs.google.com/spreadsheet/ccc?key=0Ah4Bis8FTYfCdDJILVdYOG1EZEYtc1N3NF96dHZSYkE&usp=drive_web#gid=0"

Do
Loop Until oBrowser.ReadyState = READYSTATE_COMPLETE

Application.Wait DateAdd("s", 3, Now)

'''Code to click on File menu -> Download As -> Microsoft Excel (.xlsx) goes here'''

oBrowser.Visible = True

End Sub

I used Firefox's "Pick an element from the page" button to see what's going on the File menu button, and the HTML code changes each time when I hover my mouse over File Menu, click on Download As, and Microsoft Excel (.xlsx). There's no input type, submit, or click code.

Thanks for any help.

 

[John_w]

I've found Google pages to be difficult to automate with IE.

The best way to download your Google spreadsheet with VBA is with the Google Drive API - https://developers.google.com/drive/web/manage-downloads - however there is a steep learning curve.

First, you need to activate the Drive API for your VBA program. You can do this in the Google Developers Console https://code.google.com/apis/console/:
1. In APIs & Auth, click APIs and set Drive API to ON.
2. In Credentials, Create New Client ID - Installed application. This gives you a Client ID string and a Client secret string which you use in your <acronym title="visual basic for applications">VBA</acronym> code.

Then write <acronym title="visual basic for applications">VBA</acronym> code to handle the Google OAuth2 authentication using the Client ID and Secret - there is example code here - Google Oauth2 VBA authentication - Excel Liberation, which is based on OAUTH2 and VBA - Excel Liberation. The code described on the former page encrypts the Client ID and Secret in the Windows registry, whereas the latter doesn't. Authentication includes, if necessary, displaying in IE the Google Sign in page and/or the Cancel/Accept page to permit your program to download files from Google Drive.

Then write <acronym title="visual basic for applications">VBA</acronym> code to download the file as described in the first link above - specifically https://developers.google.com/drive/v2/reference/files/get using the fileId (the key parameter value shown in your docs.google.com URL) to get the download URL . You would use XMLhttp in <acronym title="visual basic for applications">VBA</acronym> to make GET and POST requests to the Google URLs. You will also need code to parse the JSON strings that the Drive API sends - see Parsing JSON in Excel VBA - Stack Overflow, or I believe there is similar code on Downloads - Excel Liberation.

As you can see the whole process is very complicated and you may find it quite overwhelming.

 

[XcelMan]

John_w, thank you for replying to my question. Coincidentally, I think I've seen your post on some other thread when I was searching on Google. The process of downloading a Google spreadsheet with VBA using the Google Drive API is complicated for me since I'm quite a beginner in programming, so I skipped what you posted and kept searching on Google for alternative answer. Now, I'm willing to try your method when I have time and really try to figure out the steps necessary to download a Google spreadsheet using VBA.

So far, I've done steps #1 and #2, which are the easiest steps of the whole process. Now, I have to figure out the Google OAuth2 authentication. I think I would like to try the second method of the authentication since it doesn't encrypt the Client ID and Secret in the Windows registry.

I'll try to keep my progress updated in the coming days. I really would like to figure this whole thing out.