Can you link a google spreadsheet to an Access database? (1 Viewer)

[Big Pat]

Hi,

Some colleagues have started using a shared Google spreadsheet on Google Drive, which is working quite well for them. It's basically a data store, with just a few calculated columns.

Is there a way to link that sheet to an Access database? I know I could download it as an Excel or CSV file and IMPORT a snapshot, but can it be linked "live"?

Thanks,

Pat.

 

[Mile-O]

I just typed 'link google spreadsheet to microsoft access' into Google. This is the first result.

https://www.rssbus.com/odbc/google/

 

[Big Pat]

Thanks for that - I must have been googling the wrong words!

Most of this seems way above my level, I have to admit, but when I get home I might try downloading the beta and having a play.

Then I'll still need to convince our IT dept. I was hoping it would be easier, but nothing worthwhile ever is I suppose!

 

[spikepl]

Inspired by this thread, I posted a related question in the Web-forum: http://www.access-programmers.co.uk/forums/showthread.php?p=1331800#post1331800

 

[david howard]

Here is my solution.

My situation, I run a sporting comp
- I want potential players to register using google forms.
- Have the form saved in a googlesheet
- Have the information from that sheet available to Access
- Having manipulated information publish the draw etc to the web.
I have seen a solution where the info from the form is published, I have one where it is private.

Solution
(1)Set up Google drive so documents had a path in my local computer.
(2)Created the form in Google docs
(3)The form can be placed in my google site.
(4)The Gsheet with form responses is visible on my C drive, when I click on it my browser opens with
"https://docs.google.com/spreadsheets/dblah blah blah #gid=1112223333"
(5)Opened a new excel sheet clicked on [data] [from the web] and used the above https:
The result was a spreadsheet with a bit of guff around it but with the data I wanted
Note:the Gsheet isn't a public document so only I can do this

(6)Set up the excel spreadsheet as a linked table in my Access database
(7) I needed an "event" to refresh the spreadsheet. You might want it to refresh on open but I have a button with the following
Private Sub Commandxx_Click()
Dim appexcel As Object
Set appexcel = CreateObject("Excel.Application")
appexcel.workbooks.Open "C:\Users\base\Google Drive\blahblah\mystuff.xlsx"
appexcel.activeworkbook.refreshall
Set appexcel = Nothing
End Sub
(8)I had to filter a bit because some guff came with it but I just used the timestamp field, ie if it doesn't have a timestamp then it isn't something I am interested in.

(9)I haven't done the last bit yet but I'll have the draw come out as a html file saved to my googledrive and then embedded in my googlesite.
Hope this helps some people

 

[topdesk123]

Hello,

I can get to step 6 ok, but my spreadsheet is still considered a new web query not an actual spreadsheet that I could link to. What should I do?

I thank you in advance.
Gina

 

[david howard]

I'm not sure of the problem you describe so I'll mention 2 things. (1) It might be as simple as making XLsheet2 the location linked to gsheet and then XLsheet1 cells equal to XLsheet2.

I'll give more detailed sub-steps.
in XLsheet go to Data-From Web and a little window opens up like a browser.
The window opens on google (my default).
I change the address from google to blah blah
Press go
Press import
I am prompted to put it in A1

I can close the XLsheet
Make a change to the Gsheet
Open the XLsheet
Go to Data
Connections - refresh
Note: there are various options to trigger that refresh.

 

[topdesk123]

Oh, I didn't realize you were importing it so it's no longer live data. I'm looking for a way to link to google spreadsheets to that the data is always up to date.

We have a web form that people complete to receive a free quote. In order to tap into the results, I used google integration in hopes that I could integrate it further into access.

thank you for your response!
Gina

 

[david howard]

Hi
The "event" of opening Access (or some other event like a button) is set to cause excel to refresh so whenever you open Access your data is up-to-date automatically but I don't know a way of having Access running in the background and knowing that someone has completed the form and so respond accordingly.

Good Luck

 

[zmach]

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

 

[Gasman]

Really handy. I've been downloading up until now and always being a little out of date.

Does this bring down all the worksheets in the Google doc, or just the first sheet?

TIA

 

[zmach]

I don't know how to read the second sheet. I'll be happy to hear ideas...

A not-so-cleaver workaround would be to create a new spreadsheet that includes all the spreadsheets you have, and read it.
For example, if you know that your spreadsheets has 10 rows and 5 columns, fill the appropriate cells in the new spreadsheet with the formula =Sheet1:A1 and drag this to the other cells. Then, go to row 11 and put the formula =Sheet2!A1 and drag it to the appropriate cells, etc. At the end, put this new spreadsheet as the first spreadsheet, and then read it...
(You can also make a new workbook and use formula importRange(...))

Already said - not so cleaver.

And if we're already talking, I'm now looking for a way to edit the spreadsheet from Access. Or being more precise - deleting rows from the spreadsheet, so I will not re-read data the I've already imported...
BTW, clearing the whole spreadsheet is dangerous for me, cause I'm using a googleform spreadsheet, and I wouldn't want to delete by mistake a new data that is entered between my import and deletion... Deleting row by row after importing is secure.
Any ideas?!?

 

[Gasman]

I suppose I could create my own google workbook that imports the data from the workbook and sheet that I need to look at. Good idea

My VBA in Excel is pretty much just automating any process I would do manually.
I would have a column perhaps called Processed and set this to "Yes" or the date processed and filter those rows out.?
I am doing something similar where I need to create a new sheet and so I filter out the rows that have already been processed and then add the date to those rows I need to work on and then run vba to process the sheet and create all the new sheets and pdf files. I hope to convert it to Access in the future, but for now have to stick with Excel as that is how it has been done for ages.
If you have to delete, the range has an EntireRow.Delete method and I would walk the rows using ActiveCell.Offset(1, 0).Select

I have a routine that I use now and again

Sub Move_Cursor(Direction As String, Optional varMoves)
Dim iMoves As Integer, iLoop As Integer
    If IsMissing(varMoves) Then
        iMoves = 1
    Else
        iMoves = varMoves
    End If
    For iLoop = 1 To iMoves
        Select Case Direction
            Case "Down"
                ActiveCell.Offset(1, 0).Select
            Case "Up"
                ActiveCell.Offset(-1, 0).Select
            Case "Right"
                ActiveCell.Offset(0, 1).Select
            Case "Left"
                ActiveCell.Offset(0, -1).Select
        End Select
    Next
End Sub

 

[zmach]

Thanx,
If I understad correctly, you gave an answer for deleting rows in the local excel worksheet. If so, I wasn't clear enough - I'm talking about editing the google docs spreadsheet itself.
The question is wether I can do one of the followings:
1. Send an http command that will delete rows there.
2. Or - Link excel/access to the google docs spreadsheet in such a way that will enable me to edit the google doc spreadsheet.
Or maybe in some other way...?

 

[Gasman]

Yes, sorry my answer was for the Excel workbook, I misunderstood.

I would not have a clue as how to do it in Google docs. They have their own version of script to do what VBA does for excel. You would be better off asking in their forums?
I would still be looking at a column that holds the status of whether the data had been processed or not, and then the deletion issue would not arise, but I suppose it would depend on how much data is in the sheet?

Good luck with it.

 

[zmach]

found a way to get to the sheet you want!
In my code, replaces the line starts with: .QueryTables.Add Connection:=....
with the following:

.QueryTables.Add Connection:= _
"URL;https://docs.google.com/spreadsheets/d/GOOLEDOC_CODE_NUMBER_HERE/gviz/tq?tqx=out:html&tq&gid=GOOGLE_ID_OF_SHEET" _
, Destination:=.Range("$A$1")

you can see the GOOGLE_ID_OF_SHEET when you open the spreadsheet in the internet browser.
Yipiii!

 

[scroogie]

........
'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...

........
End Sub

very old post to renew but I'm sure some of you are still tuned in: I found it very useful but I don't know how to proceed to populate an Access table after opening the temporary excel. I think the proper command is:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, xxxx
but I'm not sure which argument to use as data source.
Any help?
thanks in advance

 

[moke123]

You should probably post to a new thread as no one wants to read through 2 pages of a 5 year old post to get to your question.

 

[Bastard]

You should probably post to a new thread as no one wants to read through 2 pages of a 5 year old post to get to your question.

I disagree 100%, if the post is relevant to the thread regardless of age, please feel free to post... people need to get over the "reviving old threads". Nobody wants to spend time piecing together a puzzle from 50 threads when they can get it all the info in one spot.

Back on topic, thanks for the examples everyone, I will see if any of this works for me.

 

[Uncle Gizmo]

I disagree 100%, if the post is relevant to the thread regardless of age, please feel free to post... people need to get over the "reviving old threads". Nobody wants to spend time piecing together a puzzle from 50 threads when they can get it all the info in one spot.



Back on topic, thanks for the examples everyone, I will see if any of this works for me.

I think a new thread should be started in each case.

Why?

Well, the problem is, people don't know what they want. They look through the old posts and think that looks like my problem. I will highlight this thread and bring it to the fore, I don't have to do any work, all the works been done for me because someone else has typed up the question.

The reason you should post your own question is, in constructing the question you will think about the problem... It's very likely, and I know this from my own experience, it's likely that until you actually formulate the question, you haven't really given the issue any thought.

Composing the question forces you to think.





Sent from Newbury UK