Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filesearch (WXP, OXP)

    Dear all,

    something very strange for me.

    I realised that Filesearch was not working. After a lot of trials I installed Excel again, and Filesearch worked.
    But only one time! If I start this macro again (or a different one in a different file), it will not work again.
    (On other computers it seems to run properly with the same files)

    Repairing Excel does not help, it has to be reinstalled

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Filesearch (WXP, OXP)

    By "not working" what do you mean?

    Do you get an error message? What is the error message?

    Does it give incorrect results? Where does it fail? Using what code?

    Steve

  3. #3
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filesearch (WXP, OXP)

    Hi Steve,

    thanks for the answer.

    Something like this:

    Sub test()
    Dim flsFile As FileSearch
    Set flsFile = Application.FileSearch
    With flsFile
    .LookIn = "c:excel"
    .SearchSubFolders = False
    .Filename = "Book1.xls"
    MsgBox .Execute ' the result is 0
    End With
    Workbooks.Open "c:ExcelBook1.xls" 'the workbook opens
    End Sub

    This works with no problem on different computers (the result of the .execute is "1").

    Wolf

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Filesearch (WXP, OXP)

    Not sure what the "problem" symptom is.

    Are you getting an indication that no files match, yet you can open one that should match?

    Are you getting that 1 (or more) file(s) match but you can't open any of them?

    Steve

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filesearch (WXP, OXP)

    My help files do not indicate that .Execute returns any value. Try the following:

    <pre>Sub test()
    Dim flsFile As FileSearch
    Set flsFile = Application.FileSearch
    With flsFile
    .NewSearch
    .LookIn = "c:excel"
    .SearchSubFolders = False
    .Filename = "Book1.xls"
    .Execute
    MsgBox .FoundFiles.Count & " Files found."
    End With
    End Sub
    </pre>

    Legare Coleman

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Filesearch (WXP, OXP)

    XP Help shows that .Execute returns the count of files found:

    Execute method as it applies to the FileSearch object. Begins the search for the specified file(s). Returns a Long; zero (0) if no files are found, or a positive number if one or more files are found

    I think .Execute has returned FoundFiles.Count since XL97. So within FileSearch we can use:

    If .Execute Then ....
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filesearch (WXP, OXP)

    The macro should show that the file c:Excelbook1.xls is not found by Filesearch(.execute=0, should be 1), but it can be opened.

    What I want to do is to use Filesearch to check if a file exists.

    Thanks

    Wolf

  8. #8
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filesearch (WXP, OXP)

    Hi,

    it returns 0 files found.

    Wolf

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Filesearch (WXP, OXP)

    I'm confused. Do you mean that "The macro should show that the file c:Excelbook1.xls IS found by Filesearch(.execute=0, should be 1), because it can be opened"? Try two changes to your code. As Legare shows use:

    With myfilesearch (or whatever you named the FileSearch object)
    .Newsearch

    and last thing before the end sub, clear the FileSearch object:

    Set myfilesearch = Nothing
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filesearch (WXP, OXP)

    Hi John,

    c:Excelbook1.xls exists

    The macro
    Set flsFile = Application.FileSearch
    With flsFile
    .LookIn = "c:excel"
    .SearchSubFolders = False
    .Filename = "Book1.xls"
    MsgBox .Execute

    returns "zero".
    The result should be "one".

    Using ".Newsearch" or "set flsFile = nothing" gives the same result.

    BTW: This marcro was working for several years (and it still works on other machines), but suddenly it gave this wrong result on my computer.

    Wolf

Posting Permissions

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