Solved: Check that an excel file exists

[AleemAM123]

I have a folder with excel files named by date. Instead of opening each of these files to check the data manually I have some code to generate the filename based on the date and return the value of a cell. The code loops through a range of dates and checks each file in the folder, this works great but every now and then a file does not exist and then the code is interupted and a box pops up to locate the missing file.

After generating the filename, how can I check that an excel file actually exists in a directory?

[GarysStudent]

See:

http://www.excelguru.ca/content.php?157-Function-To-Check-If-File-Or-Directory-(Folder)-Exists

[AleemAM123]

Not getting it to work, it's stopped returning the values from the excel files.

This is what I have:



FileName = "Cost_" & Format(theDate, "YYYY_MM_DD") & "_7-3_Fin.xls"

FileName2 = "'C:\Documents and Settings\xxx\My Documents\costs\budgetLogs\" & yearlyDir & "\" & monthlyDir & yearlyDir & "\[" & FileName & "]"

If FileFolderExists(FileName2) Then
.Range("e2").Offset(NextRow, 0).Formula = "=" & FileName2 & "Spend Log'!c19"
Else

NextRow = NextRow + 3
NextRow1 = NextRow1 + 2
theDate = theDate + 1

End If

Loop

End Sub

Public Function FileFolderExists(strFullPath As String) As Boolean
'Author : Ken Puls (www.excelguru.ca (http://www.excelguru.ca))
'Macro Purpose: Check if a file or folder exists
On Error GoTo EarlyExit
If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True

EarlyExit:
On Error GoTo 0
End Function

[snb]

sub M_snb()
c00="C:\Documents and Settings\xxx\My Documents\costs\budgetLogs\" & yearlyDir & "\" & monthlyDir & yearlyDir & "\"
c01 = "Cost_" & Format(theDate, "YYYY_MM_DD") & "_7-3_Fin.xls"

if dir(c00 & c01)<>"" then
thisworkbook.sheets("Sheet1").Range("e2").Offset(NextRow, 0)= "=" & c00 & ""'[" & c01 & "]Spend Log'!c19"
Else
NextRow = NextRow + 3
NextRow1 = NextRow1 + 2
theDate = theDate + 1
End If
End sub



PS. you can be advised more effectively if you post a sample workbook.

[AleemAM123]

SNB/Gary,

Thanks for the assist.
SNB your code did the trick.