Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    application.filesearch fails (XL2007 64bit (via Citirx))

    A search of the Lounge found <post#=733,253>post 733,253</post#> from Rory that states:
    <hr>if you use Application.Filesearch in your macros, that doesn't exist in 2007.<hr>
    I have macro that runs under the Workbook_Open() event that fails at such a line so I tend to believe him.

    BUT... If you search the Excel 2007 VBA help file for 'FileSearch' you find some example code that uses Application.FileSearch:
    <pre>Set fs = Application.FileSearch
    With fs
    .LookIn = "C:My Documents"
    .FileName = "cmd*.*"
    If .Execute > 0 Then</pre>


    That e.g. is not a million miles from the code my workbook is choking on:
    <pre>Pivate Sub Workbook_Open()
    Application.ScreenUpdating = False
    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">With Application.FileSearch</span hi>
    .LookIn = "path to a network folder"
    .Filename = "filename*.xls"
    If .Execute > 0 Then 'Workbook exists</pre>


    Please can someone clarify this apparent contradiction?

    stuck

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts

    Re: application.filesearch fails (XL2007 64bit (via Citirx))

    The bad news below is directly from the MicroSoft Knowledge Base. Hope the workaround can be used in your application.

    You run a Microsoft Visual Basic for Applications (VBA) macro in a Microsoft Office 2007 program to search for a file on your computer. When you do this, you may receive the following VBA error message:
    "Run-time error 5111
    The command is not available on this platform"

    CAUSE
    This issue occurs when you have 2007 Office programs installed on a Microsoft Windows XP computer, and the VBA macro uses the Application.FileSearch object. This object is no longer supported in 2007 Office programs.

    WORKAROUND
    To work around this issue, use the FileSystemObject object to recursively search directories and to find specific files. See the sample code that is provided in the following Microsoft Knowledge Base article:
    185601 How to recursively search directories by using filesystemobject

    Tom Duthie

  3. #3
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: application.filesearch fails (XL2007 64bit (via Citirx))

    Thank you, I've been busy with Google and similarly confirmed the bad news.

    I find it appalling that the Excel 2007 help file includes example code for an unsupported feature.

    Never mind, it's progress!

    stuck

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: application.filesearch fails (XL2007 64bit (via Citirx))

    Excel's Help gets consistently worse with each version! (though there is a nice bit of prose in the offline help for named ranges in 2007)
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: application.filesearch fails (XL2007 64bit (via Citirx))

    Can I borrow the light sabre in your user pic? There are some people where I am that I'd like to approach with it.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: application.filesearch fails (XL2007 64bit (via Citirx))

    It's a pool cue, not a light sabre, (my light sabre is buried in someone's head) but yes, you may! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: application.filesearch fails (XL2007 64bit (via Citirx))

    Excellent! Yet again, you always get the help you need from the Lounge.

    Ken

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

    Re: application.filesearch fails (XL2007 64bit (via Citirx))

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    This is not a new story.

    With Off97 on Win2000 filesearch did not work either. (It comes back every 10 years <img src=/S/aflame.gif border=0 alt=aflame width=16 height=16> )

    The suggested workaround at that time was using "Dir"
    http://support.microsoft.com/kb/259738

    Wolf

  9. #9
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: application.filesearch fails (XL2007 64bit (via Citirx))

    and sure enough "Dir" to the rescue:

    <pre>Dim tmp
    tmp = Dir("pathname" & "*.xls")

    If tmp <> "" Then</pre>


    (not) stuck

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts

    Re: application.filesearch fails (XL2007 64bit (via Citirx))

    or even

    tmp = Dir("pathname" & "*.xlsm")

    or

    tmp = Dir("pathname" & "*.xlsx")

    or

    tmp = Dir("pathname" & "*.xlsb")

    (Well, it was an Excel2007 question)

    zeddy

  11. #11
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: application.filesearch fails (XL2007 64bit (via Citirx))

    Yes, it was an XL 2007 question, so your suggestions have merit.

    At this instant though a simple *.xls gets me out of a hole. Eventually though I will need to tackle my 'issue' in a completely different way. This is only a small step at the start of the road that is Office 2007 or bust.

    Ken

Posting Permissions

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