Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Refresh data - button (2003 SP2)

    I'm doing a spreadsheet for some users & wanted to put a button on the data sheet so a user just click that to refresh the data. Only problem is; I don't know how to get the button to do the refresh!

    Help!

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

    Re: Refresh data - button (2003 SP2)

    If you want to refresh a pivot table, you can use something like

    ActiveWorkbook.PivotCaches(1).Refresh

    or

    ActiveSheet.PivotTables(1).RefreshTable

    If you want to refresh a table based on an external data source:

    ActiveSheet.QueryTables(1).Refresh

  3. #3
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refresh data - button (2003 SP2)

    That'll do.

    Many thanks

  4. #4
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refresh data - button (2003 SP2)

    Ooh, just had an additional requirement; could you give me the code for refreshing multiple sheets please?

    TIA

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

    Re: Refresh data - button (2003 SP2)

    Is this about pivot tables or query tables?
    Do you want to refresh specific sheets, or all sheets with pivot (or query) tables, or all sheets?

  6. #6
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refresh data - button (2003 SP2)

    These are all query tables (from AS400 server), with different data on about 5 sheets (at the moment!).

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

    Re: Refresh data - button (2003 SP2)

    The following macro will refresh all query tables in the active workbook:

    Sub UpdateQueryTables()
    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
    End Sub

  8. #8
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refresh data - button (2003 SP2)

    Cheers Hans.

Posting Permissions

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