Excel VBA Dir Method

Question

I have used a form of the following code successfully many times to open multiple files in a folder and I understand how dir helps set the file path before the while loop, but don't quite understand how call the dir method again with no arguments right before the end of the while loop looks up the next file in the folder:

Sub OpenFiles()

Dim MyFolder As String
Dim MyFile As String

MyFolder = "C:\newFolder"
MyFile = Dir(MyFolder & "\*.xlsx")

Do While MyFile <> ""
Workbooks.Open Filename:=MyFolder & "\" & MyFile

MyFile = Dir

Loop
End Sub

When calling Dir on a string that already has been set with a value, is Dir designed to look to the next file in the list of files at that same path?

the MyFile = Dir line is used to get the next entry (i.e. your next file). See this: exceltrick.com/formulas_macros/vba-dir-function — Alex
– Alex, Commented Jan 29, 2015 at 20:21
Thanks Alex, good article. I like that it starts it off by noting that Dir is a "very special" function, and it seems to me it was specifically designed anticipating the need to iterate through multiple files in a location and hence designed to be called with no argument later. — Declan
– Declan, Commented Jan 29, 2015 at 20:36

Uri Goren · Accepted Answer · 2015-01-29 20:20:46Z

3

The Dir functions declares a Static variables and uses them for later invocations.

I would guess that the logic is:

Declare index_of_file and number_of_files as static integers
If a parameter is supplied, scan the directory set index_of_file to 1, set number_of_files to the number of files in the folder, and return the first file.
If a parameter is not supplied, increment index_of_file and return the file with that index, or return "" if index_of_file>number_of_files

answered Jan 29, 2015 at 20:20

Uri Goren

13.8k8 gold badges62 silver badges113 bronze badges

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

Collectives™ on Stack Overflow

Excel VBA Dir Method

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related