VBA Stmt Format to Get Contents of Cell in Another Book

[SidBord]

I have two workbooks "A" & "B". I want a macro in
WorkbookA to fetch the contents of a named variable
("TestVal")in WorkbookB. For the life of me, I can't
remember how to format the statement. It will look
something like this:
Dim X as Integer
X = Workbooks("'C:\FolderNm\WorkbookB.xls'"!TestVal
which, of course, is incorrect. It seems I need a range
statement in there somewhere. I really would like to do
this without explicitly opening WorkbookB. I have set up
WorkbookB as a reference in WorkbookA's reference list, if
that's any help. Any suggestions?

 

[Peter Beach]

Hi,

AFAIK you can't use VBA to read a closed workbook. But assuming the
workbook is open, the following should work.

Sub B()
Dim r As Range
Dim WB As Workbook
Set WB = Workbooks("WorkbookB.xls")
Set r = WB.Names("TestVal").RefersToRange
Debug.Print r.Value
End Sub

HTH

Peter Beach