PDA

View Full Version : Solved: File Search in Excel - hidden files?



OdiN
07-09-2007, 08:39 AM
I wrote some VBA code in a workbook which will search a computers hard drive for .xls and .doc files and report the number and location of each file.

This is being used for audit purposes and I want it to be as easy to run as possible. I have a sheet that reports with two buttons on it and that's all the user has to do is click a button and wait.

Now, if folders or files are hidden, the file search will not search in them. Is there a way in Excel to specify to search for hidden files as well? I have searched around but I haven't found anything.

I know there is the SetAttr function which I could use to hide/unhide folders but the idea of the audit is that someone may hide a folder that I don't know to unhide so that won't work.

Any suggestions are welcome.

Ebrow
07-09-2007, 09:28 AM
Hi,

If you use the WSH it will find all files even if they are invisible. To use this code you need to reference the Windows Script Host Model.



Dim myFS As FileSystemObject: Set myFS = CreateObject("Scripting.FileSystemObject")
Dim myFolder
Dim myFile As File
Dim myFiles
'
'Insert code here for each folder
'
checkFolder = "C:\Test\"
Set myFolder = myFS.GetFolder(checkFolder)
Set myFiles = myFolder.Files
For Each myFile In myFiles
MsgBox myFile.Name
Next

OdiN
07-09-2007, 09:46 AM
Will that do subfolders, etc?

The code I have runs on a per-file basis. Let me post the code so you can see what I am doing...I don't know that what you posted will work quite the same. I am not that familiar with WSH.



Excel.Application.FileSearch.NewSearch
Excel.Application.FileSearch.LookIn = "C:\"
Excel.Application.FileSearch.SearchSubFolders = True
Excel.Application.FileSearch.Filename = "*.doc"
Excel.Application.FileSearch.Execute

Worksheets("Audit Search Word Files").Activate

If Excel.Application.FileSearch.Execute() > 0 Then

Worksheets("Audit Search Word Files").Range("A1").Value = "Files Found: " & Excel.Application.FileSearch.FoundFiles.Count

For i = 1 To Excel.Application.FileSearch.FoundFiles.Count

ActiveSheet.Range("A" & (i + 2)).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=Excel.Application.FileSearch.FoundFiles(i), TextToDisplay:=Excel.Application.FileSearch.FoundFiles(i)
With Selection.Font
.Name = "Arial"
.Size = 8
End With

Next i

Else

Worksheets("Audit Search Word Files").Range("A1").Value = "Files Found: 0"

End If

Bob Phillips
07-09-2007, 03:04 PM
This will



Private myFS As Object

Sub IterateFiles()

Set myFS = CreateObject("Scripting.FileSystemObject")
CheckFolder "C:\Test\"
End Sub

Sub CheckFolder(ByVal path As String)
Dim myFolder As Object
Dim myFile As Object
Dim mySubFolder As Object
Dim myFiles

Set myFolder = myFS.Getfolder(path)
Set myFiles = myFolder.Files

For Each myFile In myFiles
MsgBox myFile.Name
Next

For Each mySubFolder In myFolder.Subfolders
CheckFolder mySubFolder.path
Next

Set myFolder = Nothing
Set myFiles = Nothing
End Sub

OdiN
07-09-2007, 03:18 PM
That would work it appears. However, how can I have that search for a specific file type? i.e. *.doc, *.xls, etc?

Bob Phillips
07-09-2007, 03:31 PM
Sorry, I am not exactly sure what you mean by that.

OdiN
07-09-2007, 03:38 PM
Well the search needs to search for only files with the extension .xls or .doc for two different searches. Then a search for everything on a drive.

For Excel and Document files I would run this code two seperate times:

Once it would check through the C: drive and put the path and filename to each Excel file on a worksheet.

Second it would do the same but for Word files.

Bob Phillips
07-09-2007, 03:45 PM
Private myFS As Object

Const FILE_TYPE as String = "*Excel*"
Sub IterateFiles()

Set myFS = CreateObject("Scripting.FileSystemObject")
CheckFolder "C:\Test\"
End Sub

Sub CheckFolder(ByVal path As String)
Dim myFolder As Object
Dim myFile As Object
Dim mySubFolder As Object
Dim myFiles

Set myFolder = myFS.Getfolder(path)
Set myFiles = myFolder.Files

For Each myFile In myFiles
If myFile.Type Like FILE_TYPE Then
MsgBox myFile.Name
End If
Next

For Each mySubFolder In myFolder.Subfolders
CheckFolder mySubFolder.path
Next

Set myFolder = Nothing
Set myFiles = Nothing
End Sub

OdiN
07-09-2007, 03:57 PM
I changed the file type to "*.xls" and then for the If statement used myFile.Name instead of .Type.

This is not working however. I am getting permission denied errors and it is not finding all of the .xls files that are on my drive. Excel's FileSearch method did not error on these files.

For example, there are .xls files under C:\Documents and Settings\Administrator\templates but it does not show them.

OdiN
07-09-2007, 04:02 PM
Using Excel's FileSearch I get all 130 .xls documents.

Using the WSH method here I get only 14 documents. This is likely due to permission errors, which I don't know why I would get. I can see to some areas (System Volume Information folder) but not to reduce the number of found files so dramatically.

Ebrow
07-10-2007, 11:50 AM
Give this a shot. not as nice as XLD's method though :-)



Private myFS As Object

Const FILE_TYPE As String = "XLS"
Sub IterateFiles()

Set myFS = CreateObject("Scripting.FileSystemObject")
CheckFolder "C:\Test\"
End Sub

Sub CheckFolder(ByVal path As String)
Dim myFolder As Object
Dim myFile As Object
Dim mySubFolder As Object
Dim myFiles

Set myFolder = myFS.Getfolder(path)
Set myFiles = myFolder.Files

For Each myFile In myFiles
If ucase(right(myFile.name,3)) = FILE_TYPE Then
MsgBox myFile.Name
End If
Next

For Each mySubFolder In myFolder.Subfolders
CheckFolder mySubFolder.path
Next

Set myFolder = Nothing
Set myFiles = Nothing
End Sub

OdiN
07-10-2007, 12:03 PM
Thanks ebrow - I had already devised a method of searching just .xls files using the FILE_TYPE "*.xls" and then If myFile.Name Like FILE_TYPE Then...


I have everything working with this code, EXCEPT it will not search all the directories on my system. I get permission errors which I do not understand why I would - it is probably related to the actual WSH accessing the files and not Excel. Probably uses a different method to access the disk through the OS and causes this issue. I do not know how to resolve it. It would work if this could be worked out, otherwise I have to stick with my current Excel search.

I'm just trying to make this as user-proof as possible and if they don't have to go into the folder options and enable showing of all hidden files, then that would be best.

lucas
07-10-2007, 12:07 PM
One thing you should take into consideration. I believe that .FileSearch is not available starting with Office 2007 so FSO should be considered as suggested.

OdiN
07-10-2007, 12:41 PM
One thing you should take into consideration. I believe that .FileSearch is not available starting with Office 2007 so FSO should be considered as suggested.

I would be happy to use this code if it would work how I need it to work. It probably can but I just don't know how. I do not know as much about WSH coding.

We have no plans for updating to Office 2007.

Ebrow
07-10-2007, 01:37 PM
I would put a message box after the checkfolder mySubFolder.path line i.e msgbox mySubFolder.path and compare its output to the path you are searching. I am thinking that you may need to add an additional "\" at the end of that path before it looks at the files. :-)

OdiN
07-10-2007, 01:55 PM
I would put a message box after the checkfolder mySubFolder.path line i.e msgbox mySubFolder.path and compare its output to the path you are searching. I am thinking that you may need to add an additional "\" at the end of that path before it looks at the files. :-)

No it does find files in multiple subfolders so that can't be the case. It will just progress to a certain point and then error out with permission errors when it tries to access files. It seems to start in the documents and settings folder for any account other than my own. I am admin on local computer so that is why I do not understand why it cannot search in these folders while the Excel FileSearch method can.

Ebrow
07-10-2007, 02:05 PM
You are right. The code works perfect for me.

Is it a system folder or just a random folder that it stops on. Is it a sever drive or the local disk?

Might help figure out the issue.

OdiN
07-10-2007, 02:29 PM
It's very odd. It has errors on subfolders if they are empty. It has errors on files which I can access just fine. But I don't know what errors at the time.

Whatever these first errors are, it passes over them and continues to run, even with error handling disabled.

Running through to try to find the first spot it has the permission denied error which after that it doesn't find any other files, even if I have it continue after errors.

Oh and this is all on a local disk. I will be doing some to a mapped drive if I can get this working.

OdiN
07-10-2007, 02:44 PM
Found another issue where it finds .xls files but doesn't report them.

in the Documents and Settings/Default User/Templates folder...the Default User folder is hidden.

OdiN
07-10-2007, 03:24 PM
I found the issue....or at least part of it. It errors with permission denied when it hits System Volume Information.

Once I get that out of the way there is just the issue of it not finding as many .xls files as there are on the drive.

I also found a DumbMistake™ which was causing some of the files not to be notated, but even after fixing that it doesn't show every one.

OdiN
07-11-2007, 07:58 AM
Update:

The Excel FileSearch method includes files in the OFFICE11 folder. The WSH method does not. I do not know why it wouldn't include those.

Also the WSH method includes files in Temporary Internet Files while the Excel FileSearch method does not.

Everything else is accounted for now. Generally I don't care about the files in the OFFICE11 folder. Just default stuff that's there so unless someone specifically hid some file there they didn't want to be found, it won't be a problem. I doubt anyone would do that.


Now I just need to flesh out the rest of the program and get it to search for the different file types that I want and sort them on different worksheets.

Is there a way to get a count of the number of files with the WSH method without having to resort to counting however many cells end up being filled?

OdiN
07-11-2007, 08:07 AM
Okay got it mostly running. Gotta do a few things but I think I've gotten everything I need to work.

Ebrow
07-12-2007, 12:34 PM
How did you solver your problem? I've been racking my brain to think what it could have been.

OdiN
07-12-2007, 09:30 PM
Well the dumb mistake was that I was resetting x (my counter to tell which excel cell to put stuff in) in the wrong spot, and it was resetting with each new subfolder. Kicked myself for that one, but after looking at code all day small stuff like that is harder to spot.

My other issue was that I was searching for a file like *.xls. The other files not showing up were .XLS so I just did an LCase(myFile.Name) to fix that.

So it's doing well. I have this audit search going through and finding xls/doc/pdf/exe/bat/vbs/mde/mdb files. Got various info on each item, a link to open the file or folder and I just added in a function to enumerate installed printers and mapped network drives, pull the user and computer name and date/time stamp it.

I'm just trying to think of other useful information to audit.

I'm trying to find a list of event ID's so that I can audit for certain ID's (I.E. improper shutdowns, stuff of that nature) but I can't find a list, only places you can search if you have the ID.

johnske
07-12-2007, 09:54 PM
...My other issue was that I was searching for a file like *.xls. The other files not showing up were .XLS so I just did an LCase(myFile.Name) to fix that...use Option Compare Text at the head of your code module to stop such issues arising :)

OdiN
07-12-2007, 09:58 PM
Thanks for the tip johnske. I hadn't come across that bit of code before.