Hyperlink to Find Filenames in a Folder

  • Thread starter Thread starter Denis Bisson via AccessMonster.com
  • Start date Start date
D

Denis Bisson via AccessMonster.com

Good day,

Access 2000 on Windows 2000 Pro

I don’t know if the following can be done and if it CAN, how do I do it…

Components:
frmPersDetails - simply a form containing personnel details;
txtLNm - text field for a person’s Last Name
cmdCV – command button… more is to come with respect to the functionality I
am seeking…
Folder - “N:\Company\HR\Résumés\”


All soft copies of Résumés are stored in folder: N:\Company\HR\Résumés\
While I know how to program a hyperlink to cmdCV to open up N:\Company\HR\
Résumés\, what I would like to do is to open N:\Company\HR\Résumés\ so that
it only shows me the files/filenames that contain the text found in txtLNm.

For example: Assume txtLNm.value = “Smith”, I would expect to see the “N:\
Company\HR\Résumés\” folder displaying the following:
Frank Smith CV.doc;
Smith, Aaron.doc ;
etc

This almost sounds like a find/search/locate operation of some sort, and I
don’t know if (or rather HOW) it can be done.

Your assistance and time is appreciated.

Denis
 
Hi Denis,

Here is some code that might help. I haven't had the time to test it but it
should get you in the right direction. Based on what I understood, this code
will iterate through an array of file names and use a regular expression to
see if any of them match what was typed into your textbox.

Let me know if you have any questions.

Lance


' Don't forget to add a reference to Microsoft VBScript Regular
Expressions 5.5 or applicable version
Dim objRegExt As RegExp
Dim expressionMatch as Boolean
Dim fileCollection() as String

' Fill the file array
' Insert your code here to populate the array...

' Check to make sure the file name field isn't blank
If txtLNm = "" Then
Msgbox "You must supply a file name.", vbOkOnly + vbInformation,
"File Name Error"
End If

' Create a new Regular Expression object
Set objRegEx = New RegExp

' Declare the RegEx parameters
With objRegEx
.Pattern = Trim(txtLNm)
.IgnoreCase = True
.Global = False
End With

Dim counter as Integer

for counter = 0 to Ubound(fileCollection)
' Perform the RegEx comparison
expressionMatch = objRegEx.Test(fileCollection(counter))

' If there is a match, display the file name
If expressionMatch = True Then
' Add code to display the name in a listbox
End If
next counter
 
Hi Lance,

My interpretation of your reply is that you may be under the impression
that I wish this code to ‘iterate through an array of file names’ WITHIN
the database – which I am not. If this is the impression I left you with,
I apologize for not being clearer.

The list of file names (and files) would be located in folder (“N:\Company\
HR\Résumés\”) on the network. Since we are talking about the likelihood of
thousands of résumés, my ‘wish’ is to Open the folder from the database
(via a hyperlink attached to cmdLink) and have the folder ‘filtered’ to
include the string found in txtLNm. Pretty much mimicking the Windows
“Search for Files and Folders” dialog box functionality where we would
search for i.e. Smith.doc.

Thanks again
Denis
 
Hi Denis,

Actually, I assumed that they were being through directory searching code
and as such I thought you already had the code to obtain the file names.

I usually use the File System Object to get my list of file names and I
sometimes store them in an array depending on what I might be doing.

While I am not sure right off the bat how you could get the actual Windows
Search dialog without an API call, you could build your own search form and
use the code I have described below.

For example, the following code populates a global variable called
managementReports (which is a Collection object) so that the calling method
(in my code it is the Form_Load event so it is populated at startup) can
populate a listbox with the file name, size, modified date and path. Hope
this helps.

Private Function ListReports(SourceFolderName As String, IncludeSubfolders
As Boolean)
On Error GoTo ErrHandler

Dim FSO As Scripting.FileSystemObject
Dim sourceFolder As Scripting.Folder, subFolder As Scripting.Folder
Dim fileItem As Scripting.File

Set managementReports = New VBA.Collection

Set FSO = New Scripting.FileSystemObject
Set sourceFolder = FSO.GetFolder(SourceFolderName)

For Each fileItem In sourceFolder.Files
If Left(fileItem.Name, 1) <> "~" And Left(fileItem.Name, 1) <> "$"
And Right(fileItem.Name, 3) = "doc" Then
managementReports.Add fileItem.Name & ";" & Round(fileItem.Size
/ 1024, 1) & ";" & fileItem.DateLastModified & ";" & fileItem.path
End If
Next fileItem

If IncludeSubfolders Then
For Each subFolder In sourceFolder.SubFolders
ListFilesInFolder subFolder.path, True
Next subFolder
End If

Set fileItem = Nothing
Set sourceFolder = Nothing
Set FSO = Nothing

Exit_ErrHandler:
Exit Function

ErrHandler:
Dim errorMessage As String
errorMessage = "An error occurred while attempting to gather a list of
available reports." & Chr(13) & Chr(13)
errorMessage = errorMessage & Err.Number & Chr(13)
errorMessage = errorMessage & Err.Description
MsgBox errorMessage, vbOKOnly + vbCritical, "Directory Search Error"
Resume Exit_ErrHandler
End Function

Private Function ListFilesInFolder(SourceFolderName As String,
IncludeSubfolders As Boolean)
On Error GoTo ErrHandler

Dim FSO As Scripting.FileSystemObject
Dim sourceFolder As Scripting.Folder, subFolder As Scripting.Folder
Dim fileItem As Scripting.File

Set fileList = New VBA.Collection

Set FSO = New Scripting.FileSystemObject
Set sourceFolder = FSO.GetFolder(SourceFolderName)

For Each fileItem In sourceFolder.Files
' Add it to the array which will be passed back to the caller
fileList.Add fileItem
Next fileItem

If IncludeSubfolders Then
For Each subFolder In sourceFolder.SubFolders
ListFilesInFolder subFolder.path, True
Next subFolder
End If

Set fileItem = Nothing
Set sourceFolder = Nothing
Set FSO = Nothing

Exit_ErrHandler:
Exit Function

ErrHandler:
Dim errorMessage As String
errorMessage = "An error occurred while attempting to gather a list of
available reports." & Chr(13) & Chr(13)
errorMessage = errorMessage & Err.Number & Chr(13)
errorMessage = errorMessage & Err.Description
MsgBox errorMessage, vbOKOnly + vbCritical, "Directory Search Error"
Resume Exit_ErrHandler
End Function
 
Hi Denis,

As far as I know you can't do this with a hyperlink.

One alternative is to use the code at
http://www.mvps.org/access/api/api0001.htm to display the standard
Windows File|Open dialog, passing it details of the folder and a
wildcard pattern that will match the files you need. This gives the
users a familiar interface and lets them open files in other locations
if necessary.

Another is to do as Lance suggested, namely populate a listbox or
combobox on your form with files that meet the requirements. This gives
you 100% control of the list the user sees.

Recent versions of Access have a FileSearch object, but I can't remember
whether it existed in Access 2000. If it does, that's another
possibility.
 
Lance and John,
Thank you for your great solutions. Unfortunately, I am not at work today,
however I will certainly experiment with your suggestions when I get back!
Cheers!
Denis
 
Back
Top