Results 1 to 6 of 6

Thread: Search for flename in directory
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2005
    Posts
    17
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Search for flename in directory

    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

  2. #2
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi A,

    How about?
    Code:
    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
    Richie

  3. #3
    New Member
    Join Date
    May 2005
    Posts
    17
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    New Member
    Join Date
    May 2005
    Posts
    17
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    do have any other suggestions, thanks for your help!

  5. #5
    New Member
    Join Date
    May 2005
    Posts
    17
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Anyone?

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default

    this part is where I am having difficulties, dont know how to test its existance
    Check out this thread: http://www.mrexcel.com/board2/viewto...ance+existence

    Note that you must use the full path of the file when checking for its existence.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •