Resuming Code

[greg303]

I have code that activates another workbook to close it, but I want the next line of code to excute after the other workbook has close.
I have used workbooks("book1.xls&quot.Activate to active the other sheet then used thisworkbook.close under the Workbook_Activate() sub. Once that book has closed though the code in my first workbook has paused and I want the next lines to excute.

Any Ideas?

Thanks in Advance

 

[PHV]

Why not use this ?
workbooks("book1.xls&quot.Close

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[VBAjedi]

You should not need to activate the other workbook to close it. Create a workbook object, then assign the workbook in question to it:

Dim Wb As Workbook
Set Wb = Workbooks("book1.xls")
Wb.Close

Let me know if that does the trick for you. . .

VBAjedi

 

[TonyJollans]

Hi greg303,

I have just duplicated your problem and it is odd, but it seems to me even odder to have code in the Activate Event which closes the Workbook. Whenever you open the workbook, it will close itself, so how are you in a position to Activate it? What are you trying to do?

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[greg303]

I tried the workbook("book1.xls&quot.close but the same thing kept happing, the next line dose not run.

I tried the activate so that when the other workbook closed and focus returned to the first sheet then I could use the activate sub to contiune my code.

I just thought there must be a way to force a sheet to close then continue with the next line.

 

[greg303]

When I run the code line by line when it closes the workbook the excution stops, if I were to hit F8 another time it will restart the code for the begining which I dont want it to do. When the code excutes on its own this is were it stops. There must be a command to excute the close then return to the last line of code.

 

[VBAjedi]

Trying to close a workbook using code stored within that workbook can be very touchy - a number of threads in this forum discuss this.

Much easier to use code like PHV or I suggested (stored in a DIFFERENT workbook) to close the workbook. Just put the code inline in the module you are wanting to resume after the other workbook is closed.

VBAjedi

 

[greg303]

VBAJedi,

I had the code of workbooks("book1&quot.close inline before and tried the code you suggested and all three times the same thing happened, after the workbook closed it stopped the excution on the first workbooks sub. I think what is happening is that book1 takes focus away from book2 to close but this pauses the code from book2. I am not sure how to resume excution once the focus is back on book2.

 

[VBAjedi]

My (limited) tests did not reproduce this problem. Do you have code in the other sheet's Workbook_BeforeClose event or some other event triggered by the close?

VBAjedi

 

[greg303]

What I am trying to do is this, have a workbook open on startup, book1.xls. This file will use the date and a file name to create a new file but first it checks if that file already exsists. For example:

NewWkbName = "book_" & Format(Now, ["DD,MM,YY"]) & ".xls"
If Dir("C:\test\" & NewWkbName) <> "" Then
Workbooks.Open_ filename:="C:\test\" & NewWkbName
Workbooks("book1.xls&quot.Close SaveChanges:=False
Else
ThisWorkbook.SaveAs ("C:\test\" & NewWkbName)
End If


The problem is that when the Workbook.Open line executes the new workbook opens and dose its thing. The old workbook has to be closed for everything to work right. Once the other workbook opens it has code to excute aswell but because if I were to write in a workbooks("book1.xls&quot.close in it then that would stop code from running. There has got to be an easy fix for this.

 

[greg303]

I tried your other suggestion with the Workbook_BeforeClose event and put in my Workbooks.open line and that wont even excute, any other code I put in will run but it just passes over the workbooks.open. Even if I had a call to another sub that used workbooks.open it will still not excute. Why???

 

[VBAjedi]

Ok. Knowing the actual application helps immensely. It's going to take a smarter person than I to come up with an EASY solution, but the following shouldn't be too hard and ought to work:

I'm thinking that using book1 (your default new spreadsheet template) as the template for this other file might be a bad idea (especially if it needs code in the workbook_open event) because all new workbooks you create will have that code embedded.

Instead, you could create an .xlt template file that contains the desired code and any other content/formatting you would like. IMPORTANT: put the code in a normal module, and call that sub from the workbook_open event using Application.Ontime = now + two seconds.

Leave the code you posted in your last thread in book1, but adjust it to use your new template when creating a new workbook.

Now, when you open Excel, a new book1 opens. The code in book1 checks for the existence of your file, then opens it if it exists or creates it from your template if it doesn't yet exist. In either case, the open event of that second workbook fires your sub after a two-second delay. By the time the sub does fire, book1 is finished running its little code snippet, so your sub can close book1 (using my code snippet) without error.



VBAjedi