Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Which Directory (XP; SR2)

    I use the following code to recurse through a subdirectory looking for all ".txt" files and then perform some code on each file found. The problem with the code is that is it not recognizing the subdirectory; error message"File not Found". If I set the path using Excel's file open feature and then run the code everything works fine.

    Sub GetFile()
    oPath = "D:Hold"

    StrFileName = Dir("" & oPath & "*.txt")
    While StrFileName <> ""
    'Do some code
    Wend
    End sub


    Thanks,
    John

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Which Directory (XP; SR2)

    Dir is not going to return multiple file names. You would be better off using the Application.FileSearch as demonstrated by <!profile=LegareColeman>LegareColeman<!/profile> <!post=here,416110>here<!/post>, except don't use .Filetype = msoFilter, specify .FileName = "*.txt".
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Which Directory (XP; SR2)

    This works for me:

    <pre>Sub GetFile()
    Dim strPath As String, strFileName As String
    strPath = "D:Hold"
    strFileName = Dir(strPath & "*.txt")
    While strFileName <> ""
    'Do some code
    strFileName = Dir
    Wend
    End Sub
    </pre>

    Legare Coleman

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Which Directory (XP; SR2)

    Legare,

    Try it with this code:

    Sub GetFile()
    Dim strPath As String, strFileName As String
    strPath = "D:Hold"
    strFileName = Dir(strPath & "*.txt")
    While strFileName <> ""
    Open strFileName For Input As #1 This is where you should see the "unable to locate file"; it is due to the path
    'Do some code
    Close #1
    strFileName = Dir
    Wend
    End Sub



    John

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Which Directory (XP; SR2)

    The Dir function returns only a filename, not a path. So you must specify the path in the code: replace

    <code>Open strFileName For Input As #1</code>

    with

    <code>Open strPath & "" & strFileName For Input As #1</code>

Posting Permissions

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