Approach Linked documents via INDIRECT

I try to approach an external document, defined in cell A1 in cell B2

A1 := '[File1.xls]Sheet1!$A$1
A2 := =INDIRECT(A1)

This works perfectly if File1.xls is open, but gives #REF! is File1.xls
isn't open.

Is there a way I can link to a file defined in a cell, without that file
being opened

(in the real problem I link to a file with a specific element in the name
that changes frequently. I want to define the exact file to link to in the
"master" excel sheet).

Thanks,

Johannes

Johannes wrote:
A2 := =INDIRECT(A1)

This works perfectly if File1.xls is open, but gives #REF! is File1.xls
isn't open.


Correct. That is a feature of INDIRECT

Is there a way I can link to a file defined in a cell, without that file
being opened

Not without running a macro to change the link source(s) for you.
For example you could have a Worksheet_Change procedure that detects a
change in A1 and changes the link source to that file. Something like:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
If IsEmpty(Me.Range("A1")) Then Exit Sub
If Dir(Me.Range("A1"))="" Then
MsgBox "File " & Me.Range("A1") & " not found"
Exit Sub
End If
ThisWorkbook.ChangeLink ThisWorkbook.LinkSources(xlExcelLinks)(1),
Me.Range("A1")
End Sub

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Thanks Bill,

Does not happen too often, but every now and then i am disappointed by
Excel.

Thanks for the work-around though.

Johannes

"Bill Manville" schreef in bericht
...
Johannes wrote:
A2 := =INDIRECT(A1)

This works perfectly if File1.xls is open, but gives #REF! is File1.xls
isn't open.


Correct. That is a feature of INDIRECT

Is there a way I can link to a file defined in a cell, without that file
being opened

Not without running a macro to change the link source(s) for you.
For example you could have a Worksheet_Change procedure that detects a
change in A1 and changes the link source to that file. Something like:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
If IsEmpty(Me.Range("A1")) Then Exit Sub
If Dir(Me.Range("A1"))="" Then
MsgBox "File " & Me.Range("A1") & " not found"
Exit Sub
End If
ThisWorkbook.ChangeLink ThisWorkbook.LinkSources(xlExcelLinks)(1),
Me.Range("A1")
End Sub

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup