Get names of all workbooks in a directory

[Graham Whitehead]

I have done this previously but I am unable to remember how I did it.
Basically I have a load of excel workbooks in one directory and I want to
create a loop to get the names of each workbook so that I can store them and
use them all later on. If anyone can help with this one I would be very
gratefull. Thanks in advance.

 

[Bob Phillips]

One way


Dim oFSO

Sub LoopFolders()

Set oFSO = CreateObject("Scripting.FileSystemObject")

selectFiles "c:\MyTest"

Set oFSO = Nothing

End Sub


'---------------------------------------------------------------------------
Sub selectFiles(sPath)
'---------------------------------------------------------------------------
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim fldr

Set Folder = oFSO.GetFolder(sPath)

For Each fldr In Folder.Subfolders
selectFiles fldr.Path
Next fldr

For Each file In Folder.Files
If file.Type = "Microsoft Excel Worksheet" Then
Debug.print file.name
End If
Next file

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)