Getting data from a "published" Google Sheet?

[jimrowland]

I have a published google sheet located here: https://docs.google.com/spreadsheet...iWOaXWxnzD39LdApRsA/pubhtml?gid=0&single=true


I would like to import that into my current Excel 2007 work book, into the sheet "Dispatch", and into columns DB:DG, starting at Row 3 and going down as far as necessary. This external data changes semi-frequently (every few days), so I would like to replace the old data in the "Dispatch" sheet with the new data.

I copied and pasted (and tweaked) an example from this site: Extracting a table of data from a website using a VBA query
But nothing seems to happen.

This new Sub runs just fine (completes without errors), and deletes all of the old data, but I'm left with nothing being imported.


Here is the final code I came up with:

Sub GetPlanes()

'=====Clear current Data=====
Worksheets("Dispatch").Range("DA:DG").ClearContents


Dim qt As QueryTable
Dim ws As Worksheet


Set ws = Worksheets("Dispatch")


'set up a table import (the URL; tells Excel that this query comes from a website)
Set qt = ws.QueryTables.Add( _
Connection:="URL;" & "https://docs.google.com/spreadsheets/d/15zrfM0q3UFUsLCwfIbLD2WcGiWOaXWxnzD39LdApRsA/pubhtml?gid=0&single=true", _
Destination:=Range("DB3"))


qt.RefreshOnFileOpen = True




End Sub

I also scrolled through this page (which I found after searching MrExcel for my issue), but it's a bit more advanced than the hand-holding I apparently need.
http://www.mcpher.com/Home/excelquirks/get-data-from-google-docs



Appreciate any thoughts on this.

Jim

 

[jimrowland]

Are we allowed to "bump" questions?