Loop through Files in Folder Using Dir [duplicate]

Question

I am attempting to loop through files using Dir. I have written the following, but as expected, the loop is endless as it opens and closes the first file in the folder.

I am wondering if there is a better way than the wildcard method, but can anyone offer me a solution to this.

strFolder = "D:\Name\Project\Data\Back-End\Previous\"
strFileSpec = strFolder & "*.xlsx" 
strFileName = Dir(strFileSpec)

Do While Len(strFileName) > 0
    Dim strFilePath As String: strFilePath = strFolder & strFileName
    Set proxy_wbk = Workbooks.Open(strFilePath)
    Set proxySheet = proxy_wbk.Sheets(1)
    siteName = proxySheet.Cells(1, 1).Value

    'do work'

    Workbooks(siteName & "_Jan_2017.xlsx").Close savechanges:=False
Loop

Add strFileName = Dir(without parameter) before Loop to get the next file name — FunThomas
– FunThomas, Commented Mar 2, 2017 at 16:14
Thank you. This worked. Can you explain why setting strFileName = Dir moves it forward. I don't think I quite follow it. Regardless, I appreciate the help. — Rivers31334
– Rivers31334, Commented Mar 2, 2017 at 16:16
Dir calls an underlying Windows function that other apps running at the same time may also call; that can reset the directory and file spec, so your subsequent calls to Dir may return unexpected results. If that's a concern, it's safer to load an array or collection with files that match your spec, then process them afterward by pulling the filenames from the array rather than repeatedly calling Dir. You could also add another layer of foolproofing by checking each array entry to make sure the path matches the one you expect prior to acting on it. — Steve Rindsberg
– Steve Rindsberg, Commented Mar 2, 2017 at 16:50

Sunil Kumar · Accepted Answer · 2017-03-02 16:50:23Z

1

Try This Code

files = Dir("/yourpath/*.xlsx")
While files <> ""
    List(i)=files //List(i) will hold the file name 
    files = Dir
Wend

Here Dir function returns the file name one by one every time it is called after all the file names are emptied It returns empty string.

To understand better assume Dir function has static variable in it to hold the filename index in it.

edited Mar 2, 2017 at 16:50

answered Mar 2, 2017 at 16:27

Sunil Kumar

8698 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dr Rob Lang Over a year ago

Please can you add some explanation around the code you've provided? Thank you.

Sunil Kumar Over a year ago

I have added explanation. Is that clear now?

Collectives™ on Stack Overflow

Loop through Files in Folder Using Dir [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related