NSearch Posted April 14, 2005 Share Posted April 14, 2005 Hello all. Thank you for your help so far. The next problem I have is that I am trying to run an excel macro until finished, and then open the next spreadsheet and run the macro that it, and so on and so on to produce ~6 reports. I am not able to run them all at the same time, because they all access a specific .xls file, and I get a read-only error if they are ran at the same time. I will post the code I have so far, and I know that my while and if statements are not doing me much good. Thanks. Run('C:\Program Files\Microsoft Office\Office\EXCEL.EXE "C:\fedxrem\upseinv analysis.xls"') WinWait("Microsoft Excel - upseinv analysis.xls","") Send("^+i") Send("{ENTER}") Run('C:\Program Files\Microsoft Office\Office\EXCEL.EXE "C:\fedxrem\upseinvsum.xls"') WinWait("Microsoft Excel - upseinvsum.xls","") Send("^+i") Run('C:\Program Files\Microsoft Office\Office\EXCEL.EXE "C:\fedxrem\upseinv misc.xls"') WinWait("Microsoft Excel - upseinv misc.xls","") Send("^+i") Run('C:\Program Files\Microsoft Office\Office\EXCEL.EXE "C:\fedxrem\upseinv purecno summary.xls"') WinWait("Microsoft Excel - upseinv purecno summary.xls","") Send("^+i") Run('C:\Program Files\Microsoft Office\Office\EXCEL.EXE "C:\fedxrem\upseinv state dist shipper.xls"') WinWait("Microsoft Excel - upseinv state dist shipper.xls","") Send("^+i") Send("{ENTER}") WinWait("Microsoft Excel - upseinv state dist shipper.xls","") Send("a") Send("{ENTER}") Run('C:\Program Files\Microsoft Office\Office\EXCEL.EXE "C:\fedxrem\upseinv audit report.xls"') WinWait("Microsoft Excel - upseinv audit report.xls","") Send("^+i") Link to comment Share on other sites More sharing options...
NSearch Posted April 14, 2005 Author Share Posted April 14, 2005 I am trying to the the WinWait Function, but I am not having much luck. The script seems to be working on the first spreadsheet, because the remaining do not try to process, but as soon as the first one finished, all the others start up. Here is what I currently have: Run('C:\Program Files\Microsoft Office\Office\EXCEL.EXE "C:\fedxrem\upseinv analysis.xls"') WinWait("Microsoft Excel - upseinv analysis.xls","") Send("^+i") Send("{ENTER}") WinWait("Microsoft Excel - upseinv analysis.xls") Run('C:\Program Files\Microsoft Office\Office\EXCEL.EXE "C:\fedxrem\upseinvsum.xls"') WinWait("Microsoft Excel - upseinvsum.xls","") Send("^+i") WinWait("Microsoft Excel - upseinvsum.xls") Run('C:\Program Files\Microsoft Office\Office\EXCEL.EXE "C:\fedxrem\upseinv misc.xls"') WinWait("Microsoft Excel - upseinv misc.xls","") Send("^+i") WinWait("Microsoft Excel - upseinv misc.xls") Run('C:\Program Files\Microsoft Office\Office\EXCEL.EXE "C:\fedxrem\upseinv purecno summary.xls"') WinWait("Microsoft Excel - upseinv purecno summary.xls","") Send("^+i") WinWait("Microsoft Excel - upseinv purecno summary.xls") Run('C:\Program Files\Microsoft Office\Office\EXCEL.EXE "C:\fedxrem\upseinv state dist shipper.xls"') WinWait("Microsoft Excel - upseinv state dist shipper.xls","") Send("^+i") Send("{ENTER}") WinWait("Microsoft Excel - upseinv state dist shipper.xls","5") Send("a") Send("{ENTER}") WinWait("Microsoft Excel - upseinv state dist shipper.xls") Run('C:\Program Files\Microsoft Office\Office\EXCEL.EXE "C:\fedxrem\upseinv audit report.xls"') WinWait("Microsoft Excel - upseinv audit report.xls","") Send("^+i") Link to comment Share on other sites More sharing options...
NSearch Posted April 14, 2005 Author Share Posted April 14, 2005 Basically what I am trying to do is to wait until the previous spreadsheet has finished running the macro, and closing, and then run the next one. Does anyone have a good solution to my problem. Thanks. Link to comment Share on other sites More sharing options...
bshoenhair Posted April 14, 2005 Share Posted April 14, 2005 You never answered my question in your other post, can you "Write" macros in Excel ? If you are at least a "Novice Writer" in VBA then I can help you with a more elegant solution. If not your best bet is to time these macros and use the Sleep function of AutoIt to pause between the different macros. I have a suggestion, all the spreadsheats you have listed seem to be somehow related so why have 6 different spreadsheets ? I would have 1 spreadsheet with 6 tabs in it, each tab would contain the uniqueness of each different spreadsheet and all macros would be run from 1 spreadsheet. Link to comment Share on other sites More sharing options...
phillip123adams Posted April 14, 2005 Share Posted April 14, 2005 Assuming the macros also close the spreadsheet, use a WinExists function to wait for Excel to close before opening the next spreadsheet (see below). expandcollapse popupRun('C:\Program Files\Microsoft Office\Office\EXCEL.EXE "C:\fedxrem\upseinv analysis.xls"') WinWait("Microsoft Excel - upseinv analysis.xls", "") Send("^+i") Send("{ENTER}") ;~ WinWait("Microsoft Excel - upseinv analysis.xls") While WinExists("Microsoft Excel - upseinv analysis.xls", "") Sleep(100) WEnd Run('C:\Program Files\Microsoft Office\Office\EXCEL.EXE "C:\fedxrem\upseinvsum.xls"') WinWait("Microsoft Excel - upseinvsum.xls", "") Send("^+i") ;~ WinWait("Microsoft Excel - upseinvsum.xls") While WinExists("Microsoft Excel - upseinvsum.xls", "") Sleep(100) WEnd Run('C:\Program Files\Microsoft Office\Office\EXCEL.EXE "C:\fedxrem\upseinv misc.xls"') WinWait("Microsoft Excel - upseinv misc.xls", "") Send("^+i") ;~ WinWait("Microsoft Excel - upseinv misc.xls") While WinExists("Microsoft Excel - upseinv misc.xls", "") Sleep(100) WEnd Run('C:\Program Files\Microsoft Office\Office\EXCEL.EXE "C:\fedxrem\upseinv purecno summary.xls"') WinWait("Microsoft Excel - upseinv purecno summary.xls", "") Send("^+i") ;~ WinWait("Microsoft Excel - upseinv purecno summary.xls") While WinExists("Microsoft Excel - upseinv purecno summary.xls", "") Sleep(100) WEnd Run('C:\Program Files\Microsoft Office\Office\EXCEL.EXE "C:\fedxrem\upseinv state dist shipper.xls"') WinWait("Microsoft Excel - upseinv state dist shipper.xls", "") Send("^+i") Send("{ENTER}") ;~ WinWait("Microsoft Excel - upseinv state dist shipper.xls","5") Sleep(5000) Send("a") Send("{ENTER}") ;~ WinWait("Microsoft Excel - upseinv state dist shipper.xls") While WinExists("Microsoft Excel - upseinv state dist shipper.xls.xls", "") Sleep(100) WEnd Run('C:\Program Files\Microsoft Office\Office\EXCEL.EXE "C:\fedxrem\upseinv audit report.xls"') WinWait("Microsoft Excel - upseinv audit report.xls", "") Send("^+i") Phillip Link to comment Share on other sites More sharing options...
bshoenhair Posted April 15, 2005 Share Posted April 15, 2005 @phillip123adams Instead of:While WinExists("title" [, "text"]) Sleep(100) WEnd Simpler to just use WinWaitClose ( "title" [, "text" [, timeout]] ) Then you have the option to bail out if needed with the TimeOut option. Link to comment Share on other sites More sharing options...
NSearch Posted April 15, 2005 Author Share Posted April 15, 2005 Thank you very much. Does Sleep(100) pause for a certain period of time. Because I will be running this script on many machines with varying speed on the amount of time it takes to process the spreadsheet. Thanks again! Link to comment Share on other sites More sharing options...
bshoenhair Posted April 15, 2005 Share Posted April 15, 2005 (edited) @NSearch Like I suggested in post #4, why not have 1 spreadsheet with only 1 macro to process ? Sleep(100) will pause the script for 100 milliseconds Edited April 15, 2005 by bshoenhair Link to comment Share on other sites More sharing options...
NSearch Posted April 15, 2005 Author Share Posted April 15, 2005 The macros have already been written. What I would like to do is duplicate what the auditors currently do. They open a spreadsheet named for example (upseinv analysis) Hold down ctrl + shift + i The report is generated (could take as long as 10 minutes) They open a new spreadsheet named (upseinvsum) Hold down ctrl + shift + i The report is generated The thing is that each one has to be run seperately because they each access a certain file, and can not be opened more than once. So, what I would like to do is run each excel spreadsheet just as the auditors would. By first opening the spreadsheets sending ctrl + shift + i to start the macro. Then have the report generate (which closes automatically) Then Trigger the next spreadsheet to open.....etc...etc...etc... The sleep function will not work for this application. Thank you very much for you help. Link to comment Share on other sites More sharing options...
bshoenhair Posted April 15, 2005 Share Posted April 15, 2005 Give me a list of the macro names for each of the different spreadsheets and I will give you a "COM" solution. Example SpreadSheet 2 Path: C:\fedxrem\upseinv analysis.xls MacroName: ??? SpreadSheet 2 Path: ???? MacroName: ??? Etc Link to comment Share on other sites More sharing options...
phillip123adams Posted April 15, 2005 Share Posted April 15, 2005 Thank you very much. Does Sleep(100) pause for a certain period of time. Because I will be running this script on many machines with varying speed on the amount of time it takes to process the spreadsheet.Thanks again!<{POST_SNAPBACK}>@NSearchAs bshoenhair states, the WinWaitClose function can be used and the While loop eliminated and would be simpler.However, to answer your question, 100 pauses the script for 100 milliseconds. Since the Sleep is in a loop, which depends on the existence of a particular window, it does not matter how fast the computer is. Within 1/10 of a second of the window closing, the script will continue. The reason for the Sleep is to reduce the load on the CPU caused by the tight While loop, allowing Excel a large chunk of time to do its chores. The value could actually be any value, but 1/10 of a second is fast enough for me.@bshoenhairThanks! I plumb forgot all about that function.. Phillip Link to comment Share on other sites More sharing options...
bshoenhair Posted April 15, 2005 Share Posted April 15, 2005 (edited) Give me a list of the macro names for each of the different spreadsheets and I will give you a "COM" solution.ExampleSpreadSheet 2Path: C:\fedxrem\upseinv analysis.xlsMacroName: ???SpreadSheet 2Path: ????MacroName: ???Etc<{POST_SNAPBACK}>@NSearchOK, I have tested my idea with "COM" and it works perfect, it will do exactly what you want, all I need is what I've quoted above then I will post my code. Edited April 15, 2005 by bshoenhair Link to comment Share on other sites More sharing options...
phillip123adams Posted April 15, 2005 Share Posted April 15, 2005 @NSearchOK, I have tested my idea with "COM" and it works perfect, it will do exactly what you want, all I need is what I've quoted above then I will post my code.<{POST_SNAPBACK}>bshoenhair, I think what you are asking for is in post 1 and 2. The script lists the filenames of the spreadsheets, and the macro names. For example:WinWait("Microsoft Excel - upseinv analysis.xls","")Send("^+i") Phillip Link to comment Share on other sites More sharing options...
bshoenhair Posted April 15, 2005 Share Posted April 15, 2005 (edited) bshoenhair, I think what you are asking for is in post 1 and 2. The script lists the filenames of the spreadsheets, and the macro names. For example:<{POST_SNAPBACK}>Note:Send("^+i") is used to access the shortcut key set to run the correspondig macro.The Paths are yes, but I need the actual "Names" of the macros that were created in excel which are not stated. In my "COM" example we not use the Send() function, we will access these macros directly through excel.To view the name of the macro go to Menu "Tools - Macro - Macros" then you will see the name of the macro. Edited April 15, 2005 by bshoenhair Link to comment Share on other sites More sharing options...
bshoenhair Posted April 15, 2005 Share Posted April 15, 2005 (edited) OK, for anyone interested on how to run an existing excel macro with "COM" an example is attached that will. Just basic, nothing special. It will open 6 spreadsheets 1 by 1 and run the macro. Note: Each macro will complete before the next one is ran. Edited April 15, 2005 by bshoenhair Link to comment Share on other sites More sharing options...
NSearch Posted April 15, 2005 Author Share Posted April 15, 2005 I am not sure what the best solution will be. Infact, I am in the process of trying each one of the suggestions. So far, the WinExists solution did not work for me. Neither did WinWaitClose. I am going to take a look at the Run Excel Macros.zip file and see if I can relate it to the work that I am doing. I really appreciate your help so far, thanks. Link to comment Share on other sites More sharing options...
NSearch Posted April 15, 2005 Author Share Posted April 15, 2005 This works for me. Does anyone see any drawbacks? Thanks for everyones help. Very appreciated. $PID1 = Run('C:\Program Files\Microsoft Office\Office\EXCEL.EXE "C:\fedxrem\upseinv analysis.xls"') WinWait("Microsoft Excel - upseinv analysis.xls","") Send("^+i") Send("{ENTER}") ProcessWaitClose($PID1) Sleep(1000) $PID2 = Run('C:\Program Files\Microsoft Office\Office\EXCEL.EXE "C:\fedxrem\upseinvsum.xls"') WinWait("Microsoft Excel - upseinvsum.xls","") Send("^+i") ProcessWaitClose($PID2) Sleep(1000) $PID3 = Run('C:\Program Files\Microsoft Office\Office\EXCEL.EXE "C:\fedxrem\upseinv misc.xls"') WinWait("Microsoft Excel - upseinv misc.xls","") Send("^+i") ProcessWaitClose($PID3) Sleep(1000) $PID4 = Run('C:\Program Files\Microsoft Office\Office\EXCEL.EXE "C:\fedxrem\upseinv purecno summary.xls"') WinWait("Microsoft Excel - upseinv purecno summary.xls","") Send("^+i") ProcessWaitClose($PID4) Sleep(1000) $PID5 = Run('C:\Program Files\Microsoft Office\Office\EXCEL.EXE "C:\fedxrem\upseinv state dist shipper.xls"') WinWait("Microsoft Excel - upseinv state dist shipper.xls","") Send("^+i") Send("{ENTER}") ;WinWait("Microsoft Excel - upseinv state dist shipper.xls","5") Send("a") Send("{ENTER}") ProcessWaitClose($PID5) Sleep(1000) Run('C:\Program Files\Microsoft Office\Office\EXCEL.EXE "C:\fedxrem\upseinv audit report.xls"') WinWait("Microsoft Excel - upseinv audit report.xls","") Send("^+i") Link to comment Share on other sites More sharing options...
bshoenhair Posted April 15, 2005 Share Posted April 15, 2005 Like I said "COM" or Excel VBA is the best solution, but after reading your PM I understand your Issues. I only have one suggestion, after each WinWait you should follow with a WinActivate to make sure it is active before using the Send function to lower the risk of sending the keys to the wrong window. Example WinWait("Microsoft Excel - upseinv analysis.xls","") WinActivate("Microsoft Excel - upseinv analysis.xls","") Good Luck 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