Word VBA Code to Traverse Files in a Folder Alphabetically

I have a macro that traverses two folder structures, checks for Word documents within those folder structures and outputs the results to a text file. These output files are then referenced in customized tabs within Word.

Unfortunately, these folders are not showing up within Word as "sorted alphabetically". I've verified that Windows explorer is seeing these folders and files sorted alphabetically, but is there a way to force the VBA code to see or sort the folders in alpha order?

I have attached some segments of code along with an example of the output and end result. Thanks for the help!!
LVL 13
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


you could use a collection to sort it

Sub gg()
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objBaseTemplateFolder = _
        objFSO.getfolder("C:\Task Lists") '
    Set cFldr = New Collection
    For Each folder In objBaseTemplateFolder.Subfolders
        cFldr.Add folder.Name, folder.Name
    For i = 1 To cFldr.Count - 1
        For J = i + 1 To cFldr.Count
            If UCase(cFldr(i)) > UCase(cFldr(J)) Then
               vTemp = cFldr(J)
               cFldr.Remove J
               cFldr.Add vTemp, vTemp, i
            End If
        Next J
    Next i

    'Test it
    For Each Itm In cFldr
        Debug.Print Itm
    Next Itm
End Sub

Open in new window

You could issue a DIR command and capture the result.
Sub Q_28638315()
    Dim oWsh As Object
    Dim oFS As Object, oTS As Object
    Dim strFileList As String
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
    Set oWsh = CreateObject("wscript.shell")
    oWsh.Run "cmd /c dir /b /s C:\Task Lists\*.docx > C:\Task Lists\dirlist.txt", 0, True

    Set oFS = CreateObject("Scripting.FileSystemObject")
    Set oTS = oFS.OpenTextFile("C:\Task Lists\dirlist.txt", ForReading, True, TristateFalse)
    strFileList = oTS.ReadAll
    'parse strFileList lines and create XML
End Sub

Open in new window

Alternatively, you could loop through the dirlist.txt file in a line-by-line manner (oTS.ReadLine).

Alternatively, you might consider using a non-compact version of the DIR command, since that resembles your menu structure.  It might simplify your code.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IT_CrowdAuthor Commented:
Thanks for the ideas.  I'm really like the dir command idea right now, I think it will be pretty easy to work with, and I think my code could use a rework anyways.
IT_CrowdAuthor Commented:
I'll use the DIR command in VBA to get the results I want.  Thanks!
If you omit the /b flag from the DIR command, you can use the following regexp patterns to parse the results.  The first one gives you directory matches and the second one parses the docx/docm file names within each match.
Directory of ([^\r]*)\s+((?:.|\n)*?)\d bytes
\d.{36}\d (.*?\.doc[xm])

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.