Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User selection of folder and open all .xls files within folder
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User selection of folder and open all .xls files within folder
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 --- Regards, Norman |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
User selection of folder and open all .xls files within folder
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) "Barb Reinhardt" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
User selection of folder and open all .xls files within folder
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 wrote: 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) "Barb Reinhardt" wrote in message ... 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
User selection of folder and open all .xls files within folder
I did indeed. my cut failed me.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Dave Peterson" wrote in message ... 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 wrote: 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) "Barb Reinhardt" wrote in message ... 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pulling pdf files from general folder to specific folder | Excel Discussion (Misc queries) | |||
How to List the names of the subfolders present in the folder (path of folder is given in the textbox by user ) | Excel Programming | |||
how can I specific a folder with wildcard criteria and excel will import all the correct files in that folder? | Excel Discussion (Misc queries) | |||
how can I specific a folder with wildcard criteria and excel will import all the correct files in that folder? | Excel Programming | |||
Open all files in a folder | Excel Programming |