Fill drop list

[Pastor Del]

i want the user to be able to open a document like word or excel by selecting
it from a combo drop list. how can i fill the drop list with the files of a
particular folder?

 

[fredg]

i want the user to be able to open a document like word or excel by selecting
it from a combo drop list. how can i fill the drop list with the files of a
particular folder?

Copy and Paste the below code into a Module.

Public Sub GetlistFiles()
' fill a List Box with the names of files found in folder
Dim MyName As String
Dim FS As Object
Dim I As Integer
Dim strList As String

On Error GoTo Err_Handler

Set FS = Application.FileSearch
With FS
.Lookin = "C:\PathToYourFolderName\"
If .Execute(SortOrder:=1) > 0 Then
For I = 1 To .foundFiles.Count
MyName = Mid(.foundFiles(I), InStrRev(.foundFiles(I), "\") + 1)
strList = strList & MyName & ","
Next I
Else
MsgBox "There were no files found."
End If
End With
strList = Left(strList, Len(strList) - 1)
Forms!TheFormName!lstExternalFiles.RowSource = strList

Set FS = Nothing

Exit_This_Sub:
Exit Sub
Err_Handler:
MsgBox "Error #: " & Err.Number & " " & Err.Description
Resume Exit_This_Sub
End Sub
****************
Add a List Box (or a Combo Box) to your form.
Set it's RowSourceType property to Value List.
Leave it's RowSource property blank.
Name this control "lstExternalFiles".

Code the Form's Open Load event:
GetlistFiles

When the form Loads it will read the names of all files in the named
folder and fill the rowsource of the list or combo box.

To then open the selected file, either code the List box Double-click
event or the Combo Box AfterUpdate event:
Application.FollowHyperlink Me.lstExternalFiles

Change TheFormName and lstExternalFiles to whatever the actual name of
your form and list box/combo box is.

 

[Jack Leach]

http://allenbrowne.com/ser-59.html

Take a look there....

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Jinjer]

Hi,

If you want a simple way, instead of using a combo box, you can perhaps do
the following:

1. Either use the path where the documents are kept or put shortcuts to the
documents in a file folder that everyone can open.
2. Place an unbound textbox control on your form with the file folder path
as the default value (ex: c:\OurDocuments)
3. Create an On Click event on the textbox control with the following code:

Application.FollowHyperline Me.textboxname

(of course, textboxname will be whatever name you give to your textbox
control)

When the user clicks on it, this will open Windows Explorer in the specified
folder with the list of documents.

However, if you don't want the users to open the folder itself, you will be
better off with using the code in the article Jack Leach gave you.

 

[Jinjer]

Oops:

Corrected code:

Application.FollowHyperlink Me.textboxname

 

[Jinjer]

Oops

Corrected code:

Application.FollowHyperlink Me.textboxname

 

[Pastor Del]

when i compile the code i get the following compile error: method or data
member not found. then it highlights additem in the Function ListFiles

i'm using access 2000. is there another library i can add to the list that
will help?

 

[Jack Leach]

I think this is because you are passing a combobox rather than a listbox to
the function. The listbox has an AddItem method, but not a combo. I think
you should be able to make a slight modification to that portion of the code
to return a Value List string that can be applied to a Combo...

in the declaration...

Public Function ListFiles(strPath As String, Optional strFileSpec As String, _
Optional bIncludeSubfolders As Boolean, Optional lst As ListBox)


change the "Optional lst As ListBox" argument to "Optional lst As ComboBox"


and in the function...


If lst Is Nothing Then
For Each varItem In colDirList
Debug.Print varItem
Next
Else
lst.RowSource = ""
For Each varItem In colDirList
lst.Rowsource = lst.Rowsource & Str(varItem) & ";"
Next
lst.RowSource = Left(lst.RowSource, Len(lst.RowSource) - 1)
lst.Requery
End If

modify this portion of the code to the following (I haven't tested but am
fairly confident it will work...)

This should build a filelist "C:\thisfile; C:\ThatFile; C:\OtherFile;" and
remove the trailing ";"

hth

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Pastor Del]

Thanks Jack. You are a huge help. I'm self taught in this and much of it by
T Edison's principle that you listed below. This is pushing the limits of my
experience/knowledge. It took a few minutes to understand what your code was
doing but it seems like it should work.