Got it, Bob! Thanks for all your help. I renamed the file so it has no
spaces in it, but that wasn't what did it. Apparently, all OnAction wanted
was "Sheet1.GetTIR" - it didn't want the file, probably because that's the
file it was looking in anyway.

Whew! Glad that's over. And glad to have people who'll help like this.
Ed

"Bob Phillips" wrote in message
...
Hi Ed,

Thought we had lost you<vbg

You've got it right about the quotes, and yes I am doing it to keep VB
happy. The reason is that if the workbook name has embedded spaces, it
will
error unless you enclose it in single quotes. So

myWorkboo.xls!GetTIR

is okay, but

my Workbook.xls!GetTir

is not and is fixed by

'my Workbook.xls'getTIR

So you can see that it is, in my favourite phrase, defensive programming,
in
case of embedded spaces. The double quotes are just to enclose the single
quotes in building the string.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ed M" wrote in message
...
Bob - sorry for the lat response. I had to bug out of work early and
didn't
catch your message until now at home. I'll try to work through that
first
thing Monday morning.

I left out (oops!) that I had tried substituting the string that held
the
name of the workbook to open, but I'm not sure if I've got the syntax
correct, or if I'm using the wrong string (one with the file path vs.
just
the file name). Another Monday morning action item. But let me also
clarify what you gave me:
.OnAction = "'" & objWkbk.Name & "'!GetTIR"
that's double-quote single-quote double quote & name & double-quote
single-quote !macro double-quote ? Are there any rules to the quotes?
Or
do you just keep throwing them in until VB is happy?!? 8\

Thanks for keeping with me on this.
Ed

"Bob Phillips" wrote in message
...
Not tried this Ed, but if we just work it through. objWkbk is the
object
variable in your VB app, which is totally meaningless to the
commandbar
and
its OnAction property. Running a macro in another workbook is in
essence
of
the form

Application.Run "myBook.xls'!myMacro"

so we need to build this up. All we have is an object and an implicit
macro
name. We need the workbook name, which we can get from the object. So
something like

.OnAction = "'" & objWkbk.Name & "'!GetTIR"

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ed" wrote in message
...
Found the error in this:
I set OnAction in the Visual
Basic app as
.OnAction = "objWkbk.Sheet1!GetTIR"
where "objWkbk" is the Excel file opened by the VB app. But when
I
run
the
macro in the workbook, I get the error "Can't find
"objWkbk.Sheet1.xls".

Of course not! objWkbk is the *object* reference, not the *filename
string*! D'oh!

So I changed it. Now I get an error '400', whatever that means.
The
macro
is there, and it does run when called. I just can't get this menu
item
to
call it.

Ed