Macro call, one workbook to another

[Guest]

Hi -
I have two workbooks in an application.
Book "One" opens before "Two". (a macro
does this.)
There are functions and subs in general modules
in book one, that I want to run from the Open
event in book two.

I don't want to have to copy the code and have it
be in two 'places.

Application.Run("MYCUSTOM.XLM!My_Func_Sum", 1, 5)

I tried to modify the above application.run example from
the help section. The line below was a flat out guess.
Artemaint is the module containing the zpend_qtyf function
which returns an integer.

' line below is to be part of a sub executing from the
' booktwo.xls open event.

PendQty = Application.Run("bookone.xls(artemaint)!zpend_qtyf")

If it's possible, please provide an example of the
correct syntax. I looked at the help for "Call" and
it said nothing about what I'd like.

The problem will go away when booktwo becomes an add-in
for bookone, but I'm not there yet.

As I write this, I'm heading into Walkenbach's book, but I'm not
optimistic about finding a reference quickly.
Thanks much.

 

[Guest]

Look under global macros.

 

[Bob Phillips]

You don't need the module name, all you need is

PendQty = Application.Run("'bookone.xls'!zpend_qtyf")

This does assume that zpend_qtyf is a function, not a sub, otherwise nothing
will be returned into PenQty.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Tom Ogilvy]

PendQty = Application.Run("bookone.xls!artemaint.zpend_qtyf")

but if there is only one zpend_qtfy in bookone, then you don't need the
module name

PendQty = Application.Run("bookone.xls!zpend_qtyf")

 

[Guest]

Thanks Whiz, will do.
--
Neal Z

Look under global macros.

 

[Guest]

Thanks Bob, will give it a try.

--
Neal Z

You don't need the module name, all you need is

PendQty = Application.Run("'bookone.xls'!zpend_qtyf")

This does assume that zpend_qtyf is a function, not a sub, otherwise nothing
will be returned into PenQty.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

Dear Tom,
Again, many thanks. I thought Bob's answer might be a little "short".
will try yours out asap. I'd love to give your replay the green useful
check, but I'm not seeing it anymore on the reply screen I get, I'm not sure
why.

 

[Tom Ogilvy]

Bob's answer was pretty much the same as my second suggestion.

While he didn't need it for the sample names you provided (no space in the
name), he did raise a good point by including the single quotes which are
necessary if there is a space in the workbook name.

so if the workbook name was "Book One.xls" then

PendQty = Application.Run("'book one.xls'!artemaint.zpend_qtyf")

as a restatement.