run macro in another workbook - error 1004


J

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

Advertisements

J

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

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.
 
J

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

Advertisements

J

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

Advertisements

D

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top