So let's deal with 1 first. We need to find an easy way of downloading a google spreadsheet to an XLSX file.
We could click on links, invoke html elements, simulate mouse and/or keyboard events etc ... but surely there's a shortcut.
There is. It's documented somewhere within the Google Drive API but it is also possible to figure it out by watching the HTTP requests when you download to XLSX.
Go to your spreadsheet in your browser. You'll see a URL something like this:
https://docs.google.com/a/mjtnet.com/sp ... 0M3c#gid=0
Note the Key - the bit after Key= and before #. In this case it is:
And in your case you'll see something other than mjtnet.com
Now change the URL to this and watch what happens:
https://docs.google.com/a/mjtnet.com/fe ... 3c&fmcmd=4
Note I have changed the /spreadsheet/ccc? part to /feeds/download/spreadsheets/Export? and I have removed the #gid=0 part and replaced with &fmcmd=4
Essentially you just use the same format as above replacing mjtnet.com with your account name and the key with your spreadsheet key.
Press enter and you'll see this instantly downloads a .XLSX file.
Brilliant, we now have the direct URL to cause the Excel file to download.
Now, if we assume that your browser is already running and logged into Google Drive we won't need to automate logging in and ALL we need to do is have Macro Scheduler open that URL and bingo the Excel file is downloaded.
Note that the file downloaded has the same name as your spreadsheet but with the .xlsx extension.
So, the script below does precisely this using Chrome. I've used Chrome because it doesn't ask for permission when downloading files. It just gets on with it and downloads. If using IE you'd need to either automate clicking Save on the file download box, or install WebRecorder and use WebRecorder's OnDownload option to use it's automatic download manager.
Here's the script that uses Chrome:
- Code: Select all
It sets up some variables to specify where Chrome lives and where the download folder is. It then deletes any files already in the download folder that we want to download so that we can be sure it will be named correctly. We then run chrome, passing our special URL to it. The next part just waits for the file to exist. And finally it opens it in Excel.
Remember the file will be named after the name of your spreadsheet. In my case it was Test. In your case it is something different, so change it. And don't forget to change the URL to YOUR url for YOUR google drive accounts (it won't be mjtnet.com) and spreadsheet key.
You might want to add some code to close the new tab in chrome afterwards. CTRL-W closes the current tab, so you could simply add this before your XLOpen line after the file is downloaded. This avoids you ending up with lots of open tabs.
You can now schedule this macro to happen every few minutes, or make it loop, or whatever.
So that's step 1. For step 2 it would probably make more sense to start a new topic as you now have an Excel file and any further questions are regarding automating Excel. Lots of examples on the site on how to automate Excel.