Synchronicity of calculations with external data in Excel 2016? 3

[TomBarsh]

I'm using the Excel feature to access external data from the web. The data source is a Google Sheet that has been published.

I have a simple routine to refresh the data ("ThisWorkbook.RefreshAll"). But subsequent commands seem to execute before the external data has been fully refreshed. Specifically, some worksheet calculations derive from the data and then a MsgBox is put up with the result. But the result is not up to date because the external data is still refreshing.

I used to run into something like this 20+ years ago, where I called Fortran programs from within Excel VBA, and there were sometimes problems with the VBA not pausing until the external routines completed. Well, similar effect but the conditions are likely way different. But I would think that as this is all native Excel functionality that there would not be an issue.

Help?

 

[SkipVought]

Hi,

Rather than Refresh All...

'
    Dim ws As Worksheet, lo As ListObject
    
    For Each ws In ThisWorkbook.Worksheets
        For Each lo In ws.ListObjects
            On Error Resume Next
            lo.QueryTable.Refresh False
            Err.Clear
        Next
    Next

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[TomBarsh]

Thanks for the tip, Skip.

I couldn't get that to work successfully. I have set aside that project for the moment. Still using the external data function to draw down data from some Google Sheets (which for me is a very useful feature). Just have to work around that lil' hiccup for the moment.

 

[JBlack68]

have you tried to add DoEvents ?

 

[SkipVought]

Could you upload your workbook?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[TomBarsh]

Skip, not possible at this time. But I can sketch it out if you (or anyone else) are interested for your own sake; this is a interesting method to get stuff from the internet.

1 - create a Google Sheet (spreadsheet). Of course, this might be an entire project itself if one isn't familiar with these, but they are simple.

I have simplified my project for research by making a simple (Google) spreadsheet with about 6 cells in one column for data, enumerating colors: Red, Blue, Green, Brown, Pink, Purple. Etc. I then import these into Excel with the following steps.

2 - "Publish" the Google Sheet to the web. This is so easy, I'm not even trying to understand how it does it. In the Google Sheet, select File menu | "Publish to the web...". Dialog pops up, select "Sheet1", click the blue "Publish" button. They give you a long url, that will be used in Excel in the Get External Data (from Web) operation.

3 - in new (or existing) Excel workbook, select the Data tab, click the From Web tool from the Get External Data sub-tab.

4- "New Web Query" dialog pops up. Paste in the copied url from the Published Google Sheet. Click "Go" (then you may get a pop up message with some error, safe to ignore that). Excel asks you where you want to put this in your worksheet; tell it where and click "Import". You're done.

Excel has settings to update the import as needed and Google has options to re-Publish when any changes are made to the data.

So a simple Excel macro can be made to update the imported data:

Sub Update_My_Data() 
   ActiveWorkbook.RefreshAll
   MsgBox Worksheets(1).Cells(1, 1)   'where this cell happens to be the top of the imported list
End Sub

The thing that happens is the web query is called to update but the MsgBox pops up before that query completes the update. So the data in the targeted cell is not fresh. Say that cell A1 is empty (and so are all others) when this macro is run. The MsgBox shows 'nothing' and then the web query runs and fills the region with the data.

ETA: actually, since Google "publishes this to the web" anyone who has the url can access the document. So I can share the url for my sample Google Sheet for the data. url is

https://docs.google.com/spreadsheet...sI5mIl7ncZS0ax9S8oE/pubhtml?gid=0&single=true

So that saves 2 of the 4 steps (I think).

Of course, all standard precautions should be followed when accessing any strange, foreign, unknown url on the internet.

 

[SkipVought]

Tom, thanks for the description of publishing a Google doc and linking. I tried it and “discovered” the solution, which I added to the code below...

'
    Dim ws As Worksheet, lo As ListObject, qt As QueryTable
    
    For Each ws In ThisWorkbook.Worksheets
        For Each lo In ws.ListObjects
            On Error Resume Next
            lo.QueryTable.Refresh False
            Err.Clear
        Next
        For Each qt In ws.QueryTables
            qt.Refresh False
        Next
    Next

BTW, my “discovery” came through the Watch Window. Here’s my FAQ...

http://www.tek-tips.com/faqs.cfm?fid=4594

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[TomBarsh]

Skip, thank you for your investigations and your FAQ. I am still behind the 8 ball on some other projects and don't have time to work on this but I will come back to it soon. I appreciate your help.

 

[SkipVought]

I’ll take the cue, hang up my cue and wait in the queue.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[TomBarsh]

Skip, I did a sigh and I gave it a quick try, but I did cry, when at last it wouldn't make me pie.


(instead, giving me Runtime error '1004' "Application or object defined error"

So, there is much to be learned on my part before I can finish this job. But I'm happy with what I have, it's 98% effective. well, it's 100% effective but with two steps.)

Thank you again

 

[SkipVought]

Error on what statement?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[TomBarsh]

well, very surprisingly, the code now runs fully, correctly, and without any error messages. I don't understand this as I made no changes to the code since earlier when I did get an error (which, as I recall, was on "qt.Refresh False")

I'll have to work with this some more to (understand it and) get it into my actual project. Thank you!




and a reminder to self, yes, it's nice to paste those screenprints of error messages into your Excel worksheet for reference, but sometimes they can be distracting.

 

[SkipVought]

The FALSE in the QueryTable Refresh, delays execution of the next statement, until the refresh completes. It is actually the Background Refresh paramater that is FALSE.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[TomBarsh]

Gotta say, Skip. This is really cool and very useful to me. Thank You.

 

[SkipVought]

And a “Thank you, Tom!” for the Google docs link tip. It’s a definite maybe that I’ll use it, but its a new tool in my toolbox. I am doing more lately with Google Drive transfers between my iPad and my Windows laptop.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]