Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Refresh QueryTable Code (Excel 2002)

    Hi,
    I use the code below to refresh all the pivot tables in the workbook... how would I do the same to refresh all the query tables in the workbook. I gave it a whirl (see below the pivot table code), but it didn't work. I don't want to use the ActiveWorkbook.RefreshAll code because then it refreshes BOTH the pivot & query tables and I need these done at different times.
    Thanks so much!
    Lana
    For Each PivotCache In ActiveWorkbook.PivotCaches
    PivotCache.Refresh
    Next PivotCache

    For Each QueryTable In ActiveWorkbook.QueryTables
    QueryTable.Refresh
    Next QueryTable

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Refresh QueryTable Code (Excel 2002)

    QueryTables is not a property of the workbook as a whole, but of a worksheet. So use this:

    Dim wsh As Worksheet
    Dim qtb As QueryTable
    For Each wsh In ActiveWorkbook.Worksheets
    For Each qtb In wsh.QueryTables
    qtb.Refresh
    Next qtb
    next wsh

    (Type QueryTables in the Visual Basic Editor and press F1 to get online help).

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refresh QueryTable Code (Excel 2002)

    You know that for Excel 2007 they've changed that: the querytables collection no longer is a part of the worksheet object but of the listObject object? So for 2007 you have to run through the ListObjects for each worksheet and refresh those if their datasource is a query.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Refresh QueryTable Code (Excel 2002)

    Thanks, I don't have Office 2007, so I didn't know this. So old code is going to break in 2007?

Posting Permissions

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