How to verify the existence of a file with formulas in Excel 97?

[Mike-hime]

Greetings fellow VBAers,

I'm writing a workbook that requires the user(s) to input the file names of
other workbooks into a range of cells so that my VBA code can open and
collect data from them. I've written a bit of VBA code to return an error to
the user(s) of my workbook if they have input the file name of one of these
files incorrectly. It looks like this:

Dim USERFILE As Variant

For Each USERFILE In Range("C5:C8")
If Dir("F:\NEWESTIMATE\" & USERFILE & ".xls", vbNormal) = "" Then
MsgBox "' " & USERFILE & ".xls '" & " is not a valid estimate!",
vbOKOnly, "Error!"
End
End If
Next

Is there an excel function that can achieve the same results? I wish to
apply conditional formatting to the cells containing the file names in such
a way that if the user enters a file name, and that file does not exist, the
color of that cell (or some other visual aid,) will change to alert the
user.

Using both of these methods together would be ideal!

Any help would be greatly appreciated. TIA

Mike-hime.

 

[Jake Marx]

Hi Mike-hime,

You can do this with Conditional Formatting and a custom VBA function
(User-Defined Function or UDF). Paste this function into a standard module
in the VBE:

Public Function FileExists(rsPath As String) As Boolean
On Error Resume Next
FileExists = Len(Dir$(rsPath, vbNormal))
On Error GoTo 0
End Function

Now, select the cell (let's say A1) with the filename to check. Select
Format | Conditional Formatting..., select "Formula Is" from the dropdown,
and enter the following formula:

=Not(FileExists(A1))

Now just click the Format... button and select the desired format for
invalid filenames.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Mike-hime]

Thanks for the help. Your suggestion was excellent

Now maybe you can help me something else.

While I am editing formulas in Excel if I use any navigation keys on the
keyboard, Excel inserts relative cell references automatically. (For
instance, pressing left adds a relative cell reference to the cell just left
of the active cell.) If I can't find where to turn this feature off in
Excel's options, I *WILL* kill myself. I prefer to type cell references
(Relative or Absolute,) manually and use the arrow keys to navigate while I
edit.

I'm sure I'm just over looking the check box somewhere, so I figured one of
the experienced users could probably tell me right off the top of their
head.

Thx again,

-Mike-hime

 

[Jake Marx]

Hi Mike-hime,

F2 will toggle this behavior on and off (notice that the status bar switches
between "Edit" to "Enter" when you hit F2 while editing a cell). So if
you're editing a formula and want to use the arrow keys to move in the
formula bar, you should hit F2 first. I don't think there is a global
option to turn that feature off.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]