Excel links to other workbooks

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brendan Welch - 07 Apr 2004 07:15 GMT

I am trying to build a workbook that summarises data out of other
workbooks. This shows a month by month break down, however, some of the
files are not created yet, but I would like it to show the values as
soon as that file is created. When I fill in the formulas to link to
these non-existant files, upon reopening the main book, I get the
"Update links" message, followed by a "File not found" dialog for each
non-existant file link. I dont want these to show, but still want it to
update the existing links. Any ideas?

Cheers

Brendan

Reply to this Message

Dave Hawley - 07 Apr 2004 07:57 GMT

Hi Brendan

One way would be to create these 'non existant file' as blank Workbooks.
Then, when it's time, open them and place in the data.

The other way is via a Custom Function like below. To use this, push
Alt+F11 then Insert>Module and paste in the code below

Function DoesWorkBookExist(FileName As String, _
FilePath As String) As Boolean
With Application.FileSearch
.LookIn = FilePath
.FileName = FileName
DoesWorkBookExist = .Execute > 0
End With
End Function

Now, in any cell use this with the IF function like;

=IF(DoesWorkBookExist("Book1.xls","C:\OzGrid Likom\Testings"),"Do if
True","")

Where "Do if True" is the full name and path of the Workbook to link to

Reply to this Message