0

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
Community
  • 1
  • 1
Rivers31334
  • 644
  • 1
  • 12
  • 30
  • Show more of your code, with the Dir. – Gordon Bell Mar 02 '17 at 16:10
  • @GordonBell I have updated the post. – Rivers31334 Mar 02 '17 at 16:12
  • 1
    Add `strFileName = Dir`(without parameter) before `Loop` to get the next file name – FunThomas Mar 02 '17 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 Mar 02 '17 at 16:16
  • [Scripting.FileSystemObject](http://stackoverflow.com/documentation/vba/990/scripting-filesystemobject) on Documentation.SO should have everything you need to know. To avoid downvotes on SO, it's usually a good idea to *research* the topic a little bit before asking. – Mathieu Guindon Mar 02 '17 at 16:36
  • @Mat'sMug Glad to see you changed your last comment... – Rivers31334 Mar 02 '17 at 16:37
  • Sorry to be blunt, but my last comment (which was apparently flagged and removed) was exactly what I meant: "vba dir function" turns up [MSDN](https://msdn.microsoft.com/en-us/library/dk008ty4(v=vs.90).aspx) on the first google results page, and it's clearly stated: "You must supply a PathName the first time you call the Dir function. **To retrieve the next item, you can make subsequent calls to the Dir function with no parameters**" - downvote button says "this question does not show any research effort / is not useful", so I downvoted it. Try a bit harder next time. – Mathieu Guindon Mar 02 '17 at 16:45
  • 1
    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 Mar 02 '17 at 16:50

1 Answers1

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.

Sunil Kumar
  • 759
  • 7
  • 17