Jump to content

Excel calculation problem


Recommended Posts

Ok so im trying to copy data between two excel sheets, i can do this fine and it works well. However, i need to bring up a messagebox to prompt the user to click when the calculations have finished. So, my question is: How can I monitor excel calculations and report when it has finished calculating? I don't know any VB, thats if macros are the only way to do it, but i would prefer a 100% Autoit option.

Any help would be greatly appreciated!!

Link to comment
Share on other sites

Ok so im trying to copy data between two excel sheets, i can do this fine and it works well. However, i need to bring up a messagebox to prompt the user to click when the calculations have finished. So, my question is: How can I monitor excel calculations and report when it has finished calculating? I don't know any VB, thats if macros are the only way to do it, but i would prefer a 100% Autoit option.

Any help would be greatly appreciated!!

Anything that you can do in excel you can do in autoit. I'm not sure what you mean on waiting for the calculations though... you could just do the calculations in your script before populating the cells so you don't have to wait for anything? Give me a little while i'll try to find a check in the object model to use in the code

***edit***

here you go, example assumes that $oEx is an excel.Application object and if this doesn't work, try using -1 or 1 instead of the zero, but i think it's 0

While $oEx.CalculationState <> 0;in excel vba you'd be checking against xlDone but through COM i believe it's 0
sleep(20)
Wend
Edited by cameronsdad
Link to comment
Share on other sites

Ok so im trying to copy data between two excel sheets, i can do this fine and it works well. However, i need to bring up a messagebox to prompt the user to click when the calculations have finished. So, my question is: How can I monitor excel calculations and report when it has finished calculating?

Let me see if I understand correctly. At this time, you are forced to use the MsgBox because you don't have a way to automatically detect when the calculations are done, but you're prefer not to use the MsgBox method. Right?

As far as I know, if you press F12 (i.e. Save As) in Excel, the dialog won't appear until Excel is finished with what it was busy with. So what you could do is press F12 (i.e. have the script do it) and wait for the Save As dialog to appear. When it appears, cancel it, but then you know that the process that preceded the F12 keystroke has finished.

This idea is entirely theoretical... :-)

Samuel

Link to comment
Share on other sites

Let me see if I understand correctly. At this time, you are forced to use the MsgBox because you don't have a way to automatically detect when the calculations are done, but you're prefer not to use the MsgBox method. Right?

As far as I know, if you press F12 (i.e. Save As) in Excel, the dialog won't appear until Excel is finished with what it was busy with. So what you could do is press F12 (i.e. have the script do it) and wait for the Save As dialog to appear. When it appears, cancel it, but then you know that the process that preceded the F12 keystroke has finished.

This idea is entirely theoretical... :-)

Samuel

Yes, that is what I want to happen. The process is like so: I open one spreadsheet and copy quite a lot of data, I then open another spreadsheet and paste the data in, which updates values in another tab of that new sheet due to the formula that is there. It then calculates those values and because my pc is so slow it takes more than a minute to do this!

I am opening the spreadsheets using the Excel.au3

$sFilePath1 = @ScriptDir &"\example.xls"

$oExcel = _ExcelBookOpen($sFilePath1, 1)

I will try

While $oEx.CalculationState <> 0;in excel vba you'd be checking against xlDone but through COM i believe it's 0

sleep(20)

Wend

and see what happens. I will post the results.

Thanks!

Link to comment
Share on other sites

Ok so

While $oEx.CalculationState <> 0;in excel vba you'd be checking against xlDone but through COM i believe it's 0

sleep(20)

Wend

worked!! except in my case i used $oExcel.CalculationState.

Thank you cameronsdad, your code did the job beautifully ;)

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...