Detecting invalid links

[elanus]

A quick question about links, probably with an obvious answer for thos
in the know.

I want to avoid a value being displayed if a link is not valid becaus
the file does not exist. Is there an 'If Exists' command or similar?
mean for the file, not a cell, since the problem still occurs in th
latter case if a cell in the linked spreadsheet is tested wit
'ifexists'.

I have up to ten individual spreadsheets all containing a field that
want to include in a single composite file. These spreadsheets ar
named 'file1.xls', 'file2.xls', etc.

The composite file that retrieves each of the fields from th
individual files uses the formula ='c:\Excel\[file1.xls]Sheeta'!$E$6
to retrieve the field from the first file
='c:\Excel\[file2.xls]Sheeta'!$E$64 to retrieve the field from th
second file, and so on. So far so good; all fields are retrieved fro
their separate files and I can print a report from the composit
spreadsheet that shows all their values.

Now, sometimes some of the files will be missing. When I open th
composite file to print the new report, I get the normal warning 'Thi
workbook contains links to other data sources' and I am offered th
option to update or not. I select 'Update'.

If some files are not present, I get the further warning 'This workboo
contains one or more links that cannot be updated. To open as is, clic
Continue'. I click Continue.

However, when I look at the composite spreadsheet, the new values fro
the files that exist are displayed (great), but the values from th
missing files still show whatever value was previously in them from th
last update.

I want to be able to see the current state of play; new values fro
files that exist and some kind of null value, or zero, or error messag
from the files that don't exist.

Any advice would be appreciated

 

[Frank Kabel]

Hi
one way:
1. Use the following function FileExist from J. Walkenbach's site
(http://j-walk.com/ss/excel/tips/tip54.htm#func1) to dertime if the
file exists:
Private Function FileExists(fname) As Boolean
' Returns TRUE if the file exists
Dim x As String
x = Dir(fname)
If x <> "" Then FileExists = True _
Else FileExists = False
End Function

2. I would use a combination of this FileExist and the INDIRECT
function (this requieresthat you store your filename in a cell; e.g. in
cell A1):
=IF(FileExists("C:\temp\" & A1),INDIRECT("'C:\temp\[" & A1 &
"]Sheet1'!$A$1),"File does not exist")

HTH
Frank

 

[elanus]

Frank

Thanks a million; that has helped a great deal. I'm almost there, bu
not quite.

I am getting a #REF! error on the parameter to the INDIRECT statement.

Following your previous post, the formula in the cell is:

=IF(FileExists("C:\temp\" & A1),INDIRECT("'C:\temp\[" & A1
"]Sheet1'!$A$1"),"File does not exist")

If I follow the error comment and display the calculation steps, i
shows that it is trying to evaluate:
=IF(TRUE,INDIRECT("'C:\temp\[rcjc.xls]Sheet1'!$A$1"),"File does no
exist")

If I evaluate, it gives:
=IF(TRUE,#REF!,"File does not exist")

What is the problem with the Indirect part? It's not to do with th
quotes, is it

 

[Frank Kabel]

Hi
I think your other worksheet is closed. If yes, INDIRECT won't work.
Test this by opening the other worksheet and see if your INDIRECT
function works.

I can also recommend using the free Add-In Morefunc.xll (see
http://longre.free.fr/english). This adds the function INDIRECT.EXT
which overcomes Excel's restriction with closed files. Just replace
INDIRECT with INDIRECT.EXT and see if it works

HTH
Frank

 

[Jonathan Rynd]

I am getting a #REF! error on the parameter to the INDIRECT statement.

It appears that the INDIRECT function only supports references to open
worksheets.

Here's how I'd do it:

Have one cell for the
='c:\Excel\[file1.xls]Sheeta'!$E$64
and another cell for the
=IF(FileExists("C:\temp\file1.xls"),FirstCell,"File not there")

replacing FirstCell with the address of the appropriate cell.

 

[elanus]

Thanks to both Frank and Jonathon. I now have my main program workin
(mostly).

I'm not a VBA programmer, and in trying to set an example to post t
this forum, I find that the 'FileExists' function described above doe
not work in my new example, but works just fine in my full project.

I have copied the 'FileExists' module from my working project into m
example, and the formula '=IF(FileExists("c:\File1.txt"),"Y","N")
fails with a #Name? error, presumably meaning that it doesn't know wha
the command 'FileExists' is.

For what it is worth, I created the module by 'Tools', 'Macro', 'Visua
Basic Editor', then double-clicked 'ThisWorkbook' under 'VBAProject'
When the blank module appeared, I pasted the working 'FileExists
code.

Is there something that I have to do to make the cell formula use th
VBA code

 

[elanus]

All's well; ignorance, I'm afraid.

I needed it under 'Modules', not 'ThisWorkbook'.

I will close this thread and start another with the question relating
to this example that I was trying to build.