Excel VBA to download Excel Google Doc

[Williamtj21]

Hello guys,

I would like to have a code that can open my excel google doc, click file, choose download as, choose microsoft excel, click open. This process is identical for all Excel Google Doc, so I don't think I need to give my google doc website addresses.

Can someone help me please? I have been trying to look for a solution, but have not been successful. I would really really really appreciate the help.

Thank you very very much for your time.

 

[John_w]

Welcome to MrExcel forums.

Your request can be done with the Google Drive API - https://developers.google.com/drive/web/manage-downloads.

First, you need to activate the Drive API for your app. 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 VBA code.

Then write VBA 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.

Then write VBA code to download the file as described in the first link above. You would use XMLhttp in VBA 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.

A possible alternative is to write VBA code which automates your Google Drive web page with Internet Explorer and handles the IE download windows using the Windows API (FindWindow, etc.).

 

[Williamtj21]

Hello John,

Thank you very much for your reply. Can you show me the second alternative?

"write VBA code which automates your Google Drive web page with Internet Explorer and handles the IE download windows using the Windows API (FindWindow, etc.)"

I would really appreciate the help.

 

[John_w]

Search this forum for all or a combination of these words:"Google InternetExplorer automate control website click button HTMLdocument". There should be many matching threads.

Code for handling the IE download window can be found in Download web table in multiple pages and Need help regarding IE Automation using VBA.