MarkS said:
Hi,
I need to get the file names and modified date for file using 'PDA*.PDF' and
searching all subdirectories
What I have tried
Dir() will only give me the file name
Application.FileSearch to so long to do just one directory
Scripting.FileSystemObject I can't get it to use wild cards for the file name
I think the Scripting.FileSystemObject will do what I want but I need some
help to get it to use wild cards and to search the sub directories
I don't think you can use wildcards with Scripting.FileSystemObject to list
the files in subfolders (you can use wildcards with FSO.CopyFile and
FSO.CopyFolder methods for source arguments to copy multiple files or
folders). From what I've seen, you need a recursive procedure that would look
like this:
- get the starting folder
- enumerate the files in the starting folder
- check each file to see if it matches your criteria ("PDA" in the file name
and "PDF" extension)
- if it does, get the file last modified date
- enumerate all the subfolders in the starting directory
- repeat the procedure recursively for each subfolder.
Public oFSO As New FileSystemObject
Sub FsoTest()
Call ListFiles("c:\test\")
End Sub
Sub ListFiles(strFolder As String)
Set oFolder = oFSO.GetFolder(strFolder)
For Each oFile In oFolder.Files
If oFSO.GetExtensionName(oFile.Name) = "txt" Then
Debug.Print oFile.Name, oFile.DateLastModified
End If
Next
For Each oSubfolder In oFolder.SubFolders
Call ListFiles(oSubfolder.Path)
Next
End Sub
I don't have files named PDA*.PDF so I used "txt" extension. You would also
need to modify the code to check if the first three characters of oFile.Name
are "PDA".
If you don't like using recursion, you could use the command line Dir
command with /s switch:
cmd /c dir /b/s c:\test\*.txt
You could redirect the output to WshShellExec.StdOut stream, read the stream
line by line, and use FileSystemObject to get the file object for each line
of output, and the last modified date.
Sub WshShellTest()
Dim oWshShell As New WshShell
Set oStdOut = oWshShell.Exec _
("cmd /c dir /b/s c:\test\*.txt").StdOut
Do While Not oStdOut.AtEndOfStream
Set oFile = oFSO.GetFile(oStdOut.ReadLine())
Debug.Print oFile.Name, oFile.DateLastModified
Loop
End Sub
To run this you need to add reference to Windows Script Host Object Model. I
think that what you need also can be done with VBA Dir() and FileDateTime ()
functions. Try searching this group, I'm sure you will find some examples.