Connecting to Google Sheets (1 Viewer)

rpeare

Registered User.
Local time
Today, 16:09
Joined
Sep 15, 2016
Messages
18
I am trying to connect to a google sheet document. My intention is to copy the information to a spreadsheet, then import it into MS Access, but I want all the code to be done from a MS Access user interface. I found the thread listed at the bottom of this post and attempted to replicate it. Alternately if someone has code that will import information directly from google sheets into a MS Access database that would be great. The original poster only posted on one thread 3 years ago so I fear I may be stuck but it's worth a shot!

My google doc is:

https://docs.google.com/spreadsheets/d/1t91SUyUOp7MwJXQKeyooI2cbok2VOEh7XnuuIxVJoDc/edit#gid=0

I thought the code was referencing the part highlighted in bold red but that does not work. I get the error:

GoogleSheetError_A.jpg

I tried substituting in the entire URL i.e.

Code:
Dim appXL As Object
Dim wbk As Object
Dim wst As Object
Dim sConn

Set appXL = CreateObject("Excel.application")
Set wbk = appXL.Workbooks.Add
Set wst = wbk.Worksheets(1)

With wst
    sConn = "https://docs.google.com/spreadsheets/d/1t91SUyUOp7MwJXQKeyooI2cbok2VOEh7XnuuIxVJoDc/edit#gid=0"
    .QueryTables.Add Connection:="URL;" & sConn, Destination:=.Range("$A$1")
    .QueryTables(1).Refresh
End With

'Wait for google-doc data to be downloaded.
Dim Timer
Timer = 0
Do While Left(wst.Cells(1, 1), 12) = "ExternalData" And Timer < 40
    Debug.Print "wait..."
    Sleep 250 ' Wait 0.25 sec before re-checking data
    Timer = Timer + 1
Loop

wbk.Close savechanges:=True, FileName:="F:\AccessForum\GOOGLE SHEET CONNECTOR\OMG.xlsx"

However I get the error

GoogleSheetError.jpg

I do not know if the original method is still valid or whether I'm doing something wrong with the code. If anyone has information on this I would appreciate your input.

----------------------- Original Thread -----------------------------

I am trying to follow the code contained in this thread:

="https://access-programmers.co.uk/...s.co.uk/forums/showthread.php?t=260840&page=2

specifically this code:

Code:
'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
 

CJ_London

Super Moderator
Staff member
Local time
Today, 23:09
Joined
Feb 19, 2013
Messages
16,553
you might find this link helpful - it would appear to include a working example

http://ramblings.mcpher.com/Home/excelquirks/exceldocsintegration/excelsheetsv4

this one seems to provide the required connectors - including access
https://www.dataeverywhere.com/use-database-sheets

and another here
https://forums.digitalpoint.com/thr...trieve-and-update-google-spreadsheet.1541146/

it would appear that things do change over time so what worked before, won't work now - perhaps because now you have to pay for it so need a subscription.

I would try to get it working in excel first, then modify as required
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:09
Joined
Sep 21, 2011
Messages
14,048
If you try it manually, Google insists you sign in, even if you have the sheet open in a web page.?
 

Users who are viewing this thread

Top Bottom