Changing to a selected folder
 

Is there method of openning File Browser-like dialog, allowing the user to
move up and down the folder tree from the current folder, select a drive at
the top of the tree, move down the folder tree of the selected drive to a
desired folder, and then select that folder. The dialog needs to a) return
the full path to that selected directory (whence one could use ChDir to make
it the defulat directory), or b) change the default directory to that
selected directory?

This is an Explorer-like function but I do not see how to do this within
Excel VBA, even using the FileSystemObjecrt or any of the accessible Excel
Dialogs. Dialogs(xlDialogOpen).Show comes the closest but requires
selecting a Excel file and openning it.


I want to write a VBA procedure that will list all the directories in a tree
in an Excel worksheet, starting from a selected directory or the root
directory of a select drive.

How can I do this?

There are Excel-Addins that list files but I have found none that allow
restricting the listing to directories only.

John

Changing to a selected folder
 

John

This may get you started

Sub test()
With Application.FileDialog(msoFileDialogFolderPicker)
..Show
If .SelectedItems.Count 0 Then MsgBox .SelectedItems(1)
End With
End Sub

--
XL2002
Regards

William



"John Wirt" wrote in message
...
| Is there method of openning File Browser-like dialog, allowing the user to
| move up and down the folder tree from the current folder, select a drive
at
| the top of the tree, move down the folder tree of the selected drive to a
| desired folder, and then select that folder. The dialog needs to a) return
| the full path to that selected directory (whence one could use ChDir to
make
| it the defulat directory), or b) change the default directory to that
| selected directory?
|
| This is an Explorer-like function but I do not see how to do this within
| Excel VBA, even using the FileSystemObjecrt or any of the accessible Excel
| Dialogs. Dialogs(xlDialogOpen).Show comes the closest but requires
| selecting a Excel file and openning it.
|
|
| I want to write a VBA procedure that will list all the directories in a
tree
| in an Excel worksheet, starting from a selected directory or the root
| directory of a select drive.
|
| How can I do this?
|
| There are Excel-Addins that list files but I have found none that allow
| restricting the listing to directories only.
|
| John
|
|

Changing to a selected folder
 

William,

Thank you for this but when I simply paste this code into a Code module, I
get,

Error 438: Object doesn't support this property or method.

Do I need to specify the Application somehow?

John


"William" wrote in message
...
John

This may get you started

Sub test()
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
If .SelectedItems.Count 0 Then MsgBox .SelectedItems(1)
End With
End Sub

--
XL2002
Regards

William



"John Wirt" wrote in message
...
| Is there method of openning File Browser-like dialog, allowing the user
to
| move up and down the folder tree from the current folder, select a drive
at
| the top of the tree, move down the folder tree of the selected drive to
a
| desired folder, and then select that folder. The dialog needs to a)
return
| the full path to that selected directory (whence one could use ChDir to
make
| it the defulat directory), or b) change the default directory to that
| selected directory?
|
| This is an Explorer-like function but I do not see how to do this within
| Excel VBA, even using the FileSystemObjecrt or any of the accessible
Excel
| Dialogs. Dialogs(xlDialogOpen).Show comes the closest but requires
| selecting a Excel file and openning it.
|
|
| I want to write a VBA procedure that will list all the directories in a
tree
| in an Excel worksheet, starting from a selected directory or the root
| directory of a select drive.
|
| How can I do this?
|
| There are Excel-Addins that list files but I have found none that allow
| restricting the listing to directories only.
|
| John
|
|

Changing to a selected folder
 

See John Walkenbach's sample code for this:

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

--
Regards,
Tom Ogilvy

"John Wirt" wrote in message
...
Is there method of openning File Browser-like dialog, allowing the user to
move up and down the folder tree from the current folder, select a drive
at
the top of the tree, move down the folder tree of the selected drive to a
desired folder, and then select that folder. The dialog needs to a) return
the full path to that selected directory (whence one could use ChDir to
make
it the defulat directory), or b) change the default directory to that
selected directory?

This is an Explorer-like function but I do not see how to do this within
Excel VBA, even using the FileSystemObjecrt or any of the accessible Excel
Dialogs. Dialogs(xlDialogOpen).Show comes the closest but requires
selecting a Excel file and openning it.


I want to write a VBA procedure that will list all the directories in a
tree
in an Excel worksheet, starting from a selected directory or the root
directory of a select drive.

How can I do this?

There are Excel-Addins that list files but I have found none that allow
restricting the listing to directories only.

John

Changing to a selected folder
 

Hi John

I retested the code and it appears to run fine - at least in XL2002 it does.


--
XL2002
Regards

William



"John Wirt" wrote in message
...
| William,
|
| Thank you for this but when I simply paste this code into a Code module, I
| get,
|
| Error 438: Object doesn't support this property or method.
|
| Do I need to specify the Application somehow?
|
| John
|
|
| "William" wrote in message
| ...
| John
|
| This may get you started
|
| Sub test()
| With Application.FileDialog(msoFileDialogFolderPicker)
| .Show
| If .SelectedItems.Count 0 Then MsgBox .SelectedItems(1)
| End With
| End Sub
|
| --
| XL2002
| Regards
|
| William
|
|
|
| "John Wirt" wrote in message
| ...
| | Is there method of openning File Browser-like dialog, allowing the
user
| to
| | move up and down the folder tree from the current folder, select a
drive
| at
| | the top of the tree, move down the folder tree of the selected drive
to
| a
| | desired folder, and then select that folder. The dialog needs to a)
| return
| | the full path to that selected directory (whence one could use ChDir
to
| make
| | it the defulat directory), or b) change the default directory to that
| | selected directory?
| |
| | This is an Explorer-like function but I do not see how to do this
within
| | Excel VBA, even using the FileSystemObjecrt or any of the accessible
| Excel
| | Dialogs. Dialogs(xlDialogOpen).Show comes the closest but requires
| | selecting a Excel file and openning it.
| |
| |
| | I want to write a VBA procedure that will list all the directories in
a
| tree
| | in an Excel worksheet, starting from a selected directory or the root
| | directory of a select drive.
| |
| | How can I do this?
| |
| | There are Excel-Addins that list files but I have found none that
allow
| | restricting the listing to directories only.
| |
| | John
| |
| |
|
|
|
|

Changing to a selected folder
 

Office XP is where that object was introduced as I recall. Good chance John
isn't using xl2002 or xl2003. I get the same error in xl2000.

--
Regards,
Tom Ogilvy

"William" wrote in message
...
Hi John

I retested the code and it appears to run fine - at least in XL2002 it
does.


--
XL2002
Regards

William



"John Wirt" wrote in message
...
| William,
|
| Thank you for this but when I simply paste this code into a Code module,
I
| get,
|
| Error 438: Object doesn't support this property or method.
|
| Do I need to specify the Application somehow?
|
| John
|
|
| "William" wrote in message
| ...
| John
|
| This may get you started
|
| Sub test()
| With Application.FileDialog(msoFileDialogFolderPicker)
| .Show
| If .SelectedItems.Count 0 Then MsgBox .SelectedItems(1)
| End With
| End Sub
|
| --
| XL2002
| Regards
|
| William
|
|
|
| "John Wirt" wrote in message
| ...
| | Is there method of openning File Browser-like dialog, allowing the
user
| to
| | move up and down the folder tree from the current folder, select a
drive
| at
| | the top of the tree, move down the folder tree of the selected drive
to
| a
| | desired folder, and then select that folder. The dialog needs to a)
| return
| | the full path to that selected directory (whence one could use ChDir
to
| make
| | it the defulat directory), or b) change the default directory to
that
| | selected directory?
| |
| | This is an Explorer-like function but I do not see how to do this
within
| | Excel VBA, even using the FileSystemObjecrt or any of the accessible
| Excel
| | Dialogs. Dialogs(xlDialogOpen).Show comes the closest but requires
| | selecting a Excel file and openning it.
| |
| |
| | I want to write a VBA procedure that will list all the directories
in
a
| tree
| | in an Excel worksheet, starting from a selected directory or the
root
| | directory of a select drive.
| |
| | How can I do this?
| |
| | There are Excel-Addins that list files but I have found none that
allow
| | restricting the listing to directories only.
| |
| | John
| |
| |
|
|
|
|

Changing to a selected folder
 

Thankyou Tom

No doubt you are right.

--
XL2002
Regards

William



"Tom Ogilvy" wrote in message
...
| Office XP is where that object was introduced as I recall. Good chance
John
| isn't using xl2002 or xl2003. I get the same error in xl2000.
|
| --
| Regards,
| Tom Ogilvy
|
| "William" wrote in message
| ...
| Hi John
|
| I retested the code and it appears to run fine - at least in XL2002 it
| does.
|
|
| --
| XL2002
| Regards
|
| William
|
|
|
| "John Wirt" wrote in message
| ...
| | William,
| |
| | Thank you for this but when I simply paste this code into a Code
module,
| I
| | get,
| |
| | Error 438: Object doesn't support this property or method.
| |
| | Do I need to specify the Application somehow?
| |
| | John
| |
| |
| | "William" wrote in message
| | ...
| | John
| |
| | This may get you started
| |
| | Sub test()
| | With Application.FileDialog(msoFileDialogFolderPicker)
| | .Show
| | If .SelectedItems.Count 0 Then MsgBox .SelectedItems(1)
| | End With
| | End Sub
| |
| | --
| | XL2002
| | Regards
| |
| | William
| |
| |
| |
| | "John Wirt" wrote in message
| | ...
| | | Is there method of openning File Browser-like dialog, allowing the
| user
| | to
| | | move up and down the folder tree from the current folder, select a
| drive
| | at
| | | the top of the tree, move down the folder tree of the selected
drive
| to
| | a
| | | desired folder, and then select that folder. The dialog needs to
a)
| | return
| | | the full path to that selected directory (whence one could use
ChDir
| to
| | make
| | | it the defulat directory), or b) change the default directory to
| that
| | | selected directory?
| | |
| | | This is an Explorer-like function but I do not see how to do this
| within
| | | Excel VBA, even using the FileSystemObjecrt or any of the
accessible
| | Excel
| | | Dialogs. Dialogs(xlDialogOpen).Show comes the closest but
requires
| | | selecting a Excel file and openning it.
| | |
| | |
| | | I want to write a VBA procedure that will list all the directories
| in
| a
| | tree
| | | in an Excel worksheet, starting from a selected directory or the
| root
| | | directory of a select drive.
| | |
| | | How can I do this?
| | |
| | | There are Excel-Addins that list files but I have found none that
| allow
| | | restricting the listing to directories only.
| | |
| | | John
| | |
| | |
| |
| |
| |
| |
|
|
|
|

Changing to a selected folder
 

Apparently. Is there no .DLL in Office 2000 that I can reference?

John

"William" wrote in message
...
Thankyou Tom

No doubt you are right.

--
XL2002
Regards

William



"Tom Ogilvy" wrote in message
...
| Office XP is where that object was introduced as I recall. Good chance
John
| isn't using xl2002 or xl2003. I get the same error in xl2000.
|
| --
| Regards,
| Tom Ogilvy
|
| "William" wrote in message
| ...
| Hi John
|
| I retested the code and it appears to run fine - at least in XL2002 it
| does.
|
|
| --
| XL2002
| Regards
|
| William
|
|
|
| "John Wirt" wrote in message
| ...
| | William,
| |
| | Thank you for this but when I simply paste this code into a Code
module,
| I
| | get,
| |
| | Error 438: Object doesn't support this property or method.
| |
| | Do I need to specify the Application somehow?
| |
| | John
| |
| |
| | "William" wrote in message
| | ...
| | John
| |
| | This may get you started
| |
| | Sub test()
| | With Application.FileDialog(msoFileDialogFolderPicker)
| | .Show
| | If .SelectedItems.Count 0 Then MsgBox .SelectedItems(1)
| | End With
| | End Sub
| |
| | --
| | XL2002
| | Regards
| |
| | William
| |
| |
| |
| | "John Wirt" wrote in message
| | ...
| | | Is there method of openning File Browser-like dialog, allowing
the
| user
| | to
| | | move up and down the folder tree from the current folder, select
a
| drive
| | at
| | | the top of the tree, move down the folder tree of the selected
drive
| to
| | a
| | | desired folder, and then select that folder. The dialog needs to
a)
| | return
| | | the full path to that selected directory (whence one could use
ChDir
| to
| | make
| | | it the defulat directory), or b) change the default directory to
| that
| | | selected directory?
| | |
| | | This is an Explorer-like function but I do not see how to do
this
| within
| | | Excel VBA, even using the FileSystemObjecrt or any of the
accessible
| | Excel
| | | Dialogs. Dialogs(xlDialogOpen).Show comes the closest but
requires
| | | selecting a Excel file and openning it.
| | |
| | |
| | | I want to write a VBA procedure that will list all the
directories
| in
| a
| | tree
| | | in an Excel worksheet, starting from a selected directory or the
| root
| | | directory of a select drive.
| | |
| | | How can I do this?
| | |
| | | There are Excel-Addins that list files but I have found none
that
| allow
| | | restricting the listing to directories only.
| | |
| | | John
| | |
| | |
| |
| |
| |
| |
|
|
|
|