Ozgrid Excel Help & Best Practices Forums


XL Templates | XL Add-ins | XL Training | XL Estimating | XL Scheduling | XL Recovery | XL Trading | XL Financial | XL Conversion | XL Charting


+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 10 of 15

Thread: Application.filesearch Replacement For Office 2007

  1. #1
    Join Date
    8th July 2004
    Posts
    230

    Application.filesearch Replacement For Office 2007


    Download Active Data For Excel > > DETAILS > >
    In the following thread, richphillips wonders about Application.Filesearch

    http://www.ozgrid.com/forum/showthread.php?t=68191

    There was no answer to his question.

    Is there a replacement for this? I have several macros that use Application.Filesearch
    and I would like it to work not only with Excel 2003 but also with Excel 2007.

    This is the function

    Code:
    Function CreateFileList(FileFilter As String, _
        IncludeSubFolder As Boolean) As Variant
         ' returns the full filename for files matching
         ' the filter criteria in the current folder
        Dim FileList() As String, FileCount As Long
        CreateFileList = ""
        Erase FileList
        If FileFilter = "" Then FileFilter = "*.*" ' all files
        Debug.Print CurDir
    strFolder = BrowseForFolderShell(, , , 0)
    If strFolder = "" Then
    MsgBox "You Cancelled"
    Exit Function
    End If
       With Application.FileSearch
            .NewSearch
            .LookIn = strFolder
            .fileName = FileFilter
            .SearchSubFolders = IncludeSubFolder
            .FileType = msoFileTypeAllFiles
            If .Execute(SortBy:=msoSortByFileName, _
            SortOrder:=msoSortOrderAscending) = 0 Then Exit Function
            ReDim FileList(.FoundFiles.Count)
            For FileCount = 1 To .FoundFiles.Count
                FileList(FileCount) = .FoundFiles(FileCount)
            Next FileCount
            .FileType = msoFileTypeExcelWorkbooks ' reset filetypes
        End With
        CreateFileList = FileList
        Erase FileList
    End Function
    Thanks and regards.

    John

  2. #2
    Join Date
    8th July 2004
    Posts
    230

    Re: Application.filesearch Replacement For Office 2007

    After a few days it should be OK to BUMP.
    Thanks

  3. #3
    royUK is offline Publishes Private Messages
    Join Date
    26th January 2003
    Location
    Lincolnshire,UK
    Posts
    12,876

    Re: Application.filesearch Replacement For Office 2007

    See this
    Last edited by royUK; June 24th, 2007 at 07:47.

  4. #4
    Join Date
    18th September 2005
    Location
    Hampshire, UK
    Posts
    1,278

    Re: Application.filesearch Replacement For Office 2007

    Hi John

    Filesearch has indeed been removed but the (very) old Dir remains, so you can use this instead, an example of which follows:

    Code:
    Sub testit()
    myvar = FileList("C:\")
    For i = LBound(myvar) To UBound(myvar)
        Debug.Print myvar(i)
    Next
    End Sub
    
    Function FileList(fldr As String, Optional fltr As String = "*.*") As Variant
    Dim sTemp As String, sHldr As String
    If Right$(fldr, 1) <> "\" Then fldr = fldr & "\"
    sTemp = Dir(fldr & fltr)
    If sTemp = "" Then
        FileList = Split("No files found", "|") 'ensures an array is returned
        Exit Function
    End If
    Do
        sHldr = Dir
        If sHldr = "" Then Exit Do
        sTemp = sTemp & "|" & sHldr
    Loop
    FileList = Split(sTemp, "|")
    End Function
    This doesn't search subfolders but that functionality could no doubt be introduced. In the current spec you have to pass in the filepath to search.

    Richard

    EDIT: amended function so Do Loop doesn't error
    Last edited by RichardSchollar; June 24th, 2007 at 07:59.

  5. #5
    Join Date
    18th September 2005
    Location
    Hampshire, UK
    Posts
    1,278

    Re: Application.filesearch Replacement For Office 2007

    For some reason I can't seem to edit my last message. I have changed the return type if no files are found (it simplifies the code):

    Code:
    Sub testit()
        myvar = FileList("C:\Test3")
        If TypeName(myvar) <> "Boolean" Then
            For i = LBound(myvar) To UBound(myvar)
                Debug.Print myvar(i)
            Next
        Else
            MsgBox "No files found"
        End If
    End Sub
     
     Function FileList(fldr As String, Optional fltr As String = "*.*") As Variant
        Dim sTemp As String, sHldr As String
        If Right$(fldr, 1) <> "\" Then fldr = fldr & "\"
        sTemp = Dir(fldr & fltr)
        If sTemp = "" Then
            FileList = False
            Exit Function
        End If
        Do
            sHldr = Dir
            If sHldr = "" Then Exit Do
            sTemp = sTemp & "|" & sHldr
         Loop
        FileList = Split(sTemp, "|")
    End Function

  6. #6
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,901

    Re: Application.filesearch Replacement For Office 2007

    You can edit your posts only for 1 hour after posting.
    .

  7. #7
    Join Date
    18th September 2005
    Location
    Hampshire, UK
    Posts
    1,278

    Re: Application.filesearch Replacement For Office 2007

    Ah that would expalin it! Thanks BTC2

  8. #8
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    51,285

    Re: Application.filesearch Replacement For Office 2007

    Not too sure that it has been "removed" or M$ stuffed up and forgot to add it. Perhaps it will be added back after fixing the gazillion other bugs?

  9. #9
    Join Date
    8th July 2004
    Posts
    230

    Re: Application.filesearch Replacement For Office 2007

    Parsnip, Roy, ByTheCringe2 and Dave, thanks for replying.
    I put the suggested replacement code in from both Parsnip and Roy but I can't get things to work. Due to my ignorance of VB I have to say. I have looked and tried to understand the suggested code but I can't see where I need to change/add things to make it work as the file does in Excel 2003. I neglected to add the Sheet1 code to my first thread entry.
    This is it.

    Code:
    Private Sub CommandButton1_Click()
    
    Dim filenamelist() As Variant
    filenameslist = CreateFileList("*.*", False)
    If Not IsArray(filenameslist) Then
        MsgBox "No files"
        Exit Sub
    End If
    
    For i = 1 To UBound(filenameslist)
        ActiveSheet.Cells(10 + i, 1).Value = filenameslist(i)
    Next i
    End Sub
    I don't even know if you need it for the replies.

    I have put Parsnip's and Roy's code in but because of my very limited VB knowledge, can't get it to work.
    With Parsnip's code, I get either 'No files found" or nothing happens at all, depending on the changes I make.

    In Roy's code it says "do something" but I don't even know what to enter at this point.

    If someone has the time and would be willing to try the attached file in Excel 2003, he/she will see how it works and maybe tell me what to change where.

    The code in the attached file is obviously not written by me.

    Your help is greatly appreciated.

    Thanks and regards.

    John
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

  10. #10
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    51,285

    Re: Application.filesearch Replacement For Office 2007


    Create Excel dashboards quickly with Plug-N-Play reports.
    Pasnips codes here works fine for me so long as I modify C:\Test3 to an extisting path on my PC.

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

There are currently 2 users browsing this thread. (0 members and 2 guests)

     

Possible Answers

  1. Replies: 8
    Last Post: March 11th, 2008, 08:23
  2. Replies: 4
    Last Post: February 12th, 2008, 08:09
  3. Application.FileSearch Method in 2007
    By woodentent in forum EXCEL HELP
    Replies: 3
    Last Post: September 24th, 2007, 22:00
  4. Application.filesearch
    By parrimin in forum EXCEL HELP
    Replies: 5
    Last Post: June 28th, 2007, 21:57
  5. Application.filesearch In Office 2007
    By richphillips in forum EXCEL HELP
    Replies: 2
    Last Post: June 24th, 2007, 07:29

Bookmarks

Posting Permissions

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