Pausing or waiting on busy

[elgringo56]

I'm stumped. I am running under macro control and have three workbooks open. I do a save on the top workbook, then a close on it, then a close without saving on the second workbook as follows.

ActiveWorkbook.Save
ActiveWindow.Close
ActiveWindow.Close SaveChanges:=False

The problem is that because the system is busy saving the frist work book, when I do the ActiveWindow.Close SaveChanges:=False for the second workbook, I get a Excel has caused an error and needs to shut down. I would like to have a way to wait between the two ActiveWindow.Close statements until the the system is no longer busy with the save. Is this possible. I am using Office 2000 with windows XP.

[elgringo56]

I am trying to think of another way to ask this question. Ok, I will try this. Is there a way to test if the system is busy, perhaps with a Do/Loop or or maybe with an if else. Been browsing help for maybe an hour and have seen nothing that even comes close.

[elgringo56]

I am adding to this to bring it back to the front in hopes that some one thru the day (while I am at work) will be able to offer a solution to this problem, which is driving me nutso. Thanks in advance for any help.

[PDuPre']

On 2002-05-29 12:55, elgringo56 wrote: [Modified]
I do a save on the top workbook, then a close on it, then a close without saving on the second workbook as follows.

ActiveWorkbook.Save
ActiveWindow.Close
ActiveWindow.Close SaveChanges:=False

I would like to have a way to wait between the two ActiveWindow.Close statements.

This seems a bit backward, but may work for now.
Highlight the appropriate workbook and then perform a:
SendKeys "%fs", True
This will tell it to open the File menu and do the save option (On US English version at least). The "True" portion then tells it to wait until control is returned to Excel before continuing with the macro.

Philip

[elgringo56]

Philip

Thank you for responding. I am a newby in this programming stuff, so forgive me if I sound a tad ignorant here. you say "highlight the approaite workbook" How do I do that from within the macro? and, where do i insert the line you gave me. It sounds like what I need to maybe stop the error but not sure how to use it? Thanks again

Larry

[PDuPre']

Sorry. In meetings nad now I have to run. I can answer tomorrow or perhaps someone else can answer for me before then.

Thanks!

Philip

[elgringo56]

Ok, but if anyone else can help, just jump right in. I am open to anything on this one at this point

[PDuPre']

On 2002-05-29 12:55, elgringo56 wrote: [Modified]
I do a save on the top workbook, then a close on it, then a close without saving on the second workbook as follows.

ActiveWorkbook.Save
ActiveWindow.Close
ActiveWindow.Close SaveChanges:=False

I would like to have a way to wait between the two ActiveWindow.Close statements.

Here you have the [ActiveWorkbook.Save] line running in a macro. Simply replace this line with the line [SendKeys "%fs", True].
Not sure how you are kicking off the macro, but the command line SendKeys will effect the active workbook. So, they will accomplish the same thing.
If you were going to always have a specific workbook name then you could select that specific name and make it the active workbook by using a line such as [Windows("Book1").Activate]. This line will activate the Excel workbook called Book1, or Book1.xls.
Now if you don't want to assign a specific name then you start the macro on the active workbook. Then, you can move between open workbooks with the command [ActiveWindow.ActivateNext] or [ActiveWindow.ActivatePrevious]. You are essentially accomplishing the same thing by closing the first file and thus, the active sheet now changes to the next file.

I hope that answered your question. Should have given you some other commands you can play around with too. Would put them in the place of the commands you are currently using, etc. See how they work. If you want to record a macro and have it automatically create these relational links then use the keyboard hotkeys of CTRL-Tab to go to the next workbook and CTRL-SHIFT-Tab to go to the previuos workbook. You can also hit CTRL-Page Up and CTRL-Page Down to move between worksheets on the same workbook. When using these keystrokes the record macro will create relative links. If you want to create a specific link when recording a macro then specifically click on each tab name and specifically click on window and select the file you want to move to.
I know this is a lot of information, I hope it is helpful.

Philip

[elgringo56]

Thanks, Philip. I will play with these when I get off work tonight and see what I can come up with.

[elgringo56]

After playing around with this, I find that I have totaly mis read what is happining here. The problem is not with the save part of the sequence, but with closeing two workbooks back to back. If I only close one, then close the other with a command button, all works ok, no error. Its only when two are closed back to back that the error comes. I tried seperating them with a wait, but waits suspend all excel activity. I tried to seperate them with a MsgBox, that to appears to suspend all excel activity. However, if I build a Group autoshape with a command button on it, which appears the same as a MsgBox, by the time I get my moust to the command button, all excel activity has stopped and I can then execute the second CloseWindow without an error. Thanks a lot for you kind assistance, it was playing with your suggestion that led me to the root of the problem. This was a tough one for me, as most of them are. Again, thanks heaps for your time, hope I can return the favor some day.

Larry