Referencing a file with a variable

[VBAva]

I have a file called EMFCalculator.vXX.xls containing the code for the program that i have written. i have a variable in the code which stores the name of this file, as the versions change, the name changes so with the variable i dont have to change the code for each version, eg EMFCalculator6.2.xls (just stores the filename, not the whole path)

in this file there is a worksheet called WorksheetInfo and there is a named call, TemplateName.
this cell contains the full name and path of a custom template that i need to use.

i can use the line
Workbooks.Add Template:=Sheets("WorkbookInfo&quot.Range("TemplateName&quot
to open up the template fine when EMFCalculator is active, but i need to be able to open it without EMFCalculator being active.
i cant get the verbage just right to do this.
i think it is not too far off this but i cant get it right, any help would be welcome

Workbooks.Add Template:=Windows("&CalculatorVersion&&quot.Sheets("WorkbookInfo&quot.Range("TemplateName&quot

this code is placed in the same workbook where CalculatorVersion is declared so i dont think that is the problem

 

[robcarr]

i havent checked it but you have :-
Workbooks.Add Template:=Windows("&CalculatorVersion&&quot.Sheets("WorkbookInfo&quot.Range("TemplateName&quot

it should be:-
Workbooks.Add Template:=Windows(CalculatorVersion).Sheets("WorkbookInfo&quot.Range("TemplateName&quot

hope it helps.



Thanks Rob.

 

[VBAva]

thanks for the help but
i cant seem to get it to work :-(

i keep getting errors, from object not defined to does not suport this object or method.

i found one problem about CalculatorVersion, it was blank but i have fixed that problem.

to check the values i put in message boxes,

MsgBox CalculatorVersion
MsgBox Worksheets("WorkbookInfo&quot.Range("TemplateName&quot

both give the expected result, but if i use

MsgBox Windows(CalculatorVersion).Worksheets("WorkbookInfo&quot.Range("TemplateName&quot

i get "Object doesn't support this object or method"
if i put .Text or .Value at the end of the line it does not make any difference.
i think i have tried all combinations but the right one. this is very frustrating :-(
i cant go anyfurther until i get this sorted out

 

[Jooky68]

I dont know if this helps, but if its a named cell. You can be anywhere within that workbook and reference the named cell. No two named cells in a workbook can have the same name. So you jsut have to activate the workbook and do a range("templatename&quot.value command....no matter what sheet you are on it will get the value. Therefore there is no need to go to that sheet in the coding.