blackdog Posted August 26, 2010 Share Posted August 26, 2010 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 More sharing options...
seandisanti Posted August 26, 2010 Share Posted August 26, 2010 (edited) 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 August 26, 2010 by cameronsdad Link to comment Share on other sites More sharing options...
leuce Posted August 26, 2010 Share Posted August 26, 2010 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 More sharing options...
blackdog Posted August 27, 2010 Author Share Posted August 27, 2010 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... :-)SamuelYes, 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 0sleep(20)Wendand see what happens. I will post the results.Thanks! Link to comment Share on other sites More sharing options...
blackdog Posted August 27, 2010 Author Share Posted August 27, 2010 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now