Printing files names from multiple subdirectories

[cfcanter]

I am trying to printout all the *.pdf files in a directory "G:\Accounts". There are multiple subdirectories and each subdirectory may have multiple subdirectories. I've tried to copy code from VBA Developer's Handbook & from utteraccess without luck.

Here is the code from utteraccess


Sub try()
'required to launch SubFileFolderList(varFolder)
pstrPath = "G:\Accounts\"
Call FileFolderList(varFolder)
End Sub


Public Sub FileFolderList(varFolder)
'copied from utteraccess.com
'

http://www.utteraccess.com/forums/printthread.php?Cat=&Board=90&main=

1020831&type=thread
'c. canter 6/21/07
'---------------------------------------------------------

Dim colFolders As New Collection
Dim strName As String

If Right(pstrPath, 1) <> "\" Then pstrPath = pstrPath & "\"
strName = Dir(pstrPath, vbDirectory)
Do While strName <> ""
If (GetAttr(pstrPath & strName) And vbDirectory) = vbDirectory Then
If strName <> "." And strName <> ".." Then colFolders.Add pstrPath
Debug.Print pstrPath & strName
Else
ActiveCell.Offset(0, 1).Value = strName
ActiveCell.Offset(1, 0).Select
End If
strName = Dir
Loop
For Each varFolder In colFolders
Call FileFolderList(varFolder)
Next varFolder
Set colFolders = Nothing

End Sub

This does not appear to be listing the files in the particular subdirectories of G:\Accounts\ I'm trying to search. In Truth, I'm not sure what it is listing.


I also tried

Sub ListSubDirs()
'--------------------------------------------------------------------
'copied from "VBA Developer's Handbook", Ken Getz & Mike Gilbert
'page 661
'c. canter 6/21/07
'--------------------------------------------------------------------
Dim strSubDir, strFile As String
Dim MyAttr As Integer

strPath = "G:\Accounts\"

'Make sure strPath is a directory

'find all the files, including directories

strSubDir = Dir(strPath, vbDirectory)
Do Until strSubDir = ""

If strSubDir <> "." And strSubDir <> ".." Then
ActiveCell.Value = strSubDir
'-------------------------------------------------------------------------
'this section prints all the files in the subdirectory
'from page 656
'-------------------------------------------------------------------------
strSubPath = strPath & strSubDir & "\"
strFile = Dir(strSubPath, vbDirectory)
Do Until strFile = ""
ActiveCell.Offset(1, 0).Select
MyAttr = GetAttr(strFile)

ActiveCell.Offset(0, 1).Value = strFile
strFile = Dir
Loop
'-------------------------------------------------------
ActiveCell.Offset(1, 0).Select
End If

strSubDir = Dir

Loop

End Sub

This routine does not dive into the lower subdirectories.

Any suggestions? Which routine is better? I have to determine if 900 items are in this G:\Accounts\. The plan is to list all the drawings in .pdf file and do a vlookup against the two lists.

Please help. I am really behind the eight ball on this one

Craig Canter
Controller
Elgin IL

 

[Bong]

might this not be more effectively achieved with the shell. DOS already has a subdirectory search feature, with the /s flag, and VBA can use the OS shell.

_________________
Bob Rashkin

 

[Bong]

Here's how I would do it. Somewhere on your system you need to build a .bat file. Let's say you put that in G:\accounts. This file should have the following contents:

g:
cd \accounts
dir *.pdf /s >ds.txt

>ds.txt directs the output to a text file that will be written in g:\accounts.

Then your macro will be something like this:

Sub dirsrch()
    Shell ("g:\accounts\ds.bat")
    Application.Wait (Now + TimeValue("0:00:05"))
    Open "g:\accounts\ds.txt" For Input As #1
    Do While Not EOF(1)
        Line Input #1, a
        'do your stuff here
    Loop
    Close #1
End Sub

Where I say "do your stuff here", it depends on what you want out of the data. The file produced by the dir command looks like:
Volume in drive H is NASVolume
Volume Serial Number is F42F-4958

Directory of H:\

06/22/2007 07:56a 0 ds.txt
1 File(s) 0 bytes

Directory of H:\admin

02/15/2007 03:53p 4,837 argidp.txt
06/23/2006 08:37a 0 tes.txt
06/21/2007 01:53p 1,149 woa23CT.txt
3 File(s) 5,986 bytes

Directory of H:\admin\resumes

04/02/2007 11:56a 201 saberle.txt
1 File(s) 201 bytes

So if you want to keep track of the subdirectories, you'll need to parse out the strings that start with " Directory" and find the path structure. Anyway the most interesting thing to you is always the last element in the line: g:\accounts\blah, or blah.pdf. So you could split the line into words and look at the last one:

        plin = Split(a, " ")
        If a <> "" Then Debug.Print plin(UBound(plin))

_________________
Bob Rashkin

 

[CautionMP]

cfcanter,
I'm guessing that you are trying to use Excel? If your using 2000 through 2003 you may take a look at the [tt]FileSearch[/tt] object (this functionality was removed in Office 2007).

Sub ListFiles()
Dim varFoundFile As Variant
Dim lngFile As Long
With Application.FileSearch
  .Filename = "*.pdf"
  .SearchSubFolders = True
  .LookIn = "C:\Documents and Settings\Owner.Gateway-MX3562\My Documents"
  .Execute
  For Each varFoundFile In .FoundFiles
    'This is where you loop through the files found. varFoundFile will
    'contain the full path & filename for any file that meets the Filename
    'and LookIn criteria
    lngFile = lngFile + 1
    ActiveSheet.Cells(lngFile, 1) = varFoundFile
  Next varFoundFile
  .NewSearch
End With
End Sub

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[fumei]

cfcanter, FileSearch is definitely the way to go. Though I notice you have in your post that you want "to printout all the *.pdf files". Do you mean this literally? You want to actually send all the found PDF files to the printer? CMP's code lists them, but nothing else. Although from looking at your code it does seem you are listing them. Not printing them. "Printout" is a very specific term.

NOTE: FileSearch works the same in Word.

Hey CMP....FileSearch is not in 2007???? Really? What does it have to replace this functionality?

Oh no....let me guess....there isn't anything.

faq219-2884

Gerry
My paintings and sculpture

 

[PHV]

What does it have to replace this functionality?
The MSKB "offers" a work around saying that we just have to play with the FileSystemObject or the Windows Desktop Search ...

http://support.microsoft.com/kb/935402/en-us

http://support.microsoft.com/kb/920229/en-us

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

What does it have to replace this functionality?
Have a look at this class Module:

http://chrisrae.com/vba/routines/clrfilesearch.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886