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

B

Barb Reinhardt

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
 
N

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
 
B

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)
 
D

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.
 
B

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top