Creating ranges in closed workbooks without #REF errors
 

I have written code to add range names in a Master file that refer to other
closed workbooks. The VBA code I use is as follows:

ThisWorkbook.Names.Add Name:="MyRangeName", _
RefersTo:="='\\Full Path\[FileName.xls]SheetName'!$A$1:$AA$100"

This was working for me last week, but now if I pull up Name Manager in
Excel 2007, all I see is #REF in the value column.

Surely I can refer to a range if it is in a closed workbook??

Does the workbook need to be open for it to work?

ps - I was using the INDIRECT function but it was killing my spreadsheet
performance because it is volatile.

Hope someone can help! I'm desperate

Conor
Microsoft