Search for flename in directory

[Ahmed]

I am using a macro to automate an openfile from files ina specific directory. The file contains the date in the yyyymmdd format which isnt a problem. im iteratioing through the file searching for a file containing any of the last nine days as follows:

Sub test()

Dim i As Integer
Dim xlFileName As String
i = 0

ChDrive "E" ' set default drive
ChDir "E:\test"

While i < 9

xlFileName = "anc" & Format$(Now - i, "yyyymmdd") & "Sales.xls"
If (xlFileName = True) Then ***this part is where I am having difficulties, dont know how to test its existance
msgbox 'just to see if it work or not

End If
i = i + 1
Wend

End Sub

 

[Richie(UK)]

Hi A,

How about?

Sub test()
    Dim i As Integer
    Dim xlFileName As String
    i = 0
    
    ChDrive "C" ' set default drive
    ChDir "C:\test"
    
    While i < 9
        xlFileName = "anc" & Format$(Now - i, "yyyymmdd") & "Sales.xls"
        If Len(Dir(xlFileName)) > 0 Then
            MsgBox xlFileName 'just to see if it work or not
        End If
        i = i + 1
    Wend

End Sub

HTH

 

[Ahmed]

The length wont work as all the files are of similar format, i tried it and unfortunately it doesnt accomplish what i need it ot do

 

[Ahmed]

do have any other suggestions, thanks for your help!

 

[Ahmed]

Anyone?

 

[Joe4]

this part is where I am having difficulties, dont know how to test its existance

Check out this thread: http://www.mrexcel.com/board2/viewtopic.php?t=99108&highlight=check+cheque+file+existance+existence

Note that you must use the full path of the file when checking for its existence.