Evaluate text string as formula

[AshMorK]

Hi Everyone,

I have the following code to get a a cell linked to another workbook, but
when i call the function it returns a #REF!.
The function is trying, for example, to do the following:
Evaluate (='H:\bravhec\[Book1.xls]Sheet1'!A1)


Function GetRange(FilePath As String, FileName As String, SheetName As
String, _
SourceRange As String)
Dim link As String

'Add formula links to the closed file
link= "='" & FilePath & "\[" & FileName & "]" & SheetName _
& "'!" & SourceRange

Application.CutCopyMode = False
GetRange = Evaluate(Link)
End Function

thanks in advance!

 

[Peter T]

I don't think Evaluate will work like that with the formula pointing to a
closed file.

You can use an XL-4 Macro, see J-Walk's tip
http://www.j-walk.com/ss/excel/tips/tip82.htm

or if you can borrow a cell try something like this

sFla = "(='H:\bravhec\[Book1.xls]Sheet1'!A1"
With Range("A1")
.Formula = sFla
v = .Value
.Clear
End With
MsgBox v

Regards,
Peter T