CODEWITHSUNDEEP
vbaexcel
royce1ma
royce1ma

Reputation: 13

Can someone explain how a While Loop using the Dir function works in VBA?

I'm fairly new to VBA and needed to make a script for work. The script would look in a directory and import any .txt files found into a new worksheet in the workbook. I.e. if there were 20 .txt files, I would end up with 20 worksheets. I found some code online that does this perfectly, exactly as I want it to. The problem is, I don't really understand how it works. I've never used the Dir function, and it appears the loop focuses on this, but I'm still confused as to how this is looping from one file to the next. If someone could help me understand, or add comments to the code, that would be really helpful. Thanks.

Code is:

    Sub LoadFiles()
Dim idx As Integer
Dim fpath As String
Dim fname As String
Dim ws As Worksheet
idx = 0
fpath = "C:\MyFolderLocation"
fname = Dir(fpath)
While (Len(fname) > 0)
    idx = idx + 1
    Sheets.Add.Name = fname
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _
      & fpath & fname, Destination:=Range("A1"))
        .Name = "a" & idx
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = ","
        .TextFileColumnDataTypes = Array(1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
        fname = Dir
    End With
Wend

End Sub

Upvotes: 1

Views: 1463

Answers (1)

tiesja
tiesja

Reputation: 66

The Dir function returns, one at the time, filenames from a directory.

When you call Dir() with a path to a directory as an argument, it will return to you the first filename from that directory.

When you call Dir() without an argument, it will return to you the next filename from the directory until there are no more files, in which case a null name is returned.

So in order to process all the files in directory you need Dir(path) once, followed by Dir() as long as that returns a name.

How do you test if a name is returned?
fname>""
is a way, here they use
len(fname)>0
where len is another function that takes a string as argument and returns the length of that string.

So comes the "as long as there are files" part. That is implemented with a loop structure

while *condition*  
    ... do things  
end while

in this case

While (Len(fname) > 0)
    '...
    fname=Dir
Wend

Upvotes: 5

Related Questions