List box question

  • Thread starter Thread starter Michael Bond
  • Start date Start date
M

Michael Bond

Hello all

I need a little help please....

The following code populates a single column listbox on a
form with the files contained in a directory

Private Sub UserForm_Initialize()

Dim fs, f, f1, fc, S
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder("X:\")
Set fc = f.Files

ListBox1.ColumnCount = 1
ItemCount = 0
ItemTitle = 2

For Each f1 In fc

ListBox1.AddItem f1.Name

Next
End Sub


The idea is for the user to choose a file on which to
perform some work.

The problem?...the user does not always know the content
of the file. The file name is a string made up from the
date and time the file was created e.g. 121603_1542.xls

I want to add a second column in the list box which will
display some of the property attributes of the file e.g.
the title or the subject. The title/subject, or whatever
property I can access, can be used to hold a string which
shows the user some of the details of the file content
(for example the date range of the data it contains)

Can anyone help with the code to populate that second
column....or other suggestion to acheive my aim.

Appreciate any help.

Regards

Michael Bond
 
Of course, you need: ListBox1.ColumnCount = 1

Then after: ListBox1.AddItem f1.Name
Insert: ListBox1.List(ListBox1.ListCount - 1, 1) = "whatever"

HTH,
Merjet
 
This shows how to create/populate a 2 column listbox. The ColumnCount and
ColumnWidths properties could be pre-set in the Properties box and then
wouldn't need to be set in the code.

Private Sub UserForm_Initialize()
With Me.ListBox1
.ColumnCount = 2
.ColumnWidths = "20;50"
.AddItem "1"
.AddItem "2"
.AddItem "3"
.Column(1, 0) = "a description of 1"
.Column(1, 1) = "a description of 2"
.Column(1, 2) = "a description of 3"
End With
End Sub

You can also fill a listbox from an array:

Private Sub UserForm_Initialize()
Dim MyArray(1 To 3, 1 To 2) As String
MyArray(1, 1) = "1"
MyArray(2, 1) = "2"
MyArray(3, 1) = "3"
MyArray(1, 2) = "Description of 1"
MyArray(2, 2) = "Description of 2"
MyArray(3, 2) = "Description of 3"
With Me.ListBox1
.ColumnCount = 2
.ColumnWidths = "20;50"
.List = MyArray
End With
End Sub
 
Jim, Merjet

thnaks for the pointers in populating the two
columns...only one issue remaining....how do I pick up on
the property attribute of the files themselves...that is
f1.Name gives me the name of the file but what do I need
to get the "subject" or "title" of the file....these are
normally set in the properties of the file in excel and I
want to use them as the content for the second column

I've tried f1.Title and f1.Subject but both return "object
does not support this"

....any further ideas ?

Michael Bond
 
thnaks for the pointers in populating the two
columns...only one issue remaining....how do I pick up on
the property attribute of the files themselves...that is
f1.Name gives me the name of the file but what do I need
to get the "subject" or "title" of the file....these are
normally set in the properties of the file in excel and I
want to use them as the content for the second column

I've tried f1.Title and f1.Subject but both return "object
does not support this"
Click to expand...

Change your declare stmt for f1 to: Dim f1 as File
Then type 'f1.' and IntelliSense will show you your options
are, although they probably not enough for you.

Merjet
 
I have found a way of doing what I want. I post it here
for anyone else interested......BUT.....it is cumbersome
and takes too long to run so if anyone knows a better way
please post and let me know.

Thanks

Michael Bond


Private Sub UserForm_Initialize()

Application.ScreenUpdating = False

Dim Subject As String
Dim MyFile As String
Dim fs, f, f1, fc
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder("c:\")
Set fc = f.Files

ListBox1.ColumnCount = 1

With Me.ListBox1
.ColumnCount = 2
.ColumnWidths = "150;50"
End With

For Each f1 In fc

Workbooks.Open Filename:=(f & "\" & f1.Name)
Subject = ActiveWorkbook.BuiltinDocumentProperties
(2)
MyFile = Left$(f1.Name, Len(f1.Name) - 4)
Workbooks(f1.Name).Close
ListBox1.AddItem MyFile
ListBox1.List(ListBox1.ListCount - 1, 1) = Subject

Next
End Sub

BuiltinDocumentProperties(2) refers to the subject field
which you find when you use "File", "Properties". I
populate this field when the file is saved using the
following:

dim Subject as String

Subject = InputBox("Enter the range of data contained in
this file"'"DataRange")
ActiveWorkbook.BuiltinDocumentProperties(2) = Subject
 
Back
Top