Synchronicity of calculations with external data in Excel 2016?
Synchronicity of calculations with external data in Excel 2016?
(OP)
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?
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?





RE: Synchronicity of calculations with external data in Excel 2016?
Rather than Refresh All...
CODE
' 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 NextSkip,
Just traded in my OLD subtlety...
for a NUance!
RE: Synchronicity of calculations with external data in Excel 2016?
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.
RE: Synchronicity of calculations with external data in Excel 2016?
RE: Synchronicity of calculations with external data in Excel 2016?
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Synchronicity of calculations with external data in Excel 2016?
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:
CODE
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/spreadsheets/d/e/2PACX-1vS...
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.
RE: Synchronicity of calculations with external data in Excel 2016?
CODE
' 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 NextBTW, my “discovery” came through the Watch Window. Here’s my FAQ...
http://www.tek-tips.com/faqs.cfm?fid=4594
Skip,
for a NUance!
RE: Synchronicity of calculations with external data in Excel 2016?
RE: Synchronicity of calculations with external data in Excel 2016?
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Synchronicity of calculations with external data in Excel 2016?
(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
RE: Synchronicity of calculations with external data in Excel 2016?
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Synchronicity of calculations with external data in Excel 2016?
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.
RE: Synchronicity of calculations with external data in Excel 2016?
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Synchronicity of calculations with external data in Excel 2016?
RE: Synchronicity of calculations with external data in Excel 2016?
Skip,
Just traded in my OLD subtlety...
for a NUance!