help w/excel 2003 file exist vba

[meterguy]

I need to create an excel spreadsheet that reports on whether files exist in one of two directories.
The leftmost column has 7 digit numbers or aphabetic id characters.
All other columns are for specific dates.
The file naming convention is number_date.
So if cell a3 has 0709002 and c1 has 6-7-3006 then cell c3 should have a E for exist if it is found in f:\files\0709002.

Once the file is processed, it is moved to f:\files\0709002\backup, so cell c3 should show an E, even if the file is moved to the backup folder.
Any help is appreciated.

 

[PHV]

And what have you tried so far ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[firefytr]

Checking whether a file exists is easy enough in VBA, check out the Dir() method. For copying files, check out the FileCopy method.

-----------
Regards,
Zack Barresse

 

[CautionMP]

meterguy,
Typed and untested, usage in Excel [tt]=FileExists(A3,C1)[/tt]

Function FileExists(FileName As String, FileDate As Date) As String
On Error GoTo FileExists_Error
Dim strFileNameToCheck As String
strFileNameToCheck = FileName & "_" & Format(FileDate, "M-D-YYYY") & ".xls"
If Dir("f:\files\" & FileName & "\" & strFileNameToCheck) <> "" Then
  FileExists = "E"
ElseIf Dir("f:\files\" & FileName & "\backup\" & strFileNameToCheck) <> "" Then
  FileExists = "E"
Else
  FileExists = ""
End If
Exit Function

FileExists_Error:
Select Case Err.Number
  Case 52 'Bad file name or number
    'Drive or Directory does not exist
    FileExists = ""
  Case Else
    If MsgBox("An error has occured, do you want to debug?", _
              vbYesNo, "FileExists Error") = 6 Then
      Stop
    End If
End Select
End Function

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)