Hello Excel programming gurus (and others),

Assume I have a workbook, call it WB1, open. Then assume that
I open a second workbook (WB2).

Workbook WB2 has nothing in it but data. (It has nothing which
in the way of macros or code of any kind.) Workbook WB1 however
has a large number of macros which will be used to organize,
scan, and reformat the data contained in Workbook WB2.

My question is, is there a way, using macros and code which
is contained in WB1, to capture when WB2 is opened and perform
some set of tasks? I haven't been able to successfully get
the "On_Open" macro in WB1 to fire when I open WB2. (FWIW,
the WB2 names won't always be the same so I can't hardcode
in a specific name to look for.)

Suggestions of how I might be able to do this?

Many thanks, in advance, for any ideas or suggestions.

David

The way to so it is to set a variable to the newly opened workbook, and get
your macros to work on the variable.

Set oWB = Workbooks.Open(filename)

With oWB
do something
.Save
.Close
End With


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"David Schrader" wrote in message
...
Hello Excel programming gurus (and others),

Assume I have a workbook, call it WB1, open. Then assume that
I open a second workbook (WB2).

Workbook WB2 has nothing in it but data. (It has nothing which
in the way of macros or code of any kind.) Workbook WB1 however
has a large number of macros which will be used to organize,
scan, and reformat the data contained in Workbook WB2.

My question is, is there a way, using macros and code which
is contained in WB1, to capture when WB2 is opened and perform
some set of tasks? I haven't been able to successfully get
the "On_Open" macro in WB1 to fire when I open WB2. (FWIW,
the WB2 names won't always be the same so I can't hardcode
in a specific name to look for.)

Suggestions of how I might be able to do this?

Many thanks, in advance, for any ideas or suggestions.

David

You could add a button to a toolbar that would call the code in WB1.
That could be done in the workbook_open event in WB1.
(the button should be removed when the workbook closes)
That assumes your code is written to work on the ActiveWorkbook.
You would want your code to display a message box asking if
the intent is to rearrange the workbook
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"David Schrader"
wrote in message
Hello Excel programming gurus (and others),

Assume I have a workbook, call it WB1, open. Then assume that
I open a second workbook (WB2).

Workbook WB2 has nothing in it but data. (It has nothing which
in the way of macros or code of any kind.) Workbook WB1 however
has a large number of macros which will be used to organize,
scan, and reformat the data contained in Workbook WB2.

My question is, is there a way, using macros and code which
is contained in WB1, to capture when WB2 is opened and perform
some set of tasks? I haven't been able to successfully get
the "On_Open" macro in WB1 to fire when I open WB2. (FWIW,
the WB2 names won't always be the same so I can't hardcode
in a specific name to look for.)

Suggestions of how I might be able to do this?

Many thanks, in advance, for any ideas or suggestions.

David