Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Unhappy

    Hi y'all.

    We have a spreadsheet that refreshes data from the web every 5 minutes. However, whenever the data refreshes, all the Filters disappear and then have to be redone. Just to clarify, it's not just that the Filter on/off needs to be clicked again, it actually removes the Filtered data, so if we currently have a column Filtered by Town, the filter is turned off, and all the data reappears.

    Is there any way to get Excel to Refresh the Data but keep the current view?

    Thanks in advance for any help or advice.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You'd probably have to write event code for the AfterRefresh event of the QueryTable to apply the filter again.

    See How to Use the Query BeforeRefresh and AfterRefresh Events.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You can also change the properties of the webquery and set the option to "Overwrite existing cells with new data, clear unused cells".
    On my Excel 2003 that works fine.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Unhappy

    [quote name='pieterse' post='786575' date='28-Jul-2009 18:03']You can also change the properties of the webquery and set the option to "Overwrite existing cells with new data, clear unused cells".
    On my Excel 2003 that works fine.[/quote]

    Thanks Jan. MS must have changed this in Office 2007. We tried all 3 options for 'Overwrite Existing cells . . .", but in each instance, Excel removed the filters.

    Unless there's another thing we can try, I guess we'll have to try writing Code, as Hans suggested.

    Thanks to both of you for your help.

Posting Permissions

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