Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Pivot tables Refresh within onClick()

  1. #1
    Join Date
    27th March 2006
    Posts
    88

    Pivot tables Refresh within onClick()

    Hi i would like when i select my button on my access form, for the file to refresh, at the moment i've got the code below to open the form, but i would like the data to be refreshed also, the data is coming from a connection to a Access database.

    If at all possible i like to intergate the code into my on button click. or in the excel sheet.

    VB:
    Private Sub Bel_Order_report_Click() 
        On Error Goto Err_Bel_Order_report_Click 
         
        Dim oApp As Object 
        Dim wrkFile As Object 'Workbooks
         
        Set oApp = CreateObject("Excel.Application") 
        Set wrkFile = oApp.Workbooks 
         
         ' Open a file.
        wrkFile.Open "B:\Consumables_Orders_Cost_Reports\Reports\Orders\Orders_Report_Legacy_BE.xls" 
        oApp.Visible = True 
         
         'Only XL 97 supports UserControl Property
         
        MsgBox "You've closed the Order Report." & Chr$(13) & _ 
        "and return to the main screen." 
         ' Close the file.
        wrkFile.Close 
         ' Quit Excel.
        oApp.Quit 
         
         ' Close the object references.
        Set wrkFile = Nothing 
        Set appXl = Nothing 
         
        On Error Resume Next 
        oApp.UserControl = True 
         
    Exit_Bel_Order_report_Click: 
        Exit Sub 
         
    Err_Bel_Order_report_Click: 
        MsgBox Err.Description 
        Resume Exit_Bel_Order_report_Click 
         
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    14th January 2005
    Location
    Cyprus
    Posts
    2,252

    Re: Pivot tables Refresh within onClick()

    Do you want to save the workbook after the refresh ? More than one pivot ?

    I would think you could add something like :-

    VB:
    Dim ws As Worksheet 
    For Each ws In wrkFile.Worksheets 
        For Each pt In ws.PivotTables 
            pt.PivotCache.BackgroundQuery = False 
            pt.PivotCache.Refresh 
        Next pt 
    Next ws 
    wrkFile.Save 
    
    
    in the Access code.

    HTH
    Carl

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    27th March 2006
    Posts
    88

    Re: Pivot tables Refresh within onClick()

    Do you want to save the workbook after the refresh ?

    Yes i think it would be a good idea to do this also.

    More than one pivot ?

    i do have more than 1 pivot table but they are linked to the first pivot table, so if i refresh it the the rest should refresh also would you say that this is true ??

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    27th March 2006
    Posts
    88

    Re: Pivot tables Refresh within onClick()

    Am getting a error on this line saying (PivotTables ) method or datamember not found,


    VB:
    For Each pt In ws.PivotTables 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    14th January 2005
    Location
    Cyprus
    Posts
    2,252

    Re: Pivot tables Refresh within onClick()

    I forgot to specify

    VB:
    Dim pt As PivotTable 
    
    
    at the top

    You are correct, if you base all the PTs on one PT then you do only need to refresh that one. In which case I guess you could do something like :-

    VB:
    Dim pt As PivotTable 
    Set pt = Thisworkbook.Worksheets("TheSheetNameHere").PivotTables("ThePTNameHere") 
    pt.PivotCache.BackgroundQuery = False 
    pt.PivotCache.Refresh 
    
    
    HTH
    Carl

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    27th March 2006
    Posts
    88

    Re: Pivot tables Refresh within onClick()

    This may seem like a silly question, but my data is coming from a access database and i only have the work sheet name, but were do i name the pivot table or were can i find the default name this is given to it. maybe it's the sheet name ?

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    14th January 2005
    Location
    Cyprus
    Posts
    2,252

    Re: Pivot tables Refresh within onClick()

    Click on PT. Right click > Table options > Top Left

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Refresh All Pivot Tables In Workbook
    By HerronShane in forum EXCEL HELP
    Replies: 4
    Last Post: January 20th, 2012, 11:26
  2. refresh pivot tables linked to pivot table
    By macrocyte in forum EXCEL HELP
    Replies: 6
    Last Post: July 28th, 2006, 13:54
  3. Periodic refresh of pivot tables
    By itbitty in forum EXCEL HELP
    Replies: 2
    Last Post: July 22nd, 2006, 02:44
  4. Replies: 11
    Last Post: February 18th, 2004, 07:59
  5. [Solved] Pivot Tables: Wont Refresh
    By peterwalliker in forum EXCEL HELP
    Replies: 1
    Last Post: July 31st, 2003, 02:13

Bookmarks

Posting Permissions

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