Error handling: #REF!

[AleemAM123]

I have an excel sheet where it generates a file path and name based on the date specified. If the file is not there though the update links box pops up and prompts for the file location. I'd like to find a way to suppress the box if the file is not there and put a value in the cell indicating the file was missing e.g. ML.

The following is a piece of the code:


.Range("c9").Offset(NextRow, 0).Formula = theDate

monthlyDir = Format(theDate, "mmm")
FileName = "Conv_" & Format(theDate, "YYYY_MM_DD") & "_DCS.xls"
FileName2 = "'E:\OpsDailyRpt\" & yearlyDir & "\" & monthlyDir & yearlyDir & "\[" & FileName & "]"




'retrieve data
.Range("d9").Offset(NextRow, 0).Formula = "=" & FileName2 & "Sheet1'!c44"
.Range("e9").Offset(NextRow, 0).Formula = "=" & FileName2 & "Sheet1'!g44"
.Range("f9").Offset(NextRow, 0).Formula = "=" & FileName2 & "Sheet1'!h44"


First I take the date, then use it in the construction of the path and then the filename, then I create the links in the sheet for a specific cell. It may be that that file has the incorrect name or just was not created. The error would them pop up for each of the cell references.

So I could either suppress the error after each cell reference or I guess I could check whether the file exists and if it doesnt, go on to the next date (I am not sure how to check that a file exists).

[shrivallabha]

To check existence of file you can use a UDF such as this one:
Function bFileExists(sFile As String) As Boolean
If Dir(sFile) <> "" Then bFileExists = True
End Function

This is from book - Programmer to Programmer which was recommended on XLD's website.