Code To Insert Cell Reference

[Guest]

The following line opens the macro "Recalculate" in the file "Template.xls":
Application.Run "'Template.xls'!Recalculate"
The problem is the name of the file often changes. That change is recorded
in cell "C27" of worksheet "Customize". Can someone tell me the code to have
the line reference this cell? I've tried many variations of:
Application.Run "'" & Sheets("Customize").Range("C27").Value &
"'!Recalculate" with no luck. Thanks!

 

[Niek Otten]

Application.Run Sheets("Customize").Range("C27").Value&"!Recalculate"

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Guest]

Niek,
Thanks for taking the time to answer. I tried an exact copy of your code but
it did not work (syntax error). I also tried
Application.Run "'" & Sheets("Customize").Range("C27").Value &
"'!Recalculate" but it did not work either. Please advise.
Carl

 

[Tom Ogilvy]

Should work if the hard coded version works. You are just building an
identical string to feed to the run command. there is no mystery here.

if
Application.Run "'Template.xls'!Recalculate"

works then you string

"'" & Sheets("Customize").Range("C27").Value & "'!Recalculate"

must produce "'Template.xls'!Recalculate"

And Template.xls must be open and hold the macro named Recalculate in a
general module, but that would be true for the hard coded example.

 

[Guest]

Niek,
I finally got the following to work:
Application.Run "'" & Sheets("Customize").Range("C17").Value & "'!Recalculate
I had to switch back to the correct workbook so it could find the worksheet
"Customize".
Thanks!

 

[Tom Ogilvy]

I had to switch back to the correct workbook
No you don't. You just have to reference it. Assume it is in a workbook
name test.xls

Application.Run "'" & Worksheets("Test.xls").Sheets("Customize") _
.Range("C17").Value & "'!Recalculate"

Of couse if it was in cell C17 rather than C27, that would make a difference
as well.

 

[Guest]

Your answer worked! Thanks again.

No you don't. You just have to reference it. Assume it is in a workbook
name test.xls

Application.Run "'" & Worksheets("Test.xls").Sheets("Customize") _
.Range("C17").Value & "'!Recalculate"

Of couse if it was in cell C17 rather than C27, that would make a difference
as well.