Announcement

Collapse
No announcement yet.

Pivot tables Refresh within onClick()

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

    Code:
    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

  • #2
    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 :-

    Code:
    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
    locii Insight
    Locii Solutions. Business IT Training. Business Consulting. Excel Training. Business Intelligence using Excel 2010. Cyprus.

    Comment


    • #3
      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 ??

      Comment


      • #4
        Re: Pivot tables Refresh within onClick()

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


        Code:
        For Each pt In ws.PivotTables

        Comment


        • #5
          Re: Pivot tables Refresh within onClick()

          I forgot to specify

          Code:
          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 :-

          Code:
          Dim pt as PivotTable
          Set pt = Thisworkbook.Worksheets("TheSheetNameHere").PivotTables("ThePTNameHere")
          pt.PivotCache.BackgroundQuery = False 
          pt.PivotCache.Refresh
          HTH
          Carl
          locii Insight
          Locii Solutions. Business IT Training. Business Consulting. Excel Training. Business Intelligence using Excel 2010. Cyprus.

          Comment


          • #6
            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 ?

            Comment


            • #7
              Re: Pivot tables Refresh within onClick()

              Click on PT. Right click > Table options > Top Left
              locii Insight
              Locii Solutions. Business IT Training. Business Consulting. Excel Training. Business Intelligence using Excel 2010. Cyprus.

              Comment

              Working...
              X