Subdirectories into a userform

I am not very familiar with userforms, but I use a lot of VBA and I'm very
thankful for a lot of help from this groups.

I have a directory with hundreds of subdirectorys. They are quite difficult
to "navigate" between.

I want to make a userform where the user can write one or more letters in a
field. I want the combo-box tol be filled (is that the same as populated?)
with all subdir that starts with that letter(s). From that combobox the user
can choose one directory and then the fileopen dialog is opened.

I'm sure this is possible. Hope someone can help me in the right direction!

Thanks!

Torstein

JGM
Fri Dec 05 08:31:35 CST 2003

Hi Torstein,

Would you consider this different aproach:
'_______________________________________
'Needs a reference to (Tools > Reference)
'Microsoft Shell Controls And Automation

Dim oShell As Shell32.Shell
Dim oFolder As Shell32.Folder
Dim EverythingOK As Boolean

EverythingOK = False

Set oShell = New Shell32.Shell
Set oFolder = oShell.BrowseForFolder(0, "Select ", 0)

On Error GoTo NoPath
MsgBox oFolder.Self.Path
EverythingOK = True

NoPath:
Set oFolder = Nothing
Set oShell = Nothing

If EverythingOK Then Exit Sub

On Error GoTo 0
MsgBox "Action cancelled by user.", vbExclamation, "Cancelled"
'_______________________________________

Advantage: User can delete, create or rename folders at will...
Disadvantge: User has to find his way throught he folder tree (as opposed to
typing a few letters, then selecting from a listbox... as per your idea)

HTH
Cheers!

--
_______________________________________
Jean-Guy Marcil
jmarcil@sympatico.ca

"Torstein S. Johnsen" <sejohnse@online.no> a écrit dans le message de news:
qWXzb.3580$n31.63533@news2.e.nsc.no...
> I am not very familiar with userforms, but I use a lot of VBA and I'm very
> thankful for a lot of help from this groups.
>
> I have a directory with hundreds of subdirectorys. They are quite
difficult
> to "navigate" between.
>
> I want to make a userform where the user can write one or more letters in
a
> field. I want the combo-box tol be filled (is that the same as populated?)
> with all subdir that starts with that letter(s). From that combobox the
user
> can choose one directory and then the fileopen dialog is opened.
>
> I'm sure this is possible. Hope someone can help me in the right
direction!
>
> Thanks!
>
> Torstein
>
>

Jonathan
Fri Dec 05 16:25:29 CST 2003

Hi Jean-Guy,

That is definitely neat! And I hadn't come across that library before.
Thanks for sharing! More to explore always...

--
Regards
Jonathan West - Word MVP
http://www.multilinker.com
Please reply to the newsgroup


"JGM" <no-spam@leaveme.alone> wrote in message
news:uyTdSuzuDHA.1224@TK2MSFTNGP09.phx.gbl...
> Hi Torstein,
>
> Would you consider this different aproach:
> '_______________________________________
> 'Needs a reference to (Tools > Reference)
> 'Microsoft Shell Controls And Automation
>
> Dim oShell As Shell32.Shell
> Dim oFolder As Shell32.Folder
> Dim EverythingOK As Boolean
>
> EverythingOK = False
>
> Set oShell = New Shell32.Shell
> Set oFolder = oShell.BrowseForFolder(0, "Select ", 0)
>
> On Error GoTo NoPath
> MsgBox oFolder.Self.Path
> EverythingOK = True
>
> NoPath:
> Set oFolder = Nothing
> Set oShell = Nothing
>
> If EverythingOK Then Exit Sub
>
> On Error GoTo 0
> MsgBox "Action cancelled by user.", vbExclamation, "Cancelled"
> '_______________________________________
>
> Advantage: User can delete, create or rename folders at will...
> Disadvantge: User has to find his way throught he folder tree (as opposed
to
> typing a few letters, then selecting from a listbox... as per your idea)
>
> HTH
> Cheers!
>
> --
> _______________________________________
> Jean-Guy Marcil
> jmarcil@sympatico.ca
>
> "Torstein S. Johnsen" <sejohnse@online.no> a écrit dans le message de
news:
> qWXzb.3580$n31.63533@news2.e.nsc.no...
> > I am not very familiar with userforms, but I use a lot of VBA and I'm
very
> > thankful for a lot of help from this groups.
> >
> > I have a directory with hundreds of subdirectorys. They are quite
> difficult
> > to "navigate" between.
> >
> > I want to make a userform where the user can write one or more letters
in
> a
> > field. I want the combo-box tol be filled (is that the same as
populated?)
> > with all subdir that starts with that letter(s). From that combobox the
> user
> > can choose one directory and then the fileopen dialog is opened.
> >
> > I'm sure this is possible. Hope someone can help me in the right
> direction!
> >
> > Thanks!
> >
> > Torstein
> >
> >
>
>

JGM
Fri Dec 05 20:44:02 CST 2003

Hi Jonathan,

For all the times you (and the others MVP's as well) have helped me, I am
sure glad I could share something new with you!

Have a great weekend!

Cheers!

--
_______________________________________
Jean-Guy Marcil
jmarcil@sympatico.ca

"Jonathan West" <jwest@mvps.org> a écrit dans le message de news:
Oc4Yz83uDHA.1512@TK2MSFTNGP10.phx.gbl...
> Hi Jean-Guy,
>
> That is definitely neat! And I hadn't come across that library before.
> Thanks for sharing! More to explore always...
>
> --
> Regards
> Jonathan West - Word MVP
> http://www.multilinker.com
> Please reply to the newsgroup
>
>
> "JGM" <no-spam@leaveme.alone> wrote in message
> news:uyTdSuzuDHA.1224@TK2MSFTNGP09.phx.gbl...
> > Hi Torstein,
> >
> > Would you consider this different aproach:
> > '_______________________________________
> > 'Needs a reference to (Tools > Reference)
> > 'Microsoft Shell Controls And Automation
> >
> > Dim oShell As Shell32.Shell
> > Dim oFolder As Shell32.Folder
> > Dim EverythingOK As Boolean
> >
> > EverythingOK = False
> >
> > Set oShell = New Shell32.Shell
> > Set oFolder = oShell.BrowseForFolder(0, "Select ", 0)
> >
> > On Error GoTo NoPath
> > MsgBox oFolder.Self.Path
> > EverythingOK = True
> >
> > NoPath:
> > Set oFolder = Nothing
> > Set oShell = Nothing
> >
> > If EverythingOK Then Exit Sub
> >
> > On Error GoTo 0
> > MsgBox "Action cancelled by user.", vbExclamation, "Cancelled"
> > '_______________________________________
> >
> > Advantage: User can delete, create or rename folders at will...
> > Disadvantge: User has to find his way throught he folder tree (as
opposed
> to
> > typing a few letters, then selecting from a listbox... as per your idea)
> >
> > HTH
> > Cheers!
> >
> > --
> > _______________________________________
> > Jean-Guy Marcil
> > jmarcil@sympatico.ca
> >
> > "Torstein S. Johnsen" <sejohnse@online.no> a écrit dans le message de
> news:
> > qWXzb.3580$n31.63533@news2.e.nsc.no...
> > > I am not very familiar with userforms, but I use a lot of VBA and I'm
> very
> > > thankful for a lot of help from this groups.
> > >
> > > I have a directory with hundreds of subdirectorys. They are quite
> > difficult
> > > to "navigate" between.
> > >
> > > I want to make a userform where the user can write one or more letters
> in
> > a
> > > field. I want the combo-box tol be filled (is that the same as
> populated?)
> > > with all subdir that starts with that letter(s). From that combobox
the
> > user
> > > can choose one directory and then the fileopen dialog is opened.
> > >
> > > I'm sure this is possible. Hope someone can help me in the right
> > direction!
> > >
> > > Thanks!
> > >
> > > Torstein
> > >
> > >
> >
> >
>

Jonathan
Sat Dec 06 05:31:13 CST 2003


"JGM" <no-spam@leaveme.alone> wrote in message
news:%23tIjwH6uDHA.3532@TK2MSFTNGP11.phx.gbl...
> Hi Jonathan,
>
> For all the times you (and the others MVP's as well) have helped me, I am
> sure glad I could share something new with you!
>
> Have a great weekend!

It seems it hadn't come to the notice either of the Word MVPs or even of the
VB MVPs. There are at least 3 MVPs taking an active look at that library
right now to see what it can do. I plan to update the "How to allow the user
to browse to and select a folder" article
(http://www.mvps.org/word/FAQs/MacrosVBA/BrowsDialog.htm) to include this
technique, and you'll get honorable mention as the person who brought it to
our attention :-)

--
Regards
Jonathan West - Word MVP
http://www.multilinker.com
Please reply to the newsgroup

JGM
Sat Dec 06 07:21:55 CST 2003

Hi Jonathan,

Well, I'll be...!

I was so sure that it was a standard way of doing things... I tought that
the Word.mvps site had that one covered as a standard technique!

Goes to show... you never know!
Honestly, I do not even remember where I picked this up! I thought it was
thorough you or someone else in this NG, as this is where I am being
educated!

Have a good one.
Cheers!

--
_______________________________________
Jean-Guy Marcil
jmarcil@sympatico.ca

"Jonathan West" <jwest@mvps.org> a écrit dans le message de news:
OEZ7E$#uDHA.1512@TK2MSFTNGP10.phx.gbl...
>
> "JGM" <no-spam@leaveme.alone> wrote in message
> news:%23tIjwH6uDHA.3532@TK2MSFTNGP11.phx.gbl...
> > Hi Jonathan,
> >
> > For all the times you (and the others MVP's as well) have helped me, I
am
> > sure glad I could share something new with you!
> >
> > Have a great weekend!
>
> It seems it hadn't come to the notice either of the Word MVPs or even of
the
> VB MVPs. There are at least 3 MVPs taking an active look at that library
> right now to see what it can do. I plan to update the "How to allow the
user
> to browse to and select a folder" article
> (http://www.mvps.org/word/FAQs/MacrosVBA/BrowsDialog.htm) to include this
> technique, and you'll get honorable mention as the person who brought it
to
> our attention :-)
>
> --
> Regards
> Jonathan West - Word MVP
> http://www.multilinker.com
> Please reply to the newsgroup
>

Martin
Sat Dec 06 11:04:49 CST 2003

Hi Torstein

> I want to make a userform where the user can write one or more letters in
a
> field. I want the combo-box tol be filled (is that the same as populated?)
> with all subdir that starts with that letter(s). From that combobox the
user
> can choose one directory and then the fileopen dialog is opened.

Here's one way to do it (only roughly outlined):

Consider a UserForm with a Textbox "txtQuickfinder" and a Listbox
"lbxDirectories". The former will be used to fill in the starting letters,
the latter will contain the list of matching listboxes. Write a routine,
which gathers all directories matching a given string and adds them
to the listbox ("PopulateList"), e.g.:

Private Sub PopulateListbox(lbx As ListBox, Optional filter As String = "*")
' Note that you should NEVER hardcode paths! this is just for quick'n'dirty
demonstration!!!
Const FLDR = "D:\Programme"
Dim directory As String

' clear the listbox of previously added directories
lbx.Clear
' get first file
directory = Dir(FLDR & "\*", vbDirectory)
Do
' stop if no more file found
If directory = "" Then Exit Do
' this is the tricky part: check if it is a directory, and compare
the
' current directory's name to the given filter pattern
If isDir(FLDR, directory) And (LCase(directory) Like LCase(filter))
Then
lbx.AddItem directory
End If

directory = Dir()
Loop
End Sub

Note that the Dir-function always returns files as well, so don't forget to
check if it really is a directory (line 15). The helper function to check
if a found item is a directory might look something like this:

Function isDir(path As String, name As String) As Boolean
' check if it is a directory
isDir = (GetAttr(path & "\" & name) And vbDirectory)
' check if it is not the . (active dir) or .. ("super" dir)
isDir = isDir And name <> "." And name <> ".."
End Function

That's all you need. Now for the UserForm: Let the listbox be
populated with all subdirs whenever the UserForm ist started
(Initialize-event-handler):

Private Sub UserForm_Initialize()
Call PopulateListbox(Me.lbxDirectories)
End Sub

Now the only thing left to do is to react to the Change-event of
the textbox:

Private Sub txtQuickfinder_Change()
Call PopulateListbox(Me.lbxDirectories, Me.txtQuickfinder.text & "*")
End Sub

Done! Not that difficult, was it? There are, however, various things to
consider:

- The list of directories comes unsorted. In a more sophisiticated version,
you might want to read the dirs into an array and sort it before adding
the items to the listbox.

- All the directories are read in every time the user writes a letter into
the
quickfinder-textbox. If you have hundreds of subdirs or if you are
working with network drives, things might run very slowly.


Ok, that's it for the moment. Have fun playing around with the code :-)


Cheers,

Martin

Martin
Sat Dec 06 11:23:10 CST 2003

Hello again

[This post might as well be called "PopulateListbox revisited" :-)]

> Would you consider this different aproach:
> '_______________________________________
> 'Needs a reference to (Tools > Reference)
> 'Microsoft Shell Controls And Automation

The idea of referencing other object-libraries led to my
remembering of the Windows Scripting Host and its
FileSystemObject. This way, you can get the best of
both worlds: a modern object oriented approach together
with the most possible flexibility while still getting a sorted
list of subdirs to work with:

Consider a rewrite of the PopulateListbox-Procedure
which makes use of the FSO: [Needs a reference to the Windows
Script Host Object Model]

Private Sub PopulateListboxNG(lbx As ListBox, Optional filter As String =
"*")
' Note that it still does NOT make much sense to hardcode paths like this!
' (this is just for quick'n'dirty demonstration, remember?)
Const FLDR = "D:\Programme"
Dim fso As New FileSystemObject
Dim directory As folder
Dim subdir As folder

lbx.Clear
Set directory = fso.GetFolder(FLDR)
For Each subdir In directory.SubFolders
If (LCase(subdir.name) Like LCase(filter)) Then
lbx.AddItem subdir.name
End If
Next
End Sub


Or another version without referencing the Scripting Host Object Model
(istn't COM and the IDispatch-interface a cool thing?):

Private Sub PopulateListboxNG(lbx As ListBox, Optional filter As String =
"*")
' Note that it still does NOT make much sense to hardcode paths like this!
' (this is just for quick'n'dirty demonstration, remember?)
Const FLDR = "D:\Programme"
Dim fso As Object
Dim directory As Object
Dim subdir As Object

lbx.Clear
Set fso = CreateObject("Scripting.FileSystemObject")
Set directory = fso.GetFolder(FLDR)
For Each subdir In directory.SubFolders
If (LCase(subdir.name) Like LCase(filter)) Then
lbx.AddItem subdir.name
End If
Next
End Sub


Oh, er, and remember to adjust the function calls in the initialize-
and the change-event-handlers to use PopulateListboxNG.



Cheers,

Martin

Jonathan
Sat Dec 06 12:19:31 CST 2003

In the code sample I'm developing for the mvps.org website, I plan to do two
things.

1. Use late-binding rather than early-binding, so that the same template
should be able to work without problems irrespective of which version of
Windows it is running under.

2. Have an easy-to-code way of setting all the options in the
BrowseForFolder method. I'll do this by coding it as a class module, and
then allow the different options to be set as properties.

There are apparently some problems using the Shell32 library under Windows
95 - it depends on IE4 or later being installed.

--
Regards
Jonathan West - Word MVP
http://www.multilinker.com
Please reply to the newsgroup



"JGM" <no-spam@leaveme.alone> wrote in message
news:utp%23Ks$uDHA.1680@TK2MSFTNGP12.phx.gbl...
> Hi Jonathan,
>
> Well, I'll be...!
>
> I was so sure that it was a standard way of doing things... I tought that
> the Word.mvps site had that one covered as a standard technique!
>
> Goes to show... you never know!
> Honestly, I do not even remember where I picked this up! I thought it was
> thorough you or someone else in this NG, as this is where I am being
> educated!
>
> Have a good one.
> Cheers!
>
> --
> _______________________________________
> Jean-Guy Marcil
> jmarcil@sympatico.ca
>
> "Jonathan West" <jwest@mvps.org> a écrit dans le message de news:
> OEZ7E$#uDHA.1512@TK2MSFTNGP10.phx.gbl...
> >
> > "JGM" <no-spam@leaveme.alone> wrote in message
> > news:%23tIjwH6uDHA.3532@TK2MSFTNGP11.phx.gbl...
> > > Hi Jonathan,
> > >
> > > For all the times you (and the others MVP's as well) have helped me, I
> am
> > > sure glad I could share something new with you!
> > >
> > > Have a great weekend!
> >
> > It seems it hadn't come to the notice either of the Word MVPs or even of
> the
> > VB MVPs. There are at least 3 MVPs taking an active look at that library
> > right now to see what it can do. I plan to update the "How to allow the
> user
> > to browse to and select a folder" article
> > (http://www.mvps.org/word/FAQs/MacrosVBA/BrowsDialog.htm) to include
this
> > technique, and you'll get honorable mention as the person who brought it
> to
> > our attention :-)
> >
> > --
> > Regards
> > Jonathan West - Word MVP
> > http://www.multilinker.com
> > Please reply to the newsgroup
> >
>
>

Martin
Tue Dec 09 07:49:00 CST 2003

Hi Torstein

> What do you mean with hard-code paths?

A "hard-coded" path is something like this:

Const FLDR As String = "D:\Temp"

... where a path is directly written into the VBA-code. This is generally
not a good idea since you have to change your Macro-code whenever
you want to switch to another directory. Usually, you save such a path
in a configuration file (*.ini) which you can easily access using Word's
System-object:

' save value in the specified file
System.PrivateProfileString(file, section, key) = value

Or to read a value:

' read a value out of the specified file
value = System.PrivateProfileString(file, section, key)

In order not to make things worse, you generally use a filename, which
is the same as the template/document's name but ending on ".ini". To achieve
this, you can write a simple function which constructs such a name (not
tested!):

Function getIniFilename() As String
getIniFilename = Thisdocument.FullName
getIniFilename = left(getIniFilename, len(getIniFilename)-3) & "ini"
End Function

With that function, it is easy to read/write a value to an ini file.
To save e.g. "D:\Temp" as the value of the key "Folder" in the section
"General", use the following statement:

System.PrivateProfileString(getIniFilename(), "General", "Folder") =
"D:\Temp"

To read the same value into a variable (e.g. into variable foldername),
use:

foldername = System.PrivateProfileString(getIniFilename(), "General",
"Folder")

If you create functions to read/write the path, you can workaround
the problem with "hard-coded" paths and do not need to change the
VBA-code whenever you change your mind and want to use another
directory:

Function getPath() As String
getPath = System.PrivateProfileString(getIniFilename(), "General",
"Folder")
End Function

Now, replace the occurence of all the hard-coded paths with such
a construction and you're done. The only thing you need to adjust
is now the ini file (use a text editor) :-)



Cheers,

Martin