Syntax to refer to external workbook

[ecawilkinson]

Hi,

I need to refer to (in a cell) a named range in an external workbook, which is, for example, ='C:\Test\TestFile.xlsx'!Test_Range. However, I need to refer to an external workbook which is made of a path and file, I've tried something like:
="'" &File_Path & "\[" & File_Name & "]!Test_Range
where File_Path = "C:\Test" and File_Name = "TestFile.xlsx"
but this does not work. I would be great if someone could show me the syntax. The workbook is closed, but it does not have to be.

Thanks,
Chris

 

[Hui]

=Indirect("'" &File_Path & "\[" & File_Name & "]'!Test_Range")

Note the use of indirect and the ' after the ]
and the destination file must be open

 

[ecawilkinson]

Thank you.