Trim path name from list of documents?

K

Kim M.

Hi. I have a cbox that has as its contents the names of documents in a
specific folder. I used the code below provided by a very helpful expert
here (thanks!) in a module to populate the cbox when the form loads. It
works great. The only problem is that it returns the entire path name along
with the document names, which takes up a lot of space on the screen, because
I have to make the cbox really wide to show it all. So, my question is: how
can I modify this code to only return the name of the documents as the
contents of the cbox, without the whole path name tacked on?
TIA, Kim M.


Option Compare Database

Public Function ListFiles(strPath As String, Optional strFileSpec As String, _
Optional bIncludeSubfolders As Boolean, Optional cbo As ComboBox)
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 cbo Is Nothing Then
For Each varItem In colDirList
Debug.Print varItem
Next
Else
For Each varItem In colDirList
cbo.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
 
D

Dirk Goldgar

Kim M. said:
Hi. I have a cbox that has as its contents the names of documents in a
specific folder. I used the code below provided by a very helpful expert
here (thanks!) in a module to populate the cbox when the form loads. It
works great. The only problem is that it returns the entire path name
along
with the document names, which takes up a lot of space on the screen,
because
I have to make the cbox really wide to show it all. So, my question is:
how
can I modify this code to only return the name of the documents as the
contents of the cbox, without the whole path name tacked on?
TIA, Kim M.


Option Compare Database

Public Function ListFiles(strPath As String, Optional strFileSpec As
String, _
Optional bIncludeSubfolders As Boolean, Optional cbo As ComboBox)
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 cbo Is Nothing Then
For Each varItem In colDirList
Debug.Print varItem
Next
Else
For Each varItem In colDirList
cbo.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


See this line in FillDir():
colDirList.Add strFolder & strTemp

?

Change that line to:

colDirList.Add strTemp

Note, though, that doing that will make it impossible to distinguish files
from separate folders, if you tell the function to include subfolders. All
you'll get are the filenames.
 
J

Jack Leach

And always include the Option Explicit declaration. This ensures that if
you mistype a name or variable that it throws a compile error... otherwise
vba creates a new variant variable if you accidentally mistype something. I
believe there's a an option in the VBA IDE Options called something like
"require variable declaration" (at least thats what it is for 2003, not sure
in 2007).

--
Jack Leach
www.tristatemachine.com

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

Dirk Goldgar

Jack Leach said:
And always include the Option Explicit declaration. This ensures that if
you mistype a name or variable that it throws a compile error... otherwise
vba creates a new variant variable if you accidentally mistype something.
I
believe there's a an option in the VBA IDE Options called something like
"require variable declaration" (at least thats what it is for 2003, not
sure
in 2007).


This is very good advice.
 
K

Kim M.

Thanks all! I am only looking in one folder for the file names, so the
subfolder issue should not be a problem.

Kim M.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top