Changing to a selected folder

J

John Wirt

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
 
W

William

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

(e-mail address removed)

| 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
|
|
 
J

John Wirt

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
 
W

William

Hi John

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


--
XL2002
Regards

William

(e-mail address removed)

| 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
|
|
| | > 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
| >
| > (e-mail address removed)
| >
| > | > | 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
| > |
| > |
| >
| >
|
|
 
T

Tom Ogilvy

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

William

Thankyou Tom

No doubt you are right.

--
XL2002
Regards

William

(e-mail address removed)

| 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
|
| | > Hi John
| >
| > I retested the code and it appears to run fine - at least in XL2002 it
| does.
| >
| >
| > --
| > XL2002
| > Regards
| >
| > William
| >
| > (e-mail address removed)
| >
| > | > | 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
| > |
| > |
| > | | > | > 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
| > | >
| > | > (e-mail address removed)
| > | >
| > | > | > | > | 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
| > | > |
| > | > |
| > | >
| > | >
| > |
| > |
| >
| >
|
|
 

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

Similar Threads

Open Workbook Question 1
Saving templete / workbook to a specific drive / file 1
Custom Document Properties in Word documents and Excel workbooks 13
change local drive / folder to network folder 3
Move all except latest to another folder 2
Open Files in Folder 2
execution speed issue when closing files 5
Selected... or not ?? 5

Top