With a lot of searching and adding together all kinds of code parts, I've managed to wirte a code that reads a google-doc spreadsheet into access!
The google doc should be in semi-protected sharing - whoever has the link can view the document.
For the example, I've made a form with the command button cmdButton,
and the following code is the 'click' event of it.
And the code is... (Access 2010):
'This line is to enable the 'Sleep' function which I use later.
Private Declare Sub Sleep Lib "kernel32" (ByVal lngMilliSeconds As Long)
Private Sub cmdButton_Click()
'To enable Excel.Application, Excel.Workbook and Excel.Worksheet - you need to enable
'the Excel objects in your Access file: in the VBA application go to 'Tools' menu > References.
'Find the Microsoft Excel 12.0 Object Library, and activate the checkbox.
'Now you have the full Excel library at your service.
'Here I used 'Object' - which is enough to make it work without the excel library.
Dim appXL As Object 'Excel.Application
Dim wbk As Object 'Excel.Workbook
Dim wst As Object 'Excel.Worksheet
Dim Timer As Integer
Set appXL = CreateObject("Excel.Application")
' appXL.Visible = True 'If you want to see the excel sheet - enable this row (good for debugging)
Set wbk = appXL.Workbooks.Add
Set wst = wbk.Worksheets(1)
With wst
'In the following row, after the word 'key=' until the '&gid' - put the code-number of the google-doc spreadsheet, which you extract from the link you get for the spreadsheet google-doc (looks like: 'KeXnteS6n6...')
.QueryTables.Add Connection:= _
"URL;https://spreadsheets.google.com/tq?tqx=out:html&tq=&key=GOOGLEDOC_CODE_NUMBER_HERE&gid=1" _
, Destination:=.Range("$A$1")
.Name = "Worksheet1"
'The following fields are available if enabling Excel library (See above)
' .FieldNames = True
' .RowNumbers = False
' .FillAdjacentFormulas = False
' .PreserveFormatting = True
' .RefreshOnFileOpen = False
' .BackgroundQuery = True
' .RefreshStyle = xlInsertDeleteCells
' .SavePassword = False
' .SaveData = True
' .AdjustColumnWidth = True
' .RefreshPeriod = 0
' .WebSelectionType = xlEntirePage
' .WebFormatting = xlWebFormattingNone
' .WebPreFormattedTextToColumns = True
' .WebConsecutiveDelimitersAsOne = True
' .WebSingleBlockTextImport = False
' .WebDisableDateRecognition = False
' .WebDisableRedirections = False
' .Refresh BackgroundQuery:=False
.QueryTables(1).Refresh
End With
'Wait for google-doc data to be downloaded.
Timer = 0
Do While Left(wst.Cells(1, 1), 12) = "ExternalData" And Timer < 40
Sleep 250 ' Wait 0.25 sec before re-checking data
Timer = Timer + 1
Loop
MsgBox "The value of cell A1 is: " & wst.Cells(1, 1)
'Here you can work with the data...
wbk.Close SaveChanges:=False 'Don't save excel sheet
' wbk.Close SaveChanges:=True, FileName:="GDocs" 'Save excel sheet in 'Documents' folder
appXL.Quit
End Sub