User selection of folder and open all .xls files within folder

[Guest]

I need to allow the user to select a folder so that .xls files meeting a
specific file naming convention can be opened. I know how to create an
array of workbooks based upon that naming convention, but I've not been
successful in allowing the user to select a folder. How should I code this?

Thanks,
Barb Reinhardt

 

[Norman Jones]

Hi Barb,

'-----------------
I need to allow the user to select a folder so that .xls files meeting a
specific file naming convention can be opened. I know how to create an
array of workbooks based upon that naming convention, but I've not been
successful in allowing the user to select a folder. How should I code
this?
'-----------------

See John Walkenbach at:

Selecting a Directory
http://j-walk.com/ss/excel/tips/tip29.htm

See also Jim Rech's BrowseForFolder download which
may be freely downloaded from Stephen Bullen's
OfficeAutomation site:

http://www.bmsltd.ie/MVP/Default.htm

Also visit Chip Pearson at:

Selecting A Folder With VBA
http://www.cpearson.com/excel/BrowseFolder.htm

 

[Bob Phillips]

Here is one simple way


Sub LoopFolders()

Set oFSO = CreateObject("Scripting.FileSystemObject")

With Application.FileDialog(msoFileDialogFolderPicker)
.Show

selectFiles .SelectedItems(1)

End With

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
Workbooks.Open Filename:=file.Path
End If
Next file

End Sub



--
HTH

Bob

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

 

[Dave Peterson]

Just an addendum to Bob's reply:

Application.FileDialog was added in xl2002 (IIRC).

And I think Bob wanted to declare oFSO at the top of the module.

 

[Bob Phillips]

I did indeed. my cut failed me.

--
HTH

Bob

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