B
BruceM
I have a database to identify the manufacturing process instruction
associated with a particular task. The process instructions are identiied
as 05-03, 08-21, and things like that in which the year is the beginning of
the ID. The names are assigned independent of the database, and are
included in the file name of the associated Word document. For instance,
MyCo 05-03 may be for welding part numbers 12345 and 23456. The file name
is:
MyCo 05-03 (Welding) [12345, 23456]
With the database the user can search for Part Number 12345 and learn that
it is associated with the process instruction 05-03. This works as
intended. Clicking a command button should be able to open the document
named "MyCo 05-03 (Welding) [12345, 23456]". The record contains the field
MyCo, in which the number 05-03 is stored. All documents in this category
adhere to a file-naming convention such as I have described.
I found Allen Browne's code to list file names recursively. I have modified
the code to open the file that fits the parameters. The code followng the
Click event code below is in a standard module. I use the code by clicking
a command button on the form, with this as the Click event:
Dim strFile As String
strFile = "* " & Me.MyCo & " *.doc"
Call ListFiles(conPath, strFile, True)
Here is Allen's code, with the modified part highlighted with asterisks:
Option Compare Database
Option Explicit
' *******I added the constant**********
Public Const conPath As String = "\\ServerName\DirectoryName\"
Public Function ListFiles(strPath As String, Optional strFileSpec As String,
_
Optional bIncludeSubfolders As Boolean, Optional lst As ListBox)
On Error GoTo Err_Handler
'Purpose: List the files in the path.
'Arguments: strPath = the path to search.
' strFileSpec = "*.*" unless you specify differently.
' bIncludeSubfolders: If True, returns results from
subdirectories of strPath as well.
' lst: if you pass in a list box, items are added to it. If
not, files are listed to immediate window.
' The list box must have its Row Source Type property set
to Value List.
'Method: FilDir() adds items to a collection, calling itself
recursively for subfolders.
Dim colDirList As New Collection
Dim varItem As Variant
Call FillDir(colDirList, strPath, strFileSpec, bIncludeSubfolders)
'Add the files to a list box if one was passed in. Otherwise list to the
Immediate Window.
If lst Is Nothing Then
For Each varItem In colDirList
' ************ This is where the file is opened**************
Application.FollowHyperlink (varItem)
Next
Else
For Each varItem In colDirList
lst.AddItem varItem
Next
End If
Exit_Handler:
Exit Function
Err_Handler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Exit_Handler
End Function
Private 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 folder.
strFolder = TrailingSlash(strFolder)
strTemp = Dir(strFolder & strFileSpec)
Do While strTemp <> vbNullString
colDirList.Add strFolder & strTemp
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
' CODE END **************************
This works in that it opens the file with the Application.FollowHyperlink
line of code. However, the code as written is intended to build an array,
and list the array's elements. I have used it to build a one-item array, in
effect, which may not be the most efficient approach. I just want to find
the file that matches the strFileSpec argument. Once it is found I want to
pass its value to Application.FollowHyperlink. I don't think I need an
array, and I would think I could call FillDir directly rather than by way of
ListFiles, but I can't figure out how to do that. Also, I may not need
TrailingSlash.
What this posting amounts to is that I want to know if I am on the right
general track. If so, I want to streamline the code for my specific
purposes, if possible and if it makes sense to do so.
Note: I think the file-naming convention will guard against two files
matching the criterium for strFileSpec, but it would probably be safest to
see if there is more than one matching file. In that case maybe the array
is needed after all, but I'm a bit out of my depth here, so I couldn't say.
Another note: I have in the past used a function called ExecuteFile, which I
found online somewhere for opening files when the host application is not
known (I think that's what it's for). I can no longer find the link, but I
have the code, which I can post if necessary. However, the documents in
question are Word documents that open without difficulty using
Application.FollowHyperlink, so there may be no need for more than that.
associated with a particular task. The process instructions are identiied
as 05-03, 08-21, and things like that in which the year is the beginning of
the ID. The names are assigned independent of the database, and are
included in the file name of the associated Word document. For instance,
MyCo 05-03 may be for welding part numbers 12345 and 23456. The file name
is:
MyCo 05-03 (Welding) [12345, 23456]
With the database the user can search for Part Number 12345 and learn that
it is associated with the process instruction 05-03. This works as
intended. Clicking a command button should be able to open the document
named "MyCo 05-03 (Welding) [12345, 23456]". The record contains the field
MyCo, in which the number 05-03 is stored. All documents in this category
adhere to a file-naming convention such as I have described.
I found Allen Browne's code to list file names recursively. I have modified
the code to open the file that fits the parameters. The code followng the
Click event code below is in a standard module. I use the code by clicking
a command button on the form, with this as the Click event:
Dim strFile As String
strFile = "* " & Me.MyCo & " *.doc"
Call ListFiles(conPath, strFile, True)
Here is Allen's code, with the modified part highlighted with asterisks:
Option Compare Database
Option Explicit
' *******I added the constant**********
Public Const conPath As String = "\\ServerName\DirectoryName\"
Public Function ListFiles(strPath As String, Optional strFileSpec As String,
_
Optional bIncludeSubfolders As Boolean, Optional lst As ListBox)
On Error GoTo Err_Handler
'Purpose: List the files in the path.
'Arguments: strPath = the path to search.
' strFileSpec = "*.*" unless you specify differently.
' bIncludeSubfolders: If True, returns results from
subdirectories of strPath as well.
' lst: if you pass in a list box, items are added to it. If
not, files are listed to immediate window.
' The list box must have its Row Source Type property set
to Value List.
'Method: FilDir() adds items to a collection, calling itself
recursively for subfolders.
Dim colDirList As New Collection
Dim varItem As Variant
Call FillDir(colDirList, strPath, strFileSpec, bIncludeSubfolders)
'Add the files to a list box if one was passed in. Otherwise list to the
Immediate Window.
If lst Is Nothing Then
For Each varItem In colDirList
' ************ This is where the file is opened**************
Application.FollowHyperlink (varItem)
Next
Else
For Each varItem In colDirList
lst.AddItem varItem
Next
End If
Exit_Handler:
Exit Function
Err_Handler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Exit_Handler
End Function
Private 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 folder.
strFolder = TrailingSlash(strFolder)
strTemp = Dir(strFolder & strFileSpec)
Do While strTemp <> vbNullString
colDirList.Add strFolder & strTemp
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
' CODE END **************************
This works in that it opens the file with the Application.FollowHyperlink
line of code. However, the code as written is intended to build an array,
and list the array's elements. I have used it to build a one-item array, in
effect, which may not be the most efficient approach. I just want to find
the file that matches the strFileSpec argument. Once it is found I want to
pass its value to Application.FollowHyperlink. I don't think I need an
array, and I would think I could call FillDir directly rather than by way of
ListFiles, but I can't figure out how to do that. Also, I may not need
TrailingSlash.
What this posting amounts to is that I want to know if I am on the right
general track. If so, I want to streamline the code for my specific
purposes, if possible and if it makes sense to do so.
Note: I think the file-naming convention will guard against two files
matching the criterium for strFileSpec, but it would probably be safest to
see if there is more than one matching file. In that case maybe the array
is needed after all, but I'm a bit out of my depth here, so I couldn't say.
Another note: I have in the past used a function called ExecuteFile, which I
found online somewhere for opening files when the host application is not
known (I think that's what it's for). I can no longer find the link, but I
have the code, which I can post if necessary. However, the documents in
question are Word documents that open without difficulty using
Application.FollowHyperlink, so there may be no need for more than that.