Loop Through Files - File not found

Question

So, I have this code that I found here: https://stackoverflow.com/a/10382861

And when I first found it, I modified it to actually open the files that it found:

Sub LoopThroughFiles()
    Dim SourceFolder As String
    SourceFolder = "C:\Users\Jeff\Downloads\IO\"

    Dim StrFile As String
    StrFile = Dir(SourceFolder & "*.xls")

    Dim wb As Workbook

    Do While Len(StrFile) > 0
        Debug.Print StrFile
        Set wb = Workbooks.Open(Filename:=StrFile)
        StrFile = Dir
    Loop
End Sub

When I first modified this code, it worked perfectly, and would open my files for me.

So I decided to do a bit of house keeping, and cleaned up the code a bit, removed extra spaces etc, however now when I run this exact same code, it now tells me:

Runtime error "1004"
Cannot find file "Excelfile.xls"

I've not moved any files, I've not changed any paths, I've not renamed any files. Everything is exactly the same ... and yet it cannot find the files. I've also tested hard coding the path, with the same error.

One thing I noticed that was interesting, is even if I go back to the bare bones code:

Sub LoopThroughFiles()
    Dim StrFile As String
    StrFile = Dir("C:\Users\Jeff\Downloads\IO\*.xls")
    Do While Len(StrFile) > 0
        Debug.Print StrFile
        StrFile = Dir
    Loop
End Sub

and simply add a Debug.Print Dir, right after the Debug.Print StrFile. It seems that the StrFile and Dir are reading the wrong file at any given time.

First print out is "file1.xls" and the second print out is "file2.xls"

Just not sure if this has anything to do with it.

Hmmm to open files you need both the path & filename, `Dir()` only gives you back the filename so you need to add the path. — Damian, May 08 '19 at 16:11
Possible duplicate of [Excel VBA using Workbook.Open with results of Dir(Directory)](https://stackoverflow.com/questions/28461614/excel-vba-using-workbook-open-with-results-of-dirdirectory) — BigBen, May 08 '19 at 16:12
@level42 inded, but no on the `workbooks.open()` you are seeing that debugging the `StrFile` only give you the name, to use `workbooks.open()`you need the full path, no only the name of the file. — Damian, May 08 '19 at 16:14
Thanks @Damian and BigBen. This seems to have resolved my issue! — level42, May 08 '19 at 16:21

score 1 · Answer 1 · answered May 08 '19 at 16:16

The filename returned by Dir does not include the source folder. Unless you can reliably use the default folder as the source folder then you need to concatenate SourceFolder onto the StrFile or risk looking in the wrong folder.

Sub LoopThroughFiles()

    Dim SourceFolder As String
    SourceFolder = "C:\Users\Jeff\Downloads\IO\"

    Dim StrFile As String
    StrFile = Dir(SourceFolder & "*.xls")

    Dim wb As Workbook

    Do While Len(StrFile) > 0
        Debug.Print StrFile
        Set wb = Workbooks.Open(Filename:=SourceFolder & StrFile)
        StrFile = Dir
    Loop

End Sub

Loop Through Files - File not found

1 Answers1