Running a Macro on another worksheet

I am trying to run a Macro on a different WorkBook.

When I "HARD CODE" the name of the WorkBook into the Macro it works, but how can I get it to work when I assign the name of the WorkBook to a variable.


The name of the workbook contains spaces.
This works: Application.Run ("'Search Database 2025 test.xls'!RedFont")

This is the bit of code that's creating the problem when I try to use it with a variable

Code:

---

    ol2 = ActiveWorkbook.Name
    Workbooks(ol2).Application.Run ("!RedFont")

---

try

Code:

---

    ol2 = CreateObject("Scripting.FileSystemObject").GetBaseName(ActiveWorkbook.Name)
    Application.Run "'" & ol2 & "'!RedFont"

---

I would have thought that if the macro RedFont is in the ActiveWorkbook, that you'd just need to call it

Thanks jindon:

That works perfectly.

Just curious.

If I Dim ol2, exactly how would I declare it ?
As a String?

Paul_Hossler:

A couple of issues.

When the filename that you're calling from another workbook contains spaces, you come up with these issues if you're trying to use/call that workbook by referencing its Variable name from another Workbook.
Excel VBA APPARENTLY does NOT allow you to just do what you were suggesting.

That being said, I would like to see how others have gotten around this in addition to what jindon provided, which turned out to be a workable solution.

Thanks for your input.

BTW:

When calling A Macro on another workbook, If you create a function like:

Code:

---

Function GetWorkbookName() As String

      GetWorkbookName = ActiveWorkbook.Name
  End Function

---

Then you can use:

Code:

---

Application.Run "'" & GetWorkbookName & "'!RedFont"

---

To get the same result as:

Code:

---

ol2 = CreateObject("Scripting.FileSystemObject").GetBaseName(ActiveWorkbook.Name)
        Application.Run "'" & ol2 & "'!RedFont

---

Quote:

---

Originally Posted by simora

Paul_Hossler:

A couple of issues.

When the filename that you're calling from another workbook contains spaces, you come up with these issues if you're trying to use/call that workbook by referencing its Variable name from another Workbook.
Excel VBA APPARENTLY does NOT allow you to just do what you were suggesting.

That being said, I would like to see how others have gotten around this in addition to what jindon provided, which turned out to be a workable solution.

Thanks for your input.

---

I know that. I was just saying that this ...

Code:

---

    ol2 = ActiveWorkbook.Name
  Workbooks(ol2).Application.Run ("!RedFont")

---

... seems to be running a macro IN the ActiveWorkbook

Running macro ON the Activeworkbook that in a non-Activeworkbook is different

Quote:

---

Originally Posted by simora

Thanks jindon:

That works perfectly.

Just curious.

If I Dim ol2, exactly how would I declare it ?
As a String?

---

You are welcome and thanks for the feedback.
ol2 should be a String type variable.

NOTE: I can still log in under jindon, but since I failed to update email address, I can only view the forum, so I created a new account under jindon22.

Paul_Hossler

I see your point.

What transpired before that is that the user had selected the other workbook, so the other remote workbook was selected and therefore became the Active Workbook.

Hope that makes sense. :yes

jindon22

Just curious:

Are we supposed to update our forum email address ?

jindon22

Just curious:

Are we supposed to update our forum email address ?

Simora, Jindon's issue is simply an administration one that I've since fixed for him.

Quote:

---

Originally Posted by simora

Paul_Hossler
What transpired before that is that the user had selected the other workbook, so the other remote workbook was selected and therefore became the Active Workbook.
Hope that makes sense. :yes

---

I sort of follow


From post #1 ..

Code:

---

Running a Macro on another worksheet (Assume you meant 'Workbook')

I am trying to run a Macro on a different WorkBook.

---

It wasn't clear to me (at least) which WB had the macro and which WB the macro was intended to operate on

Since this works, I assumed that the WB 'Search Database 2025 test.xls' contained the macro RedFont

Code:

---

This works: Application.Run ("'Search Database 2025 test.xls'!RedFont")

---

What I missed was some how activating the macro containing WB, either manually or in code

Code:

---

Workbooks("'Search Database 2025 test.xls'").Activate

---

which as you said then makes Search Database 2025 test.xls the ActiveWorkbook in

Code:

---

ol2 = ActiveWorkbook.Name
Workbooks(ol2).Application.Run ("!RedFont")

---

Simora,

No, you don't need to update your email address unless you need it to.

I was trying to change the email since old email is not convenient for me, but I failed to do it right.

I really appreciate the kind and quick action taken by Aussiebear.

Thanks guys for all your assistance.
Seems that some of my posts went into the AlienScape because I don't see them.