EXCEL - Wait for recalc

[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

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

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

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

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.

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

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

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

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!

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