Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Dec 2001
    Location
    Cromwell, Connecticut, USA
    Posts
    40
    Thanks
    2
    Thanked 2 Times in 2 Posts

    Using VBA Filesearch in Excel 2010?

    I've started to explore upgrading my VBA code from Excel 2003 to Excel 2010 and keep finding lots of changes! My latest dilemma comes from the list of "Object Model Changes Since Microsoft Excel 2003" that I found in the Excel 2010 VBA help. If I try to use any of the objects listed as "hidden", I still get an error code.

    Shouldn't I be able to still use the deprecated objects?

    Any suggested workaround or reference to a master list of optional objects to use? I use a lot of these, such as:
    - application.filesearch
    - various chart properties


    Thanks - John

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Given that Microsoft distinguishes between "Hidden" (deprecated) and "Removed" (see Excel 2010 Object Model Changes Since Earlier Versions), I agree that it sounds as though they still should work.

    What error do you get?

  4. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,869
    Thanks
    0
    Thanked 78 Times in 74 Posts
    Filesearch does not work in 2007 onwards. Which chart properties are you having issues with?
    Regards,
    Rory
    Microsoft MVP - Excel.

  5. #4
    Lounger
    Join Date
    Dec 2001
    Location
    Cromwell, Connecticut, USA
    Posts
    40
    Thanks
    2
    Thanked 2 Times in 2 Posts
    Running "compile" doesn't generate an error but I do receive error# 445, "object doesn't support this action" when I step to the line:

    Set FS = Application.Filesearch

    So, is there a replacement for filesearch or do I need to rewrite my code using fileexists, vbscript or similar?

    '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''

    Rory - I haven't explored the charting issues yet. I have over 4,000 control charts, as individual Excel workbooks containing 2 worksheets each, 1 chart per worksheet. The good news is that all of the charts are created from a single master file so they are exactly the same & the minimum I must control via the chart object looks like it should still work. The other charting properties from the deprecated list is just "fluff" that we can live without. This is the minimum code I need to function properly:

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Axes(xlValue).Select
    With ActiveChart.Axes(xlValue)
    .MinimumScale = LCL - NewChartRng
    .MaximumScale = UCL + NewChartRng
    .MinorUnit = 1
    .MajorUnitIsAuto = True
    .Crosses = xlCustom
    .CrossesAt = 100
    .ReversePlotOrder = False
    .ScaleType = xlLinear
    End With

    '''''''''''''''''''''''''''''''''''''''''''

    Thanks for the help,
    John

  6. #5
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 27 Times in 26 Posts
    Quote Originally Posted by kamski View Post
    So, is there a replacement for filesearch or do I need to rewrite my code using fileexists, vbscript or similar?
    John,

    There's no direct replacement for FileSearch within Office. One practical alternative is to use the Scriptiing.FileSystemObject - if you do a search on this forum for 'FileSystemObject' and/or 'FSO', you'll probably find lots of code examples.

    Gary

Posting Permissions

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