Solved: How to search for files other than MS Office files

[malik641]

I was trying to do a search using the FileSearch object, but when I finished the code I came to realize that the FileSearch object only looks for MS office files. So how do you search for other types of files (such as PDF, in my case)?

TIA

[austenr]

Hi,

Try this spreadsheet. Some of the code might be useful.

[Bob Phillips]

[vba]

Dim FSO As Object

Sub ProcessFiles()
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Dim this As Workbook
Dim cnt As Long

Set FSO = CreateObject("Scripting.FileSystemObject")

Set this = ActiveWorkbook
sFolder = "C:\MyTest"
If sFolder <> "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
cnt = 1
For Each file In Files
Select Case Right(file.Name, 3)
Case "wav": MsgBox file.Name
Case "txt": MsgBox file.Name
Case "pdf": MsgBox file.Name
End Select
Next file

End If ' sFolder <> ""

End Sub
[/vba]

[malik641]

Bob and austenr, thank you both. Both solutions solve my problem. I ended up using Bob's code because I was at work and didn't want to download anything (IT could be spying on me, lol). Here's what I ended up with:

[vba]Private Sub UserForm_Initialize()
Dim i As Long
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Dim cnt As Long

Dim rngHCE As Range, pdfFile As Variant
Dim HCE_Num As String, HoldADD As String, LookIN As String, Att As String

Set rngHCE = Sheets("Jobs").Range("D" & Selection.Row)
HCE_Num = rngHCE.Text

Att = rngHCE.HyperLinks(1).Address
HoldADD = WorksheetFunction.Substitute(Att, "\", "|", Len(Att) - Len(WorksheetFunction.Substitute(Att, "\", "")))
sFolder = Left(HoldADD, InStr(1, HoldADD, "|") - 1)

Set FSO = CreateObject("Scripting.FileSystemObject")

If sFolder <> "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
cnt = 1
For Each file In Files
If Right(file.Name, 3) = "pdf" And InStr(1, file.Name, HCE_Num, vbTextCompare) <> 0 Then
lstPDF.AddItem file.Name
End If
Next file
End If

If lstPDF.ListCount = 0 Then
MsgBox "No Items found", vbOKOnly, "Items"
'Unload Me
End If

End Sub[/vba]
This is just a userform that lists the PDF files of an AutoCAD plot of a specified file name (the hyperlink's text) that resides in a specific folder (the hyperlink's address) into a listbox. I then choose whatever one's I want and it inserts it into a new Outlook email and writes the body I need and the recipient's address and the correct subject (all of this with the info found in the cell with the hyperlink).

I'll end up modifying this a bit more cause sometimes I need to email the CAD file itself. I think I'll also add another listbox with the contacts I have in my contact list so I don't limit myself to emailing only my engineer.



But now I'm confused...I thought that the FileSearch object only can search for MS office files??? Well, now I know, huh? Thanks for the workbook austenr, the code was useful...it proved me wrong

[malik641]

Hey I was just testing austenr's way of code. And it worked in Excel 2003, but not 2000? Here's the code. It skips right over the .Execute IF statement:

[vba]
Private Sub UserForm_Initialize()
Dim I As Long
Dim FileName As Variant
Dim rngHCE As Range, pdfFile As Variant
Dim HCE_Num As String, HoldADD As String, LookIN As String, Att As String
'Get HCE num cell
Set rngHCE = Sheets("Jobs").Range("D" & Selection.Row)
HCE_Num = rngHCE.Text
'Use the hyperlink's address and break it down to get the folder it resides in
Att = rngHCE.HyperLinks(1).Address
HoldADD = WorksheetFunction.Substitute(Att, "\", "|", Len(Att) - Len(WorksheetFunction.Substitute(Att, "\", "")))
sFolder = Left(HoldADD, InStr(1, HoldADD, "|") - 1)
'Perform a search to look for the pdf files. Then check to see if they have
'the HCE number within the file name...if yes, add it to the list box.
With Application.FileSearch
.NewSearch
.LookIN = sFolder
.SearchSubFolders = False
.FileName = "pdf"

If .Execute() > 0 Then
For I = 1 To .FoundFiles.Count
If InStr(1, .FoundFiles(I), HCE_Num, vbTextCompare) <> 0 Then
FileName = Split(.FoundFiles(I), "\")
lstPDF.AddItem FileName(UBound(FileName))
End If
Next
End If
End With
'If no items were found in the file search
If lstPDF.ListCount = 0 Then
MsgBox "No Items found", vbOKOnly, "Items"
End If
End Sub
[/vba]
Did I miss something? Or does it not work for Excel 2000?

[Bob Phillips]

That's FileSearch for you, flaky.

[malik641]

That's beat. I wish my company would upgrade already. It's 2006 for crying out loud!!!!

[mdmackillop]

Hi Joseph
I would add Option Compare Text to your coding as a precaution. I don't know if the search is case sensitive, but there might be some .PDF files in there.
Regards
MD

[malik641]

Thanks MD. It's there, though :

[vba]
Option Explicit
Option Compare Text
Dim sFolder As String
[/vba]