Excel links to other workbooks

B

Brendan Welch

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

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
D

Dave Hawley

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

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
Top