Listbox source = directory

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

Hi group,

I have a listbox containing all the available word documents contained
within a directory. At the moment all the word documents names are i a
table and the listbox is boud to the table.

I would like the table to look up all the word documents in the directory
and populate the listbox. This would obviously mean that if a word document
was added to the directory then it would show up in the listbox.

any help appreciated
 
You might be better off using the standard Windows File Open dialog instead.

Check http://www.mvps.org/access/api/api0001.htm at "The Access Web". Yes,
it's pretty scary looking, but all you have to do is copy the highlighted
code into a new module, then use it like the sample at the top of the page.
 
I'd agree with Douglas that a common dialog is probably a better bet, but it
can be done quite easily with a list box. The AddItem method could be called
to add each filename, or a call-back function could be used. For the latter
add a function along these lines to the form's module:

Function FileList(fld As Control, ID As Variant, row As Variant, col As
Variant, Code As Variant) As Variant

Const FOLDER = "F:\SomeFolder\SomeSubFolder\"
Const FILETYPE = "*.Doc"
Dim varReturnVal As Variant
Dim n As Integer
Dim strFile As String
Static intEntries As Integer
Static aData() As Variant


Select Case Code
Case acLBInitialize
ReDim aData(n)
strFile = Dir(FOLDER & FILETYPE)
aData(n) = strFile
Do While strFile <> ""
strFile = Dir()
n = n + 1
ReDim Preserve aData(n)
aData(n) = strFile
Loop
intEntries = n
varReturnVal = True
Case acLBOpen
varReturnVal = Timer
Case acLBGetRowCount
varReturnVal = intEntries
Case acLBGetColumnCount
varReturnVal = 1
Case acLBGetColumnWidth
varReturnVal = -1
Case acLBGetValue
varReturnVal = aData(row)
Case acLBEnd
Erase aData
End Select
FileList = varReturnVal

End Function

Set the listbox's RowSourceType property to:

FileList

Note that you do NOT put an = sign before, or parentheses after the function
name in this case. Leave the control's RowSource property blank.

Ken Sheridan
Stafford, England
 
You're a good man, Ken: I was too lazy to type that out! <g>

Just a picky comment. ReDim is a relatively expensive operation in terms of
resource requirements. It might be better to predefine aData to some large
value (say, 100). Check to ensure that you don't exceed that value, and if
you do, ReDim the array to increase it by, say, another 50. At the end,
ReDim it one more time to set it to its proper size.
 
Douglas:

There wasn't a lot of typing involved; it was really just a few amendments
to another call-back function!

Thanks for the tip. When I've used call-back functions before in most cases
I've redimmed the array once at the start, which is often easy to do, e.g.
when dealing with a Collection you just examine its Count property. The only
way I can think of to do that with a folder would be to have two iterations
of the loop, one to get the count, the other to populate the array, but
there'd be a lot of disk access involved, so I ruled it out.

Now that Access supports the AddItem method I'd imagine most people would
opt for that rather than a call-back function.

Using a list box does seem to me to be something of a reinvention of the
wheel, however, when there are a million and one methods to use a common
dialogue available out there; I use Bill Wilson's freely distributed class
module:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=22415&webtag=ws-msdevapps



The choice is now with the OP as to which approach to use.

Regards

Ken Sheridan
Stafford, England
 
Back
Top