none
.Filesearch help RRS feed

  • Question

  • Ok i know there are other solutions similar but i am having trouble understanding how to convert my code to what i ned it to do. Honestly i have no idea what i am doing... so please help. This was origionally written in Excel2000 and I need it to work in Excel2007.

    Sub InitFileNamesArray(FilePath As String, FileSearchString As String, FileNamesArray() As Array, NumFiles As Integer, SearchSubfolders As Boolean)
    Dim fs as Object
    Dim i as Integer

    Set fs = Application.FileSearch

    With fs
     Dim Path As String
     .LookIn = FilePath
     .FileName = FileSearchString
     .SearchSubfolders = SearchSubfolders
     .FileType = msoFileTypeAllFiles
     
     If .Execute > 0 Then
      NumFiles = .FoundFiles.Count
      For i = 1 To .FoundFiles.Count
       FileNamesArray(i) = .FoundFiles(i)
      Next i
     Else
      NumFiles = 0
     End If
    End With

     

    Thanks in Advance,

    C

    Thursday, June 30, 2011 12:01 PM

Answers

  • This requires a reference to MS scripting runtime. I think it should work as a drop in replacement to your sub.

    Bernie

    Option Explicit
    Dim objFSO As Scripting.FileSystemObject
    Dim objFolder As Scripting.Folder
    Dim colFiles As Scripting.Files
    Dim objfile As Scripting.File
    Dim Subfolder As Scripting.Folder


    Sub TestFSO()
        Dim myPath As String
        Dim mySString As String
        Dim myArray() As String
        Dim myFiles As Integer
        Dim mySearchSub As Boolean
        Dim i As Integer
       
        myPath = ThisWorkbook.Path
        mySString = "*k.xls"
        mySearchSub = True

        InitFileNamesArray myPath, mySString, myArray(), myFiles, mySearchSub
       
        MsgBox myFiles & " files were found."
       
        For i = LBound(myArray) To UBound(myArray) - 1
            MsgBox myArray(i)
        Next i

    End Sub


    Sub InitFileNamesArray(FilePath As String, _
                           FileSearchString As String, _
                           FileNamesArray() As String, _
                           NumFiles As Integer, _
                           SearchSubfolders As Boolean)
        ReDim FileNamesArray(1 To 1)
        Set objFSO = CreateObject("Scripting.FileSystemObject")
        Set objFolder = objFSO.GetFolder(FilePath)
        Set colFiles = objFolder.Files
        For Each objfile In colFiles
            If objfile.Name Like FileSearchString Then
                FileNamesArray(UBound(FileNamesArray)) = objfile.Path
                ReDim Preserve FileNamesArray(1 To UBound(FileNamesArray) + 1)
            End If
        Next

        If SearchSubfolders Then ShowSubFolders objFolder, FileNamesArray(), FileSearchString

        NumFiles = UBound(FileNamesArray) - 1
    End Sub

    Sub ShowSubFolders(Folder As Scripting.Folder, FileNamesArray() As String, FileSearchString As String)
        For Each Subfolder In Folder.SubFolders
            Set objFolder = objFSO.GetFolder(Subfolder.Path)
            Set colFiles = objFolder.Files
            For Each objfile In colFiles
                If objfile.Name Like FileSearchString Then
                FileNamesArray(UBound(FileNamesArray)) = objfile.Path
                ReDim Preserve FileNamesArray(1 To UBound(FileNamesArray) + 1)
                End If
            Next
            ShowSubFolders Subfolder, FileNamesArray(), FileSearchString
        Next
    End Sub

     


    HTH, Bernie
    Thursday, June 30, 2011 3:00 PM

All replies

  • At this website there is a an option to sort the questions. 
    If you sort by "total views" the resulting post at the top is "Application.FileSearch in Excel 2007".  Within the 83 responses there must by a pony someplace.

    Also, my response (at this website) in the post "running a macro in subdirectories" yesterday (June 29th) may be of help to you.
    '---
    Jim Cone
    Portland, Oregon USA
    http://www.mediafire.com/PrimitiveSoftware
    (List Files XL add-in:  finds and lists files/folders with hyperlinks)
    Thursday, June 30, 2011 2:40 PM
  • This requires a reference to MS scripting runtime. I think it should work as a drop in replacement to your sub.

    Bernie

    Option Explicit
    Dim objFSO As Scripting.FileSystemObject
    Dim objFolder As Scripting.Folder
    Dim colFiles As Scripting.Files
    Dim objfile As Scripting.File
    Dim Subfolder As Scripting.Folder


    Sub TestFSO()
        Dim myPath As String
        Dim mySString As String
        Dim myArray() As String
        Dim myFiles As Integer
        Dim mySearchSub As Boolean
        Dim i As Integer
       
        myPath = ThisWorkbook.Path
        mySString = "*k.xls"
        mySearchSub = True

        InitFileNamesArray myPath, mySString, myArray(), myFiles, mySearchSub
       
        MsgBox myFiles & " files were found."
       
        For i = LBound(myArray) To UBound(myArray) - 1
            MsgBox myArray(i)
        Next i

    End Sub


    Sub InitFileNamesArray(FilePath As String, _
                           FileSearchString As String, _
                           FileNamesArray() As String, _
                           NumFiles As Integer, _
                           SearchSubfolders As Boolean)
        ReDim FileNamesArray(1 To 1)
        Set objFSO = CreateObject("Scripting.FileSystemObject")
        Set objFolder = objFSO.GetFolder(FilePath)
        Set colFiles = objFolder.Files
        For Each objfile In colFiles
            If objfile.Name Like FileSearchString Then
                FileNamesArray(UBound(FileNamesArray)) = objfile.Path
                ReDim Preserve FileNamesArray(1 To UBound(FileNamesArray) + 1)
            End If
        Next

        If SearchSubfolders Then ShowSubFolders objFolder, FileNamesArray(), FileSearchString

        NumFiles = UBound(FileNamesArray) - 1
    End Sub

    Sub ShowSubFolders(Folder As Scripting.Folder, FileNamesArray() As String, FileSearchString As String)
        For Each Subfolder In Folder.SubFolders
            Set objFolder = objFSO.GetFolder(Subfolder.Path)
            Set colFiles = objFolder.Files
            For Each objfile In colFiles
                If objfile.Name Like FileSearchString Then
                FileNamesArray(UBound(FileNamesArray)) = objfile.Path
                ReDim Preserve FileNamesArray(1 To UBound(FileNamesArray) + 1)
                End If
            Next
            ShowSubFolders Subfolder, FileNamesArray(), FileSearchString
        Next
    End Sub

     


    HTH, Bernie
    Thursday, June 30, 2011 3:00 PM
  • Ok i know there are other solutions similar but i am having trouble understanding how to convert my code to what i ned it to do. Honestly i have no idea what i am doing... so please help. This was origionally written in Excel2000 and I need it to work in Excel2007.

    Sub InitFileNamesArray(FilePath As String, FileSearchString As String, FileNamesArray() As Array, NumFiles As Integer, SearchSubfolders As Boolean)
    Dim fs as Object
    Dim i as Integer

    Set fs = Application.FileSearch

    With fs
     Dim Path As String
     .LookIn = FilePath
     .FileName = FileSearchString
     .SearchSubfolders = SearchSubfolders
     .FileType = msoFileTypeAllFiles
     
     If .Execute > 0 Then
      NumFiles = .FoundFiles.Count
      For i = 1 To .FoundFiles.Count
       FileNamesArray(i) = .FoundFiles(i)
      Next i
     Else
      NumFiles = 0
     End If
    End With

     

    Thanks in Advance,

    C


    I don't know how your code worked because there is no valid construct like As Array!

    That said, and recognizing you have received at least one other suggestion, you should be able to adapt the 'black box solution' at

    Process all files in a folder

    http://www.tushar-mehta.com/publish_train/xl_vba_cases/1035%20vba-process%20all%20files.htm

    Modify the processOneFile procedure to create the desired array.

     


    Tushar Mehta (Technology and Operations Consulting)
    www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present
    Thursday, June 30, 2011 4:55 PM