Open the selected file

[BruceM]

I accidentally posted this as a reply to my own question on Nov. 5. I have
copied that "reply" and posted it as new:


I posted this question before (in this group on Nov. 5) with quite a bit of
detail that was intended in part to show I had tried everything I could
think of or find before I posted, but perhaps it was too much detail, as I
received no replies. I will try a more minimal approach, and fill in
details as needed.

I would like to open a file that corresponds to a field in the current
record. If the field (DocID) is "95-50" I need to scan folder and subfolder
contents until I find "MyCo 95-50 (123456)". Once it is found, open it.

I have adapted Allen Browne's code for listing files in a folder and its
subfolders. Since I need a particular file rather than a listing, I have
removed some things from the code. This is what I have:

First, in the Click event of a comman button on the form:

Dim strPath As String, strFile As String
Dim colDirList As New Collection

strFile = "* " & Me.[DocID] & " *.doc"
strPath = "\\ServerName\DriveName\FolderName\"

Call FillDir(colDirList, strPath, strFile, True)

In a standard module:

Public Function FillDir(colDirList As Collection, ByVal strFolder As String,
strFileSpec As String, _
bIncludeSubfolders As Boolean)

'Build up a list of files, and then add add to this list any additional
folders
Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant

'Add the files to the list
strTemp = Dir(strFolder & strFileSpec)
Do While strTemp <> vbNullString
colDirList.Add strFolder & strTemp
If colDirList.Count > 0 Then
Application.FollowHyperlink (strFolder & strTemp)
Exit Function
End If
strTemp = Dir
Loop

If bIncludeSubfolders Then
'Build collection of additional subfolders.
strTemp = Dir(strFolder, vbDirectory)
Do While strTemp <> vbNullString
If (strTemp <> ".") And (strTemp <> "..") Then
If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0& Then
colFolders.Add strTemp
End If
End If
strTemp = Dir
Loop

'Call function recursively for each subfolder.
For Each vFolderName In colFolders
Call FillDir(colDirList, strFolder & TrailingSlash(vFolderName),
strFileSpec, True)
Next vFolderName
End If

End Function

Public Function TrailingSlash(varIn As Variant) As String
If Len(varIn) > 0& Then
If Right(varIn, 1&) = "\" Then
TrailingSlash = varIn
Else
TrailingSlash = varIn & "\"
End If
End If
End Function

When the colDirList count is 1 the code opens the file that corresponds to
DocID, then stops since the correct file has been located. However, it
seems clunky to build a collection that will contain only a single item. I
just can't sort out how to loop through the file names otherwise, and I
don't know if it matters that I am building a one-item collection.

Another question is how to generate a message if the file is not found. I
added error handling, but the lack of a file that fits the criteria does not
seem to be an error, so I guess the question is how to identify that the
file was not found.

Of course, the answer may be to take a completely different approach, but I
do know that this one seems to work except that it is not helpful when the
file is not found.