Jump to content
Sign in to follow this  
mtrevino57

EXCEL - Wait for recalc

Recommended Posts

mtrevino57

I have a spreadsheet that retrieves data from the Web. I am using the following to load the spreadsheet

#include <Excel.au3>

Global $filename = "AutoIT_Test.xls"

Global $filepath = "AutoIT_Test.xls"

$oExcel = _ExcelBookAttach($filename, "FileName")

If $oExcel = 0 Then

$oExcel = _ExcelBookOpen($filepath, 1)

EndIf

$oExcel.Application.Run("PrintToPDF_Early")

This works great, the worksheet loads and the output is printed to a PDF file. I want to make this procedure

wait until the web queries have completed, i.e. about 20 seconds in most cases, before I call the print subroutine.

Tried the Sleep(20000) command immediately after the EndIf command but that isn't quite right as that suspends

the spreadsheet from doing anything for 20 seconds.

Any suggestions?

Thanks

Mark

Share this post


Link to post
Share on other sites
PsaltyDS

What "web queries"? How are those accomplished?

How does Sleep() prevent Excel from doing anything, as it only pauses your script, not the Excel app? If you mean it pauses your script in the wrong place, where do you mean to pause it? Move the Sleep() there in your script?

:blink:


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
cageman

I think the point is that he wants a more accurate way then just using sleep(20000) since it on some computers can be to short.. One idea is to have on excel formula that checks some things and gives a 1 if the data is there and a 0 if it isn't there. Just keep checking that until its there.

Share this post


Link to post
Share on other sites
mtrevino57

What "web queries"? How are those accomplished?

How does Sleep() prevent Excel from doing anything, as it only pauses your script, not the Excel app? If you mean it pauses your script in the wrong place, where do you mean to pause it? Move the Sleep() there in your script?

:blink:

The web queries are a capability inside Excel, that allows me to pull data from a web page, write it to a spreadsheet, i.e. stock quotes, or in my case reservoir storage values. When I load the page manually, these web queries are initiated and it retrieves the data from the website, since this data is updated hourly, and once I have that data I prepare some charts.

The Sleep command pauses the whole process, so consequently, the queries do not begin to update until the sleep command it finished, or so it appears. What I need is a way for Excel to begin retrieving the data while and preferably once all data is updated, then execute the macro that creates the PDF output file. I suppose I could check the status of a particular cell to know when all updates have been completed, but I was hoping for a cleaner way, ie a command that does not suspend Excel but waits while Excel updates the spreadsheet.

Share this post


Link to post
Share on other sites
mtrevino57

The web queries are a capability inside Excel, that allows me to pull data from a web page, write it to a spreadsheet, i.e. stock quotes, or in my case reservoir storage values. When I load the page manually, these web queries are initiated and it retrieves the data from the website, since this data is updated hourly, and once I have that data I prepare some charts.

The Sleep command pauses the whole process, so consequently, the queries do not begin to update until the sleep command it finished, or so it appears. What I need is a way for Excel to begin retrieving the data while and preferably once all data is updated, then execute the macro that creates the PDF output file. I suppose I could check the status of a particular cell to know when all updates have been completed, but I was hoping for a cleaner way, ie a command that does not suspend Excel but waits while Excel updates the spreadsheet.

And I see what you are saying as well. Sleep is outside so it should NOT be impacting Excel recalc, BUT, when I load the sheet manually, I see the small globe icon in the lower left hand corner that lets me know the queries are updating. When I load the sheet, using AutoIT, the worksheet loads but no update occurs, i.e. no globe, no data refresh, and at the end of the time set by the Sleep command, the file is printed out to PDF file....

Share this post


Link to post
Share on other sites
PsaltyDS

So you're expecting an Excel macro to initiate the web query? Are you sure it runs at all when the file is opened by AutoIt, Sleep() or not?

More likely you have to specifically allow and\or .Run() the macro when opening the file.

:blink:


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
mtrevino57

So you're expecting an Excel macro to initiate the web query? Are you sure it runs at all when the file is opened by AutoIt, Sleep() or not?

More likely you have to specifically allow and\or .Run() the macro when opening the file.

:blink:

Well up til now, the web queries update whenever I load the spreadsheet, as I have that turned on in Excel, BUT your comment about causing the web queries update to be initiated by the macro? I LIKE IT! I already have the macro to print the PDF and adding the VBA to update all web queries before printing should be straight forward, emphasis on SHOULD ;-)

Thanks!

Share this post


Link to post
Share on other sites
cageman

Yup that should be the solution. i once has this same problem. I think its backgroundupdate=false in the vba macro, but google it to be safe.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×