INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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?

RE: Synchronicity of calculations with external data in Excel 2016?

Hi,

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
    Next 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Synchronicity of calculations with external data in Excel 2016?

(OP)
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.

RE: Synchronicity of calculations with external data in Excel 2016?

have you tried to add DoEvents ?

RE: Synchronicity of calculations with external data in Excel 2016?

Could you upload your workbook?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Synchronicity of calculations with external data in Excel 2016?

(OP)
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:

CODE

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/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?

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

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
    Next 

BTW, my “discovery” came through the Watch Window. Here’s my FAQ...
http://www.tek-tips.com/faqs.cfm?fid=4594

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Synchronicity of calculations with external data in Excel 2016?

(OP)
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.

RE: Synchronicity of calculations with external data in Excel 2016?

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Synchronicity of calculations with external data in Excel 2016?

(OP)
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

RE: Synchronicity of calculations with external data in Excel 2016?

Error on what statement?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Synchronicity of calculations with external data in Excel 2016?

(OP)
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.

RE: Synchronicity of calculations with external data in Excel 2016?

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Synchronicity of calculations with external data in Excel 2016?

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

RE: Synchronicity of calculations with external data in Excel 2016?

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close