Excel links to other workbooks

  • Thread starter Thread starter Brendan Welch
  • Start date Start date
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 *****
 
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 *****
 
Back
Top