Reading a named range

[Karl Thompson]

We have built a summary workbook which depends heavly on Excel's ability to
read data from named cells in dozens of other workbooks. The VBA code looks
similar to this:

C.Range(FreeCFYieldCol & R).Formula = "='w:\" & Wb &
".xls'!iFCFYLD"


The problem is performance. Each workbook has maybe a dozen named cells
which are to be read by the summary workbook. These workbooks sit on a
network. I'm assuming that each workbook file is opened and closed every
time a named cell's value is read.

Question, is there a method my which we can code the summary workbook so
that it retrieves the values of all 12 named cells (probably storing those
values in variables) so that the workbooks are opened and closed but once?

TIA

 

[Karl Thompson]

Am I missing something? How does one retrieve data from multiple range names
this way? Otherwise, the workbook is opened and closed for each range name,
isn't it?

Try putting a formula that retreives the data directly from the cells you
need.

='[Other workbook.xls]'!RangeName (Single quotes and braces if
spaces in workbook's name)
or
=OtherWorkbook.xls!RangeName

Use the full path if not the same as the current Workbook

We have built a summary workbook which depends heavly on Excel's ability to
read data from named cells in dozens of other workbooks. The VBA code looks
similar to this:

C.Range(FreeCFYieldCol & R).Formula = "='w:\" & Wb &
".xls'!iFCFYLD"


The problem is performance. Each workbook has maybe a dozen named cells
which are to be read by the summary workbook. These workbooks sit on a
network. I'm assuming that each workbook file is opened and closed every
time a named cell's value is read.

Question, is there a method my which we can code the summary workbook so
that it retrieves the values of all 12 named cells (probably storing those
values in variables) so that the workbooks are opened and closed but once?

TIA