I'm somewhat of a rookie when it comes to VBA code, but I'm trying to write code that will search my hard drive for sensitive data, such as social security numbers. I've tried the code below with some very strange results. Is my problem with the wildcard characters or is it something else. I've tried a variety of wildcard characters, but I just can't get the correct result. I'm trying to find the SSN in the typical SSN format; ie. 000-00-0000. Any help would be aprreciated.
Sub searchSSN()
Dim i As Long
Dim fs, f
Dim y()
Set fs = CreateObject("Scripting.FileSystemObject")
With Application.FileSearch
.NewSearch
.LookIn = "C:\"
.SearchSubFolders = True
.Filename = "*.*"
.MatchTextExactly = True
.TextOrProperty = "\d{3}-\d{2}-\d{4}"
.MatchAllWordForms = False
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
ReDim y(1 To .FoundFiles.Count, 1 To 7)
For i = 1 To .FoundFiles.Count
Set f = fs.GetFile(.FoundFiles(i))
y(i, 1) = f.Name
y(i, 2) = f.ParentFolder
y(i, 3) = f.DateCreated
y(i, 4) = f.DateLastAccessed
y(i, 5) = f.DateLastModified
y(i, 6) = f.Size
y(i, 7) = f.Type
Next i
Else
MsgBox "There were no files found."
Exit Sub
End If
End With
With Sheets("sheet1")
.[A1:G1] = [{"Name","Folder","Created","Accessed","Modified","Size","Type"}]
.Rows(1).Font.Bold = True
.[A2].Resize(UBound(y, 1), UBound(y, 2)) = y
.Columns.AutoFit
End With
End Sub
Full Version: FileSearch and wildcard characters
Are you using Visual Basic, or are you using Visual Basic for Applications from Access or Excel?
I'm using Visual Basic for Applications in an Excel spreadsheet.
I dont' know what version of Office you are using but, filesearch was discontinued in Office 2007 for Access. I don't know if the same applies for Excel, but it is something you should find out.
Dane
Dane
You posted this in the Visual Basic Forum, which is for VB6 questions. I am moving this to the Excel forum.
I think that your problem might be here:
the TextOrProperty doesn't support regular expressions. You can only use the * or ? wild card characters.
Mabye something like this would work:
.TextOrProperty = "???-??-????"
HTH,
CODE
.TextOrProperty = "\d{3}-\d{2}-\d{4}"
the TextOrProperty doesn't support regular expressions. You can only use the * or ? wild card characters.
Mabye something like this would work:
.TextOrProperty = "???-??-????"
HTH,
I've tried the wildcard characters "???-??-????". I do get returns that include SSN numbers, but I also get returns that do not include SSN numbers. The search seems to ignore the hyphens. I need a way to distinguish the difference between a SSN number and any other 9 digit number.
Norie, I see we have the same reply but that still doesn't solve Dan's issue.
Dan, mabye we incorporate RegExp into the function after the file search is complete. I admit, not very efficient but it just might work.
See what this does for you. You will need a reference to the MS VbScript Regular Expressions 5.5 library.
Let me know if that works.
Dan, mabye we incorporate RegExp into the function after the file search is complete. I admit, not very efficient but it just might work.
See what this does for you. You will need a reference to the MS VbScript Regular Expressions 5.5 library.
CODE
Dim i As Long
Dim fs, f
Dim y()
Dim regex
Set fs = CreateObject("Scripting.FileSystemObject")
Set regex = CreateObject("VBScript.RegExp")
regex.Pattern = "\d{3}-\d{2}-\d{4}"
With Application.FileSearch
.LookIn = "C:\"
.SearchSubFolders = False
.Filename = "*.*"
.MatchTextExactly = True
.TextOrProperty = "???-??-????"
.MatchAllWordForms = False
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
ReDim y(1 To .FoundFiles.Count, 1 To 7)
For i = 1 To .FoundFiles.Count
Set f = fs.GetFile(.FoundFiles(i))
If regex.test(f.Name) = True Then
j = j + 1
y(j, 1) = f.Name
y(j, 2) = f.ParentFolder
y(j, 3) = f.DateCreated
y(j, 4) = f.DateLastAccessed
y(j, 5) = f.DateLastModified
y(j, 6) = f.Size
y(j, 7) = f.Type
End If
Next i
Else
MsgBox "There were no files found."
Exit Sub
End If
End With
With Sheets("sheet1")
.[A1:G1] = [{"Name","Folder","Created","Accessed","Modified","Size","Type"}]
.Rows(1).Font.Bold = True
.[A2].Resize(UBound(y, 1), UBound(y, 2)) = y
.Columns.AutoFit
End With
Dim fs, f
Dim y()
Dim regex
Set fs = CreateObject("Scripting.FileSystemObject")
Set regex = CreateObject("VBScript.RegExp")
regex.Pattern = "\d{3}-\d{2}-\d{4}"
With Application.FileSearch
.LookIn = "C:\"
.SearchSubFolders = False
.Filename = "*.*"
.MatchTextExactly = True
.TextOrProperty = "???-??-????"
.MatchAllWordForms = False
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
ReDim y(1 To .FoundFiles.Count, 1 To 7)
For i = 1 To .FoundFiles.Count
Set f = fs.GetFile(.FoundFiles(i))
If regex.test(f.Name) = True Then
j = j + 1
y(j, 1) = f.Name
y(j, 2) = f.ParentFolder
y(j, 3) = f.DateCreated
y(j, 4) = f.DateLastAccessed
y(j, 5) = f.DateLastModified
y(j, 6) = f.Size
y(j, 7) = f.Type
End If
Next i
Else
MsgBox "There were no files found."
Exit Sub
End If
End With
With Sheets("sheet1")
.[A1:G1] = [{"Name","Folder","Created","Accessed","Modified","Size","Type"}]
.Rows(1).Font.Bold = True
.[A2].Resize(UBound(y, 1), UBound(y, 2)) = y
.Columns.AutoFit
End With
Let me know if that works.
Eric
Isn't the OP looking for values within the file rather than filenames?
Isn't the OP looking for values within the file rather than filenames?
I hope not. Excel would not be the application to perform that type of comparison. I am not sure what app would though...
His original code was just searching through the .Foundfiles collection so I left it at that.
His original code was just searching through the .Foundfiles collection so I left it at that.
Eric
I think the OP does want to do that.
The reason why is the use of TextOrProperty which is described thus in help.
I agree FileSearch or Excel really aren't the tools for this sort of thing, but then again we don't know exactly what the OP is trying to achieve.
Another thing is that FileSearch has been dropped by Microsoft so using it could just be storing up future problems.
I think the OP does want to do that.
The reason why is the use of TextOrProperty which is described thus in help.
QUOTE
Returns or sets the word or phrase to be searched for, in either the body of a file or the file's properties, during the file search. The word or phrase can include the * (asterisk) or ? (question mark) wildcard character
I agree FileSearch or Excel really aren't the tools for this sort of thing, but then again we don't know exactly what the OP is trying to achieve.
Another thing is that FileSearch has been dropped by Microsoft so using it could just be storing up future problems.
Ah, then mabye the ???-??-???? was actually working and OP just didn't realize it . I was unaware that filesearch would search the body of the doc. I always thought it was just looking at the file's properties. Learn something new...
Eric
That capability is rarely used and I don't think it's too reliable.
In fact that and other 'flaky' behaviour of FileSearch is apparently the
reason it's been dropped.
That capability is rarely used and I don't think it's too reliable.
In fact that and other 'flaky' behaviour of FileSearch is apparently the
reason it's been dropped.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.