Auto Transfer data from Google sheets to Excel at a set time interval. Help Please.

[novalion]

Hi everyone, I am new to VBA. I have been trying to get the below code to work, but I am having some trouble.

What the code should do: Pick the data from a Google Sheet and transfer/update it to the Excel Worksheet every 20 seconds.

Problem: The updating part works fine, the delay works fine as well, however when the loop is added it no longer updates and eventually breaks. I get the message "This operation cannot be done because the data is refreshing in the background".

Any help would be much appreciated. Thanks in advance.

Sub ImportGoogleSheetsQuery()

Dim qt As QueryTable, url As String, key As String, gid As String

Dim n As Long
n = 0

Do Until n = 1

    If ActiveSheet.QueryTables.Count > 0 Then ActiveSheet.QueryTables(1).Delete
    ActiveSheet.Cells.Clear

 '***** Shared Google Sheets Link *****
    key = "XXXXXXXXXXXXXXXXXXXXXXX"
    gid = "0"
    url = "https://spreadsheets.google.com/tq?tqx=out:html&key=" & key _
    & "&gid=" & gid

    Set qt = ActiveSheet.QueryTables.Add(Connection:="URL;" & url, _
    Destination:=Range("A1"))

    With qt
    .WebSelectionType = xlAllTables
    .Refresh

End With

Application.Wait DateAdd("s", 20, Now)

Loop

End Sub

 

[novalion]

Any Ideas?

 

[Dan_W]

I'm surprised you're still getting that message after waiting for 20 seconds. What happens if you just try to run it manually? (ie. no loop)

 

[novalion]

I'm surprised you're still getting that message after waiting for 20 seconds. What happens if you just try to run it manually? (ie. no loop)

It works fine. It updates to the column like it should. Normally takes like 2 seconds.

 

[Dan_W]

I checked the MS documentation, and it seems as thought Wait should allow background processes to keep going for the duration, but maybe try the following routine instead:

Public Sub PAUSE(Period As Single)

    Dim t As Single
    t = Timer
    Do
        DoEvents
    Loop Until t + Period < Timer

End Sub

So instead of the Application.Wait line of code, try: Pause 5

That works out to be around a 5 second pause, so you may have to try adjusting it up and down till it gets to the right period.
If that doesn't work, I might have one other idea...