Hi,

Somebody (forgot the name, sorry) on this forum gave me a formula to
check if a filename exists:

Function BookExists(wb As String)
BookExists = Len(Dir(wb)) 0
End Function

It worked fine however now I am using it in a different sheet were the
filename is flexible like:

=IF(BookExists(path&filename&"*.xls");TRUE;FALSE)

were path is a named field and filename is a reference to a cell (like
A20)

It still works but only recalculates (checks if the file exists) when I
edit the cell and press enter again. Just F9 won't work.

Is there a way to make this formula recalculate by itself or
recalculate by a macro?

Thanks for your help.

Arien de Haan


--
Arien
------------------------------------------------------------------------
Arien's Profile: http://www.excelforum.com/member.php...fo&userid=1892
View this thread: http://www.excelforum.com/showthread...hreadid=514849

That somebody must have been Bob Phillips...


--
Arien
------------------------------------------------------------------------
Arien's Profile: http://www.excelforum.com/member.php...fo&userid=1892
View this thread: http://www.excelforum.com/showthread...hreadid=514849

Add the following line to the custom formula:

application.Volatile

This will force the formula to recalc everytime the spreadsheet recalcs.

Kevin Backmann


"Arien" wrote:


Hi,

Somebody (forgot the name, sorry) on this forum gave me a formula to
check if a filename exists:

Function BookExists(wb As String)
BookExists = Len(Dir(wb)) 0
End Function

It worked fine however now I am using it in a different sheet were the
filename is flexible like:

=IF(BookExists(path&filename&"*.xls");TRUE;FALSE)

were path is a named field and filename is a reference to a cell (like
A20)

It still works but only recalculates (checks if the file exists) when I
edit the cell and press enter again. Just F9 won't work.

Is there a way to make this formula recalculate by itself or
recalculate by a macro?

Thanks for your help.

Arien de Haan


--
Arien
------------------------------------------------------------------------
Arien's Profile: http://www.excelforum.com/member.php...fo&userid=1892
View this thread: http://www.excelforum.com/showthread...hreadid=514849

Kevin,

Thanks for your help, but I am afraid it still does not update.

I now made the formula:

Function BookExists(wb As String)
Application.Volatile
BookExists = Len(Dir(wb)) 0
End Function

Did I understand you correctly?

I still have to edit the cell with the formule (F2) and press enter.

regards,
Arien


--
Arien
------------------------------------------------------------------------
Arien's Profile: http://www.excelforum.com/member.php...fo&userid=1892
View this thread: http://www.excelforum.com/showthread...hreadid=514849

Sorry my mistake. It does work now.

Thanks a lot

regards,
Arien


--
Arien
------------------------------------------------------------------------
Arien's Profile: http://www.excelforum.com/member.php...fo&userid=1892
View this thread: http://www.excelforum.com/showthread...hreadid=514849