Call a file name from within string

E

etradeguru

Similar to my last post, I want to be able to change a parameter
within a calculation.

I have tried to modify code that I used for a similar event from
before but I am stumped.

The code is as follows: -

Dim extract As String
ActiveCell.FormulaR1C1 = _
extract = InputBox("Please enter Version Control Master file", "File
Name")
ActiveCell.FormulaR1C1 = _
"=+'[& extract &]Static Summary'!R9C3-'[Version Control Master
20070815.xls]Static Summary'R9C3"

Basically I am calling cell C9 from one sheet whose name changes daily
and subtracting the value in C9 on the other sheet which does not
change.

Responses/guidance gratefully received.
Thank you

Mark
 
J

JE McGimpsey

One way:

ActiveCell.FormulaR1C1 = _
"='[" & extract & "]Static Summary'!R9C3-'[Version Control Master
20070815.xls]Static Summary'R9C3"
 
E

etradeguru

Je,
Tried your suggestion , comes up with a debug error.

I also tried this: -

ActiveCell.FormulaR1C1 = _
"='[" & extract & ".xls""]Static Summary'!R9C3-'[Version
Control Master 20070815.xls]Static Summary

but that just debugs too.

Mark


One way:

ActiveCell.FormulaR1C1 = _
"='[" & extract & "]Static Summary'!R9C3-'[Version Control Master
20070815.xls]Static Summary'R9C3"

[email protected] said:
ActiveCell.FormulaR1C1 = _
"=+'[& extract &]Static Summary'!R9C3-'[Version Control Master
20070815.xls]Static Summary'R9C3"- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
J

JE McGimpsey

Not sure what 'debug' error you're getting - I'd expect an application
error.

Just noticed that you left out the last exclamation point. Try:

ActiveCell.FormulaR1C1 = _
"='[" & extract & "]Static Summary'!R9C3-'[Version Control
Master 20070815.xls]Static Summary'!R9C3"


='[Workbook1.xls]Static Summary'!R9C3-'[Version Control Master
20070815.xls]Static Summary'!R9C3
Je,
Tried your suggestion , comes up with a debug error.

I also tried this: -

ActiveCell.FormulaR1C1 = _
"='[" & extract & ".xls""]Static Summary'!R9C3-'[Version
Control Master 20070815.xls]Static Summary

but that just debugs too.

Mark


One way:

ActiveCell.FormulaR1C1 = _
"='[" & extract & "]Static Summary'!R9C3-'[Version Control Master
20070815.xls]Static Summary'R9C3"

[email protected] said:
ActiveCell.FormulaR1C1 = _
"=+'[& extract &]Static Summary'!R9C3-'[Version Control Master
20070815.xls]Static Summary'R9C3"- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
Click to expand...
 

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

Similar Threads

Reading from a file 1
Renaming files from a number string inside the file. 4
Is string allowable as a file name 2
Duplicating changes from master file from all sheets within a file. 1
Creating file name form string 1
Replacing references from one file to another 2
Help in Getting the File Name from a path 3
Use VBA to allow you to select a file name from a directory (public drive/server) 2

Top