Sign in to follow this  
Followers 0
NSearch

Running a excel macro until finished.....

18 posts in this topic

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")

Share this post


Link to post
Share on other sites



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")

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Assuming the macros also close the spreadsheet, use a WinExists function

to wait for Excel to close before opening the next spreadsheet (see below).

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")
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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

#8 ·  Posted (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 by bshoenhair

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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}>

@NSearch

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

@bshoenhair

Thanks! I plumb forgot all about that function.

.


Phillip

Share this post


Link to post
Share on other sites

#12 ·  Posted (edited)

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

<{POST_SNAPBACK}>

@NSearch

OK, 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 by bshoenhair

Share this post


Link to post
Share on other sites

@NSearch

OK, 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

Share this post


Link to post
Share on other sites

#14 ·  Posted (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 by bshoenhair

Share this post


Link to post
Share on other sites

#15 ·  Posted (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 by bshoenhair

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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")

Share this post


Link to post
Share on other sites

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

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  
Followers 0