Access a named cell in a closed workbook

[Kaak]

I want to access a named cell in a closed workbook.
Does anybody know how to do this

 

[Norman Harker]

Hi Kaak!

Here's an example:

='C:\My Files\NewsGroups\Delete1 OK.xls'!MyNamedCell

Note the use of punctuation and the need for the full path name.

Best method of achieving this would be a point and click approach with
the workbook open. You'll see from the length of the reference, it's
not an ideal approach to use it in a complex formula as you'll soon it
the "formula too long" barrier.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Kaak]

thnx

How do I use this in VBA code..

 

[Bob Phillips]

Kaak,

I don't think you can access a closed workbook in VBA (well you can, but it
is hardly worth the effort). You need to open it and use it

Set oWB = workbooks.Open Filename:="C:\My Files\NewsGroups\Delete1
OK.xls"
Msgbox oWb.Range("MyNamedCell")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

You could also drop that formula into a helper cell (empty), get the value and
then clear that cell.

And John Walkenbach has some code:

http://j-walk.com/ss/excel/eee/eee009.txt
Look for either: GetDataFromClosedFile or GetValue.