Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 26

Thread: Solved: File Search in Excel - hidden files?

  1. #1
    VBAX Regular
    Joined
    Jul 2007
    Posts
    71
    Location

    Solved: File Search in Excel - hidden files?

    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.

  2. #2
    VBAX Regular Ebrow's Avatar
    Joined
    May 2007
    Posts
    67
    Location
    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.

    [VBA]

    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
    [/VBA]
    Nothing is impossible, just it hasn't been thought of yet.

  3. #3
    VBAX Regular
    Joined
    Jul 2007
    Posts
    71
    Location
    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.

    [VBA]

    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

    [/VBA]

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    This will

    [vba]

    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
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Regular
    Joined
    Jul 2007
    Posts
    71
    Location
    That would work it appears. However, how can I have that search for a specific file type? i.e. *.doc, *.xls, etc?

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Sorry, I am not exactly sure what you mean by that.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    VBAX Regular
    Joined
    Jul 2007
    Posts
    71
    Location
    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.

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    [vba]


    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
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    VBAX Regular
    Joined
    Jul 2007
    Posts
    71
    Location
    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.

  10. #10
    VBAX Regular
    Joined
    Jul 2007
    Posts
    71
    Location
    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.

  11. #11
    VBAX Regular Ebrow's Avatar
    Joined
    May 2007
    Posts
    67
    Location
    Give this a shot. not as nice as XLD's method though :-)

    [VBA]

    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



    [/vBA]
    Nothing is impossible, just it hasn't been thought of yet.

  12. #12
    VBAX Regular
    Joined
    Jul 2007
    Posts
    71
    Location
    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.

  13. #13
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    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.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  14. #14
    VBAX Regular
    Joined
    Jul 2007
    Posts
    71
    Location
    Quote Originally Posted by lucas
    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.

  15. #15
    VBAX Regular Ebrow's Avatar
    Joined
    May 2007
    Posts
    67
    Location
    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. :-)
    Nothing is impossible, just it hasn't been thought of yet.

  16. #16
    VBAX Regular
    Joined
    Jul 2007
    Posts
    71
    Location
    Quote Originally Posted by Ebrow
    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.

  17. #17
    VBAX Regular Ebrow's Avatar
    Joined
    May 2007
    Posts
    67
    Location
    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.
    Nothing is impossible, just it hasn't been thought of yet.

  18. #18
    VBAX Regular
    Joined
    Jul 2007
    Posts
    71
    Location
    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.

  19. #19
    VBAX Regular
    Joined
    Jul 2007
    Posts
    71
    Location
    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.

  20. #20
    VBAX Regular
    Joined
    Jul 2007
    Posts
    71
    Location
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •