List contents of a Directory in a Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 12 Directories ( User1 - User12) located in c:\Users\..
When I select a user name from a combo box i.e. User3, a list of all files
ending with "doc"in the respective directory (c:\Users\Users3\*.doc) should
be rendered with a hyperlink to the actual file/s.
I have tried variouse "Select Case" and "FileSearch, FoundFiles" scripts,
but to no avail.

Any assistance would be appreciatred.
Current Version: Access 2000
 
Hi,

To retrieve the files you can use the Microsoft Scripting Runtime which you
should reference in VBA. Create a Table T_Files with minimum the field
filename.


Public Sub LoadFiles(Path As String)
Dim fso As New Scripting.FileSystemObject
Dim fl As Scripting.File
Dim cmdInsert As New ADODB.Command

cmdInsert.CommandText = "INSERT IN T_Files (Filename) VALUES (?)"
cmdInsert.ActiveConnection = CurrentProject.Connection
cmdInsert.Parameters.Refresh

For Each fl In fso.GetFolder(Path).Files
If InStr(1, fl.Name, ".doc") <> 0 Then
cmdInsert.Parameters(0) = fl.Name ' or fl.path if you want the
full filename
cmdInsert.Execute
End If
Next
Set cmdInsert = Nothing
Set fso = Nothing
End Sub

- Raoul
 
You can use the builtin Dir function:

Dim strFile As String
Dim strFiles As String
strFile = Dir(PathToYourFiles & "\*.doc)

Do until strFile=""
strFiles = strFiles & "," & strFile
Dir
Loop

At which point you could use strFiles as a ValueList for a listbox ... in
the Click event of the Listbox you can run code that will run the new file
with whatever application is associated with it (MSWord, for .doc files):

Sub YourListBox_Click()
Shell PathToYourFiles & "\" & Me.YourListBox.Column(0)
End Sub

Hyperlinks can be done via labels, but you'd have to have the correct number
of labels available (or more than you need) at run time, since you can't add
controls to Access at run time (without moving your form to design view,
that is). You could always add as many label controls as you think you'd
need, set them .Visible = False, then reset them and their hyperlink
property as needed in the loop above ... messy, in my opinion, but it would
work.
 
Back
Top