Open a File and Run AutoOpen Macro

[adambush4242]

I'm trying to use workbook.open to open another file and then I want this
other file to automatically run a macro. This other macro starts by closing
out the original file and then running a bunch of other code. I tried using
a Workbook_Open sub but as soon as the code closes the original file it just
stops running. I've tried everything I can think of but I cannot come up
with a solution. I need to open a specified file, then use this file to
close the original and run other code. Any ideas?

Thanks

Adam Bush

 

[dustinbrearton via OfficeKB.com]

If you also put a call to your Sub that you want to run in the Workbook_Open
sub it should continue on with the code. Try adding an error handler to the
code and see if that doesn't help.

On Error Resume Next

 

[adambush4242]

Dustin,

I tried both of those suggestions and they didn't work? Any more ideas? Is
there any other information I can provide you with to narrow down the problem?

Thanks

Adam Bush

 

[Dave Peterson]

Why not just have the workbook_open() event in the first workbook close that
workbook?

Option Explicit
Private Sub Workbook_Open()

dim wkbk as workbook
set wkbk = workbooks.open(filename:="s:\ome\other\work\book.xls")

me.close savechanges:=false

End Sub

 

[Bernie Deitrick]

Try

Workbooks.Open FileName:="whatever.XLS"
ActiveWorkbook.RunAutoMacros xlAutoOpen

With the file opened having a macro like so:

Sub Autpen()
'Some code to do stuff here
End Sub


HTH,
Bernie
MS Excel MVP

 

[adambush4242]

Dave,

The problem is I need to close the first workbook and then continue to run
code, so I can't do all this from the first workbook.

Bernie,

I tried your idea with the exact same results.

Any more ideas?

Thanks

Adam Bush

 

[Dave Peterson]

You removed the code from the second workbook that closed the first workbook and
it still stopped????

I don't have another guess.

 

[Wullie]

Am I right in understanding the problem as follows?

Workbook 1 open with code opens Workbook2.
Once Workbook2 opens it closes Workbook 1.

If that's the case, surely the reason it stops working is that the code no
longer exists because the sheet is closed.

 

[adambush4242]

Wullie,

Workbook1 opens workbook2. Workbook 2 then closes workbook1 and runs a
bunch of other code. Also, after this code is run from workbook2, workbook1
is reopened and workbook 2 is closed. The whole reason for this is I want to
just have workbook1 open at all times except when the code's running, however
for the code to be run correct workbook1 needs to be closed for a period of
time. Any ideas?

Thanks

Adam Bush

 

[Wullie]

But the code exists within workbook1.

Therefore, once you open workbook2 and closed workbook1, the code no longer
exists to run. Does that make sense?

Can you write this code in a separate workbook that you can leave open?

eg Workbook1 holds what it currently holds without the code, workbook3 holds
the code - both are open.
The code is run from workbook3, closes workbook1, opens workbook2, does what
is required, closes workbook2 then reopens workbook1, all the while workbook3
remains open.