Refreshing Pivot Table

CamiCat

Member
Hello friends,

how is it possible to Auto refresh pivot table on internal data change in Excel, for updating the Excel of Input of the bot ?
Do you know through VBA?
Thank you so much,
Camilla :)
 

sivagelli

Well-Known Member
There is not built in action to deal with Pivots. For refreshing a Pivot table, the following code will helps-

  1. Open 'MS Excel VBO' and right click on 'Go to Cell' action and choose duplicate
  2. Name the action as 'Pivot Refresh'
  3. In the 'Start' stage change the input parameter 'Cell Reference' to 'Pivot Table' and store in Data Item 'PivotTable' (you can rename the existing 'cellref' data item on the page to 'PivotTable')
  4. Open the Code stage and rename code stage to Pivot Refresh (to avoid duplicate name error with 'Go to Cell' action)
  5. Change the input parameter, 'cellref' on the Code stage to 'PivotTable'
  6. Go to Code tab and put the code GetWorkbook(handle, "").ActiveSheet.PivotTables(PivotTable).RefreshTable
  7. Publish the action
  8. Save the MS Excel VBO action

Now, you should be able to call this new action 'Pivot Refresh' passing the name of the pivot table on the worksheet to refresh.
A typical process flow for usage would be as -
  • Start
  • Create Instance
  • Open Workbook
  • Activate worksheet
  • Pivot Refresh
  • Close Instance
  • End
Post back, how it goes.
 

CamiCat

Member
Hello @sivagelli ,

which type of data item should the Data Item 'PivotTable' be (in the Start Stage of the Action Pivot Refresh)?
Thank you so much,
Camilla ;)
 

CamiCat

Member
Hello @sivagelli,i tried your solution but unfortunatey the code gives me error I'm attaching.
Can you please help me kindly?
Can you maybe attah some screenshots of how I can insert the Object?
Thank you so much,
Camilla
 

Attachments

  • error pivot refresh.png
    102.1 KB · Views: 116

sivagelli

Well-Known Member
The error means, you are missing the Data Item on the page. Have you created a data item with name 'PivotTable' of text type?
 

CamiCat

Member
Hello @sivagelli ,
1)Do you mean in the Process or in the Object Studio?
I attach the screenshot of the page of the objects
2)Where can i find the variable to put in the Value field of the Pivot Refresh Action?
Thank you,
Camilla
 

Attachments

  • pivot refresh code.png
    101.5 KB · Views: 87

CamiCat

Member
Hello @sivagelli,
I attach the screenshots you requested to me.
 

Attachments

  • pivot refresh stage code.png
    95.1 KB · Views: 99
  • pivot refresh start.png
    102.7 KB · Views: 87
  • pivot refresh code.png
    101.5 KB · Views: 87

sivagelli

Well-Known Member
The code stage has input 'cellref', change it to 'PivotTable' and map the respective data item.
As the code uses 'PivotTable' and the code stage input is not getting the data item, BP is throwing error.

You can delete 'cellref' data item on the object page.
 

sivagelli

Well-Known Member
The code stage has input 'cellref', change it to 'PivotTable' and map the respective data item.
As the code uses 'PivotTable' and the code stage input is not getting the data item, BP is throwing error.

You can delete 'cellref' data item on the object page.
 

CamiCat

Member
Thank you so much @sivagelli.
Now no more errors.
One more question:
Where can i find the variable to put in the Value field of the Pivot Refresh Action in the Process?
I don't know how I can initialize the variable PivotTable in the Process Studio.
Thank you so much,
Camilla :)
 
Last edited:

sivagelli

Well-Known Member
One more question: )Where can i find the variable to put in the Value field of the Pivot Refresh Action in the Process?
As you have published the action 'Pivot Refresh', you should be able to use this action similar to the other actions on the Process page. You have to pass the Pivot table name as an input, you can either have a data item (text) on the process page with initial value of pivot table name from the worksheet. Or you can pass the Pivot table name in double quotes to the action 'Pivot Refresh'.
 

sheel

New Member
@sonali H @sivagelli I am also getting same error after performing above steps, could you please help me.

Internal : Could not execute code stage because exception thrown by code stage: Unable to get the PivotTables property of the Worksheet class
 

maybeccie

New Member
Dear All,

I am also Applying all those steps and please help me if i am getting error. Or if you can reshare all those steps again with detail.

Since you people have spend hours and hours on that
 
Top