Populate List Box with Word doc names in a folder

[Steve C]

I'm creating a user form with two list boxes. The list box on the left
(lstDivisions)has check boxes for Divisions (i.e., 01, 02, 03 etc.). Upon
selecting the desired division, I'd like the right list box (lstAvailDocs) to
display all the names of Word documents that exist in the corresponding
folder number located at G:\Master Specifications. For example, if the user
chooses division 02 on the left, then the right list box should display all
Word documents in G:\Master Specifications\02. The desired 02 folder may
contain other non-Word files, but I only want Word documents to display in
the second list box.

I made a start with the code below, but I need help with the details. Thanks!

-- Steve C

Private Sub lstbxDivisions_Change()
'Runs when a check box in Divisions list box is selected

For x = 0 To lstbxDivisions.ListCount - 1

If lstbxDivisions.Selected(x) = True Then
LastSelected = lstbxDivisions.List(x) 'Check box has been selected

'Following populates the AvailDocs list box with choices made in
lstbxDivisions

With lstbxAvailDocs

Ifile = FreeFile
Open "G:\Master Specifications\" & LastSelected For Input As #Ifile
While Not EOF(Ifile)
Line Input #Ifile, SpecName
.AddItem SpecName
Wend
Close #Ifile
End With

Next x

End Sub

 

[Jay Freedman]

I'm creating a user form with two list boxes. The list box on the left
(lstDivisions)has check boxes for Divisions (i.e., 01, 02, 03 etc.). Upon
selecting the desired division, I'd like the right list box (lstAvailDocs) to
display all the names of Word documents that exist in the corresponding
folder number located at G:\Master Specifications. For example, if the user
chooses division 02 on the left, then the right list box should display all
Word documents in G:\Master Specifications\02. The desired 02 folder may
contain other non-Word files, but I only want Word documents to display in
the second list box.

I made a start with the code below, but I need help with the details. Thanks!

-- Steve C

Private Sub lstbxDivisions_Change()
'Runs when a check box in Divisions list box is selected

For x = 0 To lstbxDivisions.ListCount - 1

If lstbxDivisions.Selected(x) = True Then
LastSelected = lstbxDivisions.List(x) 'Check box has been selected

'Following populates the AvailDocs list box with choices made in
lstbxDivisions

With lstbxAvailDocs

Ifile = FreeFile
Open "G:\Master Specifications\" & LastSelected For Input As #Ifile
While Not EOF(Ifile)
Line Input #Ifile, SpecName
.AddItem SpecName
Wend
Close #Ifile
End With

Next x

End Sub

Hi Steve,

Nice thought, but the Open statement can't open a folder as if it was a text
file. Your code would bomb on that line and never go any further.

The function you need is the Dir function (short for "directory"). The way this
works is that you call it the first time with a pattern, consisting of a folder
path and a wildcard-specified filename. If the function returns a non-empty
string, that will be the filename of the first file that matches the pattern.
After that, you continue calling the Dir function without any parameter, getting
one name after another, until the result is an empty string.

An example of this use is in
http://www.word.mvps.org/FAQs/MacrosVBA/InsertFileNames.htm. To modify that code
to your use, replace the Selection.InsertAfter statement (for putting the
filename into a document) with your .AddItem statement to load the list box.

 

[Steve C]

Excellent, Jay! Borrowing from the code you gave me a link to (thank you
also, Bill Coan), I successfully modified mine as follows to do the trick.
Thanks for helping me learn something new!

Steve C.


Private Sub lstbxDivisions_Change()
'Runs whenever a check box in the 1st list box is selected

Dim MyPath As String
Dim MyName As String

Me.lstbxAvailDocs.Clear 'always clears 2nd list box before proceeding

For x = 0 To lstbxDivisions.ListCount - 1

If lstbxDivisions.Selected(x) = True Then 'a Division check box has been
selected
LastSelected = lstbxDivisions.List(x) 'captures Division number

'Insert into a document the names of all files in a selected folder
MyPath = "G:\Master Specifications\" & LastSelected & "\"

'Get files from the selected path and add them to 2nd list box
MyName = Dir$(MyPath & "*.doc") 'only looks for Word doc files

With lstbxAvailDocs
Do While MyName <> ""
MyName = Left(MyName, Len(MyName) - 4) 'removes .Doc from end of name
.AddItem MyName
MyName = Dir
Loop
End With
End If