| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get Folder Name
I can use the file open dialog to select a file:
Sub WhichOne() MsgBox (Application.GetOpenFilename) End Sub How can I select a folder?? -- Gary''s Student - gsnu200780 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Get Folder Name
Jim Rech has a BrowseForFolder routine at:
http://www.oaltd.co.uk/MVP/Default.htm (look for BrowseForFolder) John Walkenbach has one at: http://j-walk.com/ss/excel/tips/tip29.htm If you and all your users are running xl2002+, take a look at VBA's help for: application.filedialog(msoFileDialogFolderPicker) Gary''s Student wrote: I can use the file open dialog to select a file: Sub WhichOne() MsgBox (Application.GetOpenFilename) End Sub How can I select a folder?? -- Gary''s Student - gsnu200780 -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Get Folder Name
Thanks Dave.
-- Gary''s Student - gsnu200780 "Dave Peterson" wrote: Jim Rech has a BrowseForFolder routine at: http://www.oaltd.co.uk/MVP/Default.htm (look for BrowseForFolder) John Walkenbach has one at: http://j-walk.com/ss/excel/tips/tip29.htm If you and all your users are running xl2002+, take a look at VBA's help for: application.filedialog(msoFileDialogFolderPicker) Gary''s Student wrote: I can use the file open dialog to select a file: Sub WhichOne() MsgBox (Application.GetOpenFilename) End Sub How can I select a folder?? -- Gary''s Student - gsnu200780 -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Get Folder Name
If your users are pre-xl2002 and you don't want to go the Windows API route,
here is one more option. _______________________________ Sub FetchAfolderpath() Const MY_COMPUTER = &H11& Const WINDOW_HANDLE = 0 Const OPTIONS = 0 Set objShell = CreateObject("Shell.Application") Set objFolder = objShell.Namespace(MY_COMPUTER) Set objFolderItem = objFolder.Self strPath = objFolderItem.Path Set objShell = CreateObject("Shell.Application") Set objFolder = objShell.BrowseForFolder _ (WINDOW_HANDLE, "Select a folder:", OPTIONS, strPath) If objFolder Is Nothing Then Exit Sub End If Set objFolderItem = objFolder.Self objPath = objFolderItem.Path MsgBox objPath End Sub ______________________________ Steve Yandl "Gary''s Student" wrote in message ... Thanks Dave. -- Gary''s Student - gsnu200780 "Dave Peterson" wrote: Jim Rech has a BrowseForFolder routine at: http://www.oaltd.co.uk/MVP/Default.htm (look for BrowseForFolder) John Walkenbach has one at: http://j-walk.com/ss/excel/tips/tip29.htm If you and all your users are running xl2002+, take a look at VBA's help for: application.filedialog(msoFileDialogFolderPicker) Gary''s Student wrote: I can use the file open dialog to select a file: Sub WhichOne() MsgBox (Application.GetOpenFilename) End Sub How can I select a folder?? -- Gary''s Student - gsnu200780 -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Get Folder Name
Gary's Student,
My copy and paste was a bit too quick. If you test the routine above, you can drop the duplicate line (the second time it appears) Set objShell = CreateObject("Shell.Application") Also, I pulled this from a vbs file of mine. In a script, it isn't important to set the objects to nothing at the end of the sub but in VBA you should include a line at the end of the sub that reads: Set objShell = Nothing Steve "Steve Yandl" wrote in message . .. If your users are pre-xl2002 and you don't want to go the Windows API route, here is one more option. _______________________________ Sub FetchAfolderpath() Const MY_COMPUTER = &H11& Const WINDOW_HANDLE = 0 Const OPTIONS = 0 Set objShell = CreateObject("Shell.Application") Set objFolder = objShell.Namespace(MY_COMPUTER) Set objFolderItem = objFolder.Self strPath = objFolderItem.Path Set objShell = CreateObject("Shell.Application") Set objFolder = objShell.BrowseForFolder _ (WINDOW_HANDLE, "Select a folder:", OPTIONS, strPath) If objFolder Is Nothing Then Exit Sub End If Set objFolderItem = objFolder.Self objPath = objFolderItem.Path MsgBox objPath End Sub ______________________________ Steve Yandl "Gary''s Student" wrote in message ... Thanks Dave. -- Gary''s Student - gsnu200780 "Dave Peterson" wrote: Jim Rech has a BrowseForFolder routine at: http://www.oaltd.co.uk/MVP/Default.htm (look for BrowseForFolder) John Walkenbach has one at: http://j-walk.com/ss/excel/tips/tip29.htm If you and all your users are running xl2002+, take a look at VBA's help for: application.filedialog(msoFileDialogFolderPicker) Gary''s Student wrote: I can use the file open dialog to select a file: Sub WhichOne() MsgBox (Application.GetOpenFilename) End Sub How can I select a folder?? -- Gary''s Student - gsnu200780 -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Get Folder Name
Thank you very much Steve!!
-- Gary''s Student - gsnu200780 "Steve Yandl" wrote: Gary's Student, My copy and paste was a bit too quick. If you test the routine above, you can drop the duplicate line (the second time it appears) Set objShell = CreateObject("Shell.Application") Also, I pulled this from a vbs file of mine. In a script, it isn't important to set the objects to nothing at the end of the sub but in VBA you should include a line at the end of the sub that reads: Set objShell = Nothing Steve "Steve Yandl" wrote in message . .. If your users are pre-xl2002 and you don't want to go the Windows API route, here is one more option. _______________________________ Sub FetchAfolderpath() Const MY_COMPUTER = &H11& Const WINDOW_HANDLE = 0 Const OPTIONS = 0 Set objShell = CreateObject("Shell.Application") Set objFolder = objShell.Namespace(MY_COMPUTER) Set objFolderItem = objFolder.Self strPath = objFolderItem.Path Set objShell = CreateObject("Shell.Application") Set objFolder = objShell.BrowseForFolder _ (WINDOW_HANDLE, "Select a folder:", OPTIONS, strPath) If objFolder Is Nothing Then Exit Sub End If Set objFolderItem = objFolder.Self objPath = objFolderItem.Path MsgBox objPath End Sub ______________________________ Steve Yandl "Gary''s Student" wrote in message ... Thanks Dave. -- Gary''s Student - gsnu200780 "Dave Peterson" wrote: Jim Rech has a BrowseForFolder routine at: http://www.oaltd.co.uk/MVP/Default.htm (look for BrowseForFolder) John Walkenbach has one at: http://j-walk.com/ss/excel/tips/tip29.htm If you and all your users are running xl2002+, take a look at VBA's help for: application.filedialog(msoFileDialogFolderPicker) Gary''s Student wrote: I can use the file open dialog to select a file: Sub WhichOne() MsgBox (Application.GetOpenFilename) End Sub How can I select a folder?? -- Gary''s Student - gsnu200780 -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Get Folder Name
You're welcome.
If you want to use a top level folder other that the MyComputer special folder, this reference will provide the available constants. http://www.microsoft.com/technet/scr....mspx?mfr=true Steve "Gary''s Student" wrote in message ... Thank you very much Steve!! -- Gary''s Student - gsnu200780 "Steve Yandl" wrote: Gary's Student, My copy and paste was a bit too quick. If you test the routine above, you can drop the duplicate line (the second time it appears) Set objShell = CreateObject("Shell.Application") Also, I pulled this from a vbs file of mine. In a script, it isn't important to set the objects to nothing at the end of the sub but in VBA you should include a line at the end of the sub that reads: Set objShell = Nothing Steve "Steve Yandl" wrote in message . .. If your users are pre-xl2002 and you don't want to go the Windows API route, here is one more option. _______________________________ Sub FetchAfolderpath() Const MY_COMPUTER = &H11& Const WINDOW_HANDLE = 0 Const OPTIONS = 0 Set objShell = CreateObject("Shell.Application") Set objFolder = objShell.Namespace(MY_COMPUTER) Set objFolderItem = objFolder.Self strPath = objFolderItem.Path Set objShell = CreateObject("Shell.Application") Set objFolder = objShell.BrowseForFolder _ (WINDOW_HANDLE, "Select a folder:", OPTIONS, strPath) If objFolder Is Nothing Then Exit Sub End If Set objFolderItem = objFolder.Self objPath = objFolderItem.Path MsgBox objPath End Sub ______________________________ Steve Yandl "Gary''s Student" wrote in message ... Thanks Dave. -- Gary''s Student - gsnu200780 "Dave Peterson" wrote: Jim Rech has a BrowseForFolder routine at: http://www.oaltd.co.uk/MVP/Default.htm (look for BrowseForFolder) John Walkenbach has one at: http://j-walk.com/ss/excel/tips/tip29.htm If you and all your users are running xl2002+, take a look at VBA's help for: application.filedialog(msoFileDialogFolderPicker) Gary''s Student wrote: I can use the file open dialog to select a file: Sub WhichOne() MsgBox (Application.GetOpenFilename) End Sub How can I select a folder?? -- Gary''s Student - gsnu200780 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to List the names of the subfolders present in the folder (path of folder is given in the textbox by user ) | Excel Programming | |||
| Save file in a new folder, but create folder only if folder doesn't already exist? | Excel Programming | |||
| How to decide folder-depth or How to select more folders/subfolders (folder-tree) ? | 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 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 | |||
Linear Mode
