run macro in another workbook - error 1004

[John Keith]

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
(e-mail address removed)

 

[John Keith]

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
(e-mail address removed)

 

[Dave Peterson]

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]

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
(e-mail address removed)

 

[Dave Peterson]

Yes it is <vbg>.

Glad it worked after you fixed MY typo!

 

[Dave Peterson]

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

 

[John Keith]

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
(e-mail address removed)

 

[Dave Peterson]

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!)