Dir$

[Kerry Hewett]

Hi

I have created a user form to display the contents of a
folder in a list box - this is working wonderfully (please
see code below).

What I now need to do is allow the user to double click
many of these files copying them into another list box.
The users will then re-arrange the files in the 2nd list
box to choose what order to insert them into the current
document.

Is this possible?? Can anyone please help or suggest a
good place/book to start.

Thanks very much for your time.

CODE:

Private Sub UserForm_Activate()

Dim MyFile As String
Dim Counter As Long

'Create a dynamic array variable, and then declare its
initial size
Dim DirectoryListArray() As String
ReDim DirectoryListArray(1000)

'Loop through all the files in the directory by using Dir$
function
MyFile = Dir$("C:\my Documents\*.*")
Do While MyFile <> ""
DirectoryListArray(Counter) = MyFile
MyFile = Dir$
Counter = Counter + 1
Loop

'Reset the size of the array without losing its values by
using Redim Preserve
ReDim Preserve DirectoryListArray(Counter - 1)

'To prove it worked you could run the following:

For Counter = 0 To UBound(DirectoryListArray)
'Debug.Print writes the results to the Immediate
window (press Ctrl + G to view it)'
Debug.Print DirectoryListArray(Counter)
Next Counter

'To populate a Listbox from the array you could use:
ListBox1.List = DirectoryListArray
End Sub

 

[Helmut Weber]

Hi Kerry,
sounds overcomplicated to me.
I would suggest to use the double_click-event,
and insert the file, the name of which name was doubleclicked,
and to remove that entry from the list, unless you want to
allow the user, to insert the file several times.
For rearranging the order in a list, there would always
be two questions: "what entry" and "where should it go".
If a double click (seemingly simple) would place the selected
item on top of the list, the user may get hopelessly confused after
clicking the wrong entry. In that case, he had to doubleclick all
previously put on top entries again. Though there must be a solution
by drag and drop. But at what cost? Could be beyond what VBA
is made for.
Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98

 

[Kerry Hewett]

Yes, I agree it does sound v complicated, I wasn't sure
where to start. I think if the users double click the
files they wish in the order in which they want them - it
would make my job a lot simpler.

I have tried adding double click event on the list box but
it doesn't seems to recognise which file i selected. Have
I used the correct code for listing the files to allow me
this event?? (see original text below) Thanks very much
indeed for your time.

Kerry.

 

[Helmut Weber]

Hi Kerry,
I'd suggest something like this:
Sub ShowForm()
UserForm1.Show vbModeless
' modeless means user can
' navigate in the doc or edit it
' while the form stays visible
End Sub
---
Private Sub UserForm_Activate()
' build list of filenames
Dim i As Integer
Const sPth = "c:\test\"
With Application.FileSearch
.NewSearch
.LookIn = sPth
.FileName = "*.doc"
.Execute msoSortByFileName
For i = 1 To .FoundFiles.Count
ListBox1.AddItem .FoundFiles(i)
Next
End With
End Sub
---
Private Sub ListBox1_DblClick(ByVal Cancel As
MSForms.ReturnBoolean)
' insert file at cursor position
' or overwrite selection
Dim sFullname As String
sFullname = ListBox1.List(ListBox1.ListIndex)
Selection.InsertFile sFullname
End Sub
---
In case, you want only the filename,
not the fullname including path in the list,
let us know. Can be done, too.

Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word 2002, Windows 2000