初学VBA(二)文件夹的遍历

Sub FindDir(ByVal dirname As String, ByRef xmlFile As Variant)
'use two dictionary
    Dim MyName, Dic, Did, i, F, MyFileName
    Dim fso, objFolder
    
       'On Error Resume Next
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set objFolder = fso.GetFolder(dirname)
    If Not objFolder Is Nothing Then lj = objFolder.Path & "\"

    Set Dic = CreateObject("Scripting.Dictionary")    'create a dictionary object
    Set Did = CreateObject("Scripting.Dictionary")
    Dic.Add (lj), ""
    i = 0
    Do While i < Dic.Count
        Ke = Dic.keys   'start to Traversal Dic
        MyName = dir(Ke(i), vbDirectory)    'find directory
        Do While MyName <> ""
            If MyName <> "." And MyName <> ".." Then
                If (GetAttr(Ke(i) & MyName) And vbDirectory) = vbDirectory Then    'if it's directory attr
                    Dic.Add (Ke(i) & MyName & "\"), ""  'add a new key to dic
                End If
            End If
            MyName = dir    'Traversal go on
        Loop
        i = i + 1
    Loop
    'Did.Add ("File List"), ""    'find all xml files of them
    For Each Ke In Dic.keys
        MyFileName = dir(Ke & "*.xml")
        Do While MyFileName <> ""
            Did.Add (Ke & MyFileName), ""
            MyFileName = dir
        Loop
    Next

    xmlFile = Application.Transpose(Did.keys)

    'Sheets("xml List").[A1].Resize(Did.Count, 1) = WorksheetFunction.Transpose(Did.keys)

End Sub

在文件夹的遍历中，学到一些VBA的知识点，罗列在以下。

1.函数形参(byval)和实参(byref)的区别

http://blog.sina.com.cn/s/blog_8654c87a0100xrb2.html

就个人使用习惯而言，byval相当于入力参数，byref既可以做入力参数也可做出力参数。

2.字典用法

http://blog.chinaunix.net/uid-24765042-id-3207729.html

3.与操作

1)文字列的拼接

    If Not objFolder Is Nothing Then lj = objFolder.Path & "\"

2)条件式

下面一段代码的意思是，Dic这个字典的Keys是文件夹，在循环keys时寻找当前文件夹下是否有xml文件。

    For Each Ke In Dic.keys
        MyFileName = dir(Ke & "*.xml")
        Do While MyFileName <> ""
            Did.Add (Ke & MyFileName), ""
            MyFileName = dir
        Loop
    Next