Results 1 to 5 of 5

Excel 2010: VBA replacement for Application.FileSearch

This is a discussion on Excel 2010: VBA replacement for Application.FileSearch within the Excel Questions forums, part of the Question Forums category; Hi! First off I am a noob at VBA and I have been trying to fix this issue myself by ...

  1. #1
    New Member
    Join Date
    Jun 2012
    Posts
    5

    Default Excel 2010: VBA replacement for Application.FileSearch

    Hi!

    First off I am a noob at VBA and I have been trying to fix this issue myself by reading other threads. Sucks Application.Filesearch is gone, I could at least figure that coding out. It is not going very well. The following code is pasted below. If somebody is kind enough to please provide me a solution for this code. Many thanks in advance!!!



    With Application.FileSearch
    .NewSearch
    .LookIn = Path4
    .FileType = msoFileTypeWordDocuments
    If .Execute > 0 Then
    'if word docs are present in the folder
    Workbooks.Open Main4 & Form4
    Application.Run "'C:\MISC\Macro.xls'!Module.Start"
    Workbooks(Form4).Close savechanges:=True
    Workbooks(FormResults4).Close savechanges:=True
    Else
    End If
    End With

  2. #2
    Board Regular
    Join Date
    Dec 2011
    Posts
    971

    Default Re: Excel 2010: VBA replacement for Application.FileSearch

    Hi vendetta,

    Here is a function (FindFiles) that searches the specified folder (and sub-folders if required) for the specified file spec and returns an array of files as well as the number of found files. You can find detailed explanation on how to use the function in the beginning of the code
    Code:
    Option Explicit
    
    Function FindFiles(ByVal sPath As String, _
        ByRef sFoundFiles() As String, _
        ByRef iFilesFound As Integer, _
        Optional ByVal sFileSpec As String = "*.*", _
        Optional ByVal blIncludeSubFolders As Boolean = False) As Boolean
    '
    ' FindFiles
    ' ---------
    ' Find all files matching the specified file spec, starting from the specified path
    ' and search subfolder as required.
    '
    ' Parameters
    ' ----------
    ' sPath (String): Startup folder, e.g. "C:\Users\Username\Documents"
    '
    ' sFoundFiles (String): Two dimensional array to store the path and name of found files.
    '   The dimension of this array is (1 To 2, 1 To nnn), where nnn is the number of found
    '   files. The elements of the array are:
    '      sFoundFiles(1, xxx) = File path     (xxx = 1 to nnn)
    '      sFoundFiles(2, xxx) = File name
    '
    ' iFilesFound (Integer): Number of files found.
    '
    ' sFileSpec (String): Optional parameter with default value = "*.*"
    '
    ' blIncludeSubFolders (Boolean): Optional parameter with default value = False
    '   (which means sub-folders will not be searched)
    '
    ' Return values
    ' -------------
    ' True: One or more files found, therefore
    '   sFoundFiles = Array of paths and names of all found files
    '   iFilesFound = Number of found files
    ' False: No files found, therefore
    '   iFilesFound = 0
    '
    ' **********************************  Important Note  **********************************
    '
    ' When searching for *.xls, FindFiles returns, in addition to xls files, xls* (not xls?)
    ' files (e.g. xlsX, xlsM, xlsWhatever, etc.). The reason is that FindFiles uses the Dir
    ' function and these files are returned by Dir! The most interesting thing here is that
    ' Windows search (including DOS DIR command) returns the same! It seems Excel Dir uses
    ' Windows search without any further checking or refinements.
    '
    ' This is also true for *.doc and *.ppt files. Actually, this is true whenever a
    ' three-character file extension is specified; *.txt, *.pdf, *.x?s, etc.
    '
    ' Moreover, if the last character of the specified extension was a question mark (?) or
    ' an asterisk (*), the returned files would be the same (e.g. *.txt? and *.txt* return
    ' the same files). This means, files with more than four-character extension are returned
    ' in both cases. This is exactly the same behaviour when specifying three-character
    ' extension (*.txt)ůso weird!
    '
    ' The aforementioned behaviour was observed in Windows 7 using Excel 2010 (mostly, Excel
    ' is not a key player here).
    '
    ' Not everything is covered in this note as further tests might reveal more. So, keep
    ' these things in mind when using Dir or FindFile.
    '
    ' Constructive comments and Reporting of bugs would be appreciated.
    '
    ' **************************************************************************************
    '
    ' Using the function (sample code)
    ' --------------------------------
    ' Dim iFilesNum As Integer
    ' Dim iCount As Integer
    ' Dim sMyFiles() As String
    ' Dim blFilesFound As Boolean
    '
    ' blFilesFound = FindFiles("C:\Users\Username\Documents", _
    '     sMyFiles, iFilesNum, "*.xls", True)
    ' If blFilesFound Then
    '     For iCount = 1 To iFilesNum
    '         MsgBox "Path: " & sMyFiles(1, iCount) & vbNewLine & _
    '             vbNewLine & "File name: " & sMyFiles(2, iCount), _
    '             vbInformation, "Files Found"
    '     Next
    ' End If
    '
    
        Dim iCount As Integer           '* Multipurpose counter
        Dim sFileName As String         '* Found file name
        '*
        '* FileSystem objects
        Dim oFileSystem As Object, _
            oParentFolder As Object, _
            oFolder As Object
    
        Set oFileSystem = CreateObject("Scripting.FileSystemObject")
        On Error Resume Next
        Set oParentFolder = oFileSystem.GetFolder(sPath)
        If oParentFolder Is Nothing Then
            FindFiles = False
            On Error GoTo 0
            Set oParentFolder = Nothing
            Set oFileSystem = Nothing
            Exit Function
        End If
        sPath = IIf(Right(sPath, 1) = "\", sPath, sPath & "\")
        '*
        '* Find files
        sFileName = Dir(sPath & sFileSpec, vbNormal)
        Do While sFileName <> ""
            iCount = UBound(sFoundFiles, 2)
            iCount = iCount + 1
            ReDim Preserve sFoundFiles(1 To 2, 1 To iCount)
            sFoundFiles(1, iCount) = sPath
            sFoundFiles(2, iCount) = sFileName
            sFileName = Dir()
        Loop
        If blIncludeSubFolders Then
            '*
            '* Select next subforbers
            For Each oFolder In oParentFolder.SubFolders
                FindFiles oFolder.Path, sFoundFiles, iFilesFound, sFileSpec, blIncludeSubFolders
            Next
        End If
        FindFiles = UBound(sFoundFiles, 2) > 0
        iFilesFound = UBound(sFoundFiles, 2)
        On Error GoTo 0
        '*
        '* Clean-up
        Set oFolder = Nothing
        Set oParentFolder = Nothing
        Set oFileSystem = Nothing
    
    End Function

  3. #3
    Board Regular
    Join Date
    Dec 2011
    Posts
    971

    Default Re: Excel 2010: VBA replacement for Application.FileSearch

    Here is a new version of FindFile, which is far more solid, reliable and easy to use than the previous one posted earlier, as it doesn't rely on the Dir function. Please replace the previous code with this one.

    Please read the instructions on how to use it in the beginning of the code.
    Code:
    Option Explicit
    
    '*
    '* Properties that will be collected for each found file
    '*
    Type FoundFileInfo
        sPath As String
        sName As String
    End Type
    
    Function FindFiles(ByVal sPath As String, _
        ByRef recFoundFiles() As FoundFileInfo, _
        ByRef iFilesFound As Integer, _
        Optional ByVal sFileSpec As String = "*.*", _
        Optional ByVal blIncludeSubFolders As Boolean = False) As Boolean
    '
    ' FindFiles
    ' ---------
    ' Finds all files matching the specified file spec starting from the specified path and
    ' searches sub-folders if required.
    '
    ' Parameters
    ' ----------
    ' sPath (String): Start-up folder, e.g. "C:\Users\Username\Documents"
    '
    ' recFoundFiles (User-defined data type): a user-defined dynamic array to store the path
    ' and name of found files. The dimension of this array is (1 To nnn), where nnn is the
    ' number of found files. The elements of this array are:
    '   .sPath (String) = File path
    '   .sName (String) = File name
    '
    ' iFilesFound (Integer): Number of files found.
    '
    ' sFileSpec (String): Optional parameter with default value = "*.*"
    '
    ' blIncludeSubFolders (Boolean): Optional parameter with default value = False
    '   (which means sub-folders will not be searched)
    '
    ' Return values
    ' -------------
    ' True: One or more files found, therefore
    '   recFoundFiles = Array of paths and names of all found files
    '   iFilesFound = Number of found files
    ' False: No files found, therefore
    '   iFilesFound = 0
    '
    ' Using the function (sample code)
    ' --------------------------------
    '    Dim iFilesNum As Integer
    '    Dim iCount As Integer
    '    Dim recMyFiles() As FoundFileInfo
    '    Dim blFilesFound As Boolean
    '
    '    blFilesFound = FindFiles("C:\Users\MBA\Desktop", _
    '        recMyFiles, iFilesNum, "*.txt?", True)
    '    If blFilesFound Then
    '        For iCount = 1 To iFilesNum
    '            With recMyFiles(iCount)
    '                MsgBox "Path:" & vbTab & .sPath & _
    '                    vbNewLine & "Name:" & vbTab & .sName, _
    '                    vbInformation, "Found Files"
    '            End With
    '        Next
    '    Else
    '        MsgBox "No file(s) found matching the specified file spec.", _
    '            vbInformation, "File(s) not Found"
    '    End If
    '
    '
    ' Constructive comments and Reporting of bugs would be appreciated.
    '
    
        Dim iCount As Integer           '* Multipurpose counter
        Dim sFileName As String         '* Found file name
        '*
        '* FileSystem objects
        Dim oFileSystem As Object, _
            oParentFolder As Object, _
            oFolder As Object, _
            oFile As Object
    
        Set oFileSystem = CreateObject("Scripting.FileSystemObject")
        On Error Resume Next
        Set oParentFolder = oFileSystem.GetFolder(sPath)
        If oParentFolder Is Nothing Then
            FindFiles = False
            On Error GoTo 0
            Set oParentFolder = Nothing
            Set oFileSystem = Nothing
            Exit Function
        End If
        sPath = IIf(Right(sPath, 1) = "\", sPath, sPath & "\")
        '*
        '* Find files
        sFileName = Dir(sPath & sFileSpec, vbNormal)
        If sFileName <> "" Then
            For Each oFile In oParentFolder.Files
                If LCase(oFile.Name) Like LCase(sFileSpec) Then
                    iCount = UBound(recFoundFiles)
                    iCount = iCount + 1
                    ReDim Preserve recFoundFiles(1 To iCount)
                    With recFoundFiles(iCount)
                        .sPath = sPath
                        .sName = oFile.Name
                    End With
                End If
            Next oFile
            Set oFile = Nothing         '* Although it is nothing
        End If
        If blIncludeSubFolders Then
            '*
            '* Select next sub-forbers
            For Each oFolder In oParentFolder.SubFolders
                FindFiles oFolder.Path, recFoundFiles, iFilesFound, sFileSpec, blIncludeSubFolders
            Next
        End If
        FindFiles = UBound(recFoundFiles) > 0
        iFilesFound = UBound(recFoundFiles)
        On Error GoTo 0
        '*
        '* Clean-up
        Set oFolder = Nothing           '* Although it is nothing
        Set oParentFolder = Nothing
        Set oFileSystem = Nothing
    
    End Function

  4. #4
    New Member
    Join Date
    Jun 2012
    Posts
    5

    Default Re: Excel 2010: VBA replacement for Application.FileSearch

    Awesome, thanks for the code! I will give it a shot and try to tailer it to my needs.

  5. #5
    Board Regular
    Join Date
    Dec 2011
    Posts
    971

    Default Re: Excel 2010: VBA replacement for Application.FileSearch

    You are most welcome! Your feedback would be appreciated.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com