Open file, auto_open, close 1st - Breaks VB.

[rev0102]

OK, so as it stands I have 2 files; 1.xls and 2.xls.

file1 has a macro:

Sub One()
Workbooks.Open Filename:="c:/2.xls"
MsgBox "You'll never see this message "
End Sub

and file2 has this in its ThisWorkbook:

Private Sub Workbook_Open()
Workbooks("1.xls&quot.Close savechanges:=False
MsgBox "You'll never see this message either"
End Sub


As far as I can tell, what's happening is file1's macro will not continue past the open command until file2's macro is finished. Closing File1 while it's macro hasn't finished causes VB to halt and reset the macros. I'm looking for any suggestions at all, I'm totally lost as for a fix. I was thinking perhaps there was a way to force file1's macro to complete before file 2's Workbook_open command runs, but I may be on the right track. All comments/suggestions/fixes (and bumps) appreciated.

Other thread with some clarification:

http://www.devcity.net/forums/topic.asp?tid=33988&#RID100901

 

[VBAjedi]

Haven't tested this, but:

Why not avoid using File2's _open event altogether? That might allow File1's code to complete. Maybe you could use the Workbook_Activate event instead. Test to see if File1 is still open, and close it if so. You might need to put a few seconds of delay in there if the File1 code is still wrapping up.

Just brainstorming here. . .


VBAjedi

 

[rev0102]

By all means, Brainstorm!
It's a good idea. I've tried both workbook_activate and having it just in a standard module in file2 and having file1 either activate file2 or run file2's macro in the module, but both have the same effect: file2's macro runs first before file1's can finish, file2 closes file1 and then all scripts cease.

 

[VBAjedi]

Ok, then, how about avoiding code for this in File2 at all?

Use code in file1 to open file 2 AND close file1. That way the close statement won't execute until the file2.open statement is finished. End result is the same - user sees file2 open, and file1 is closed.


VBAjedi

 

[rev0102]

Right, but the remainder of the code in file2 depends on file1 being closed (file2 moves file1, then saves itself to file1's old location and name).
The crummy hack I just came up with does this:
Right before this command:
Workbooks("1.xls&quot.Close savechanges:=False
I've added:
Application.OnTime Now + TimeValue("00:00:10&quot, "RemainingTwoScript"
where Sub RemainingTwoScript() contains what used to be in file2's macro after the close command.

The close command closes file1 and all scripts break, as usual, but then 10 seconds later file2 begins RemainingTwoScript() and everything works again.
It's ugly, and I need a better solution, but at least it's working at all now.