run macro in another workbook - error 1004
 

I have a number of different macros, each in a separate workbook. Now
I want to create a macro in a new workbook that will open each
workbook one at a time and run the macro contained in that workbook.

My first attempt at this yielded the following error:

Run-time error '1004':
Cannot run the macro 'report1'.
The macro may not be available in this workbook or all macros may be
disabled.

(The macro in the target workbook is defined as Public Sub Report1()
and the one function in the code module is also defined as Public
Function Find_col())

I did some experimenting and created two other workbooks with very
simple macros (msgbox with text to show me what was executing) and
then called them from the same workbook that I was starting to
develop. I was able to execute both of the simple macros but still not
able to execute the macro I really want to run.

What should I look for in the target macro that is different from the
two macros that I can successfully run?


John Keith

run macro in another workbook - error 1004
 

On Sun, 21 Feb 2010 15:15:39 -0700, John Keith wrote:

What should I look for in the target macro that is different from the
two macros that I can successfully run?

After playing around with this problem I finally found what the issue
was. It seems that if the filename of the other workbook that has the
macro I want to run has a space charater in it then the runtime error
is produced. Seems strange that a valid filename would cause this
error but after removing all the spaces everything worked as expected.


John Keith

run macro in another workbook - error 1004
 

Are you using application.run to run the macro?

If yes, then maybe...

dim wkbk as workbook
set wkbk = workbooks.open(filename:=yourpathandfilename, readonly:=true)
application.run "'" & wkbk.name & '!macronamehere"

Those apostrophes are important.

John Keith wrote:

On Sun, 21 Feb 2010 15:15:39 -0700, John Keith wrote:

What should I look for in the target macro that is different from the
two macros that I can successfully run?

After playing around with this problem I finally found what the issue
was. It seems that if the filename of the other workbook that has the
macro I want to run has a space charater in it then the runtime error
is produced. Seems strange that a valid filename would cause this
error but after removing all the spaces everything worked as expected.

John Keith


--

Dave Peterson

run macro in another workbook - error 1004
 

On Tue, 23 Feb 2010 07:21:04 -0600, Dave Peterson
wrote:

Are you using application.run to run the macro?

Yes, is there another way to do it?

If yes, then maybe...

Yes, this solved the problem, but see below.

dim wkbk as workbook
set wkbk = workbooks.open(filename:=yourpathandfilename, readonly:=true)
application.run "'" & wkbk.name & '!macronamehere"

Those apostrophes are important.

And the missing double quote is also importnat :-)

application.run "'" & wkbk.name & "'!macronamehere"


John Keith

run macro in another workbook - error 1004
 

Yes it is <vbg.

Glad it worked after you fixed MY typo!

John Keith wrote:

On Tue, 23 Feb 2010 07:21:04 -0600, Dave Peterson
wrote:

Are you using application.run to run the macro?

Yes, is there another way to do it?

If yes, then maybe...

Yes, this solved the problem, but see below.

dim wkbk as workbook
set wkbk = workbooks.open(filename:=yourpathandfilename, readonly:=true)
application.run "'" & wkbk.name & '!macronamehere"

Those apostrophes are important.

And the missing double quote is also importnat :-)

application.run "'" & wkbk.name & "'!macronamehere"

John Keith


--

Dave Peterson

run macro in another workbook - error 1004
 

ps. But you are forgiven for the typo on "importnat".

Dave Peterson wrote:

Yes it is <vbg.

Glad it worked after you fixed MY typo!

John Keith wrote:

On Tue, 23 Feb 2010 07:21:04 -0600, Dave Peterson
wrote:

Are you using application.run to run the macro?

Yes, is there another way to do it?

If yes, then maybe...

Yes, this solved the problem, but see below.

dim wkbk as workbook
set wkbk = workbooks.open(filename:=yourpathandfilename, readonly:=true)
application.run "'" & wkbk.name & '!macronamehere"

Those apostrophes are important.

And the missing double quote is also importnat :-)

application.run "'" & wkbk.name & "'!macronamehere"

John Keith


--

Dave Peterson

--

Dave Peterson

run macro in another workbook - error 1004
 

ps. But you are forgiven for the typo on "importnat".

It does happen to all of us. Unfortunately our computers are not
intelligent enough to interpret the intent with a few characters mixed
up or missing.

Thank you again for your solution and your contributions to this
group, I can only hope to someday be a tenth as knowledgable about VBA
as you and others.



John Keith

run macro in another workbook - error 1004
 

A coworker (programmer type) always said that he didn't have to be a good
speller--just a consistent speller.

I'd nod my head and say that he's made that point in most of his written
communication.

(He didn't laugh as hard as I did!)

John Keith wrote:

ps. But you are forgiven for the typo on "importnat".

It does happen to all of us. Unfortunately our computers are not
intelligent enough to interpret the intent with a few characters mixed
up or missing.

Thank you again for your solution and your contributions to this
group, I can only hope to someday be a tenth as knowledgable about VBA
as you and others.

John Keith


--

Dave Peterson