I have a program to extract datatable from somewhere, and put into an excel workbook.
If there are 2 DTs, then there will be 2 sheets. If 3 DTs, then 3 sheets. No. Of DT = no. Of sheets.
Then there should be an additional sheet, where there is a drop-down of the sheets, and a pivot table.
So when the process completed, we can access the file, choose a sheet from the drop-down, and the pivot table will change according to the sheet selected
Hi @TyraS
Drag and Drop “For Each” loop to iterate through the DataTables and add each DataTable as a new sheet in the Excel workbook using the “Add Data Table” activity.
Create an additional sheet in the Excel workbook where you want to add the drop-down and pivot table using the “Add Sheet” activity.
Use “Data Table to Excel” activity to write the DataTables with the extracted data into separate sheets in the Excel workbook.
Drag “Excel Application Scope” activity again to switch to the sheet where you want to add the drop-down and pivot table.
Use the “Add Data Validation” activity to add a drop-down list in a cell of the selected sheet. You can set the data source for the drop-down list to be the sheet names of the other sheets in the workbook.
Use “Build Data Table” activity to create a new DataTable that will serve as the data source for your pivot table. You can configure the columns and rows of the DataTable based on your requirements.
Use “Insert/Delete Pivot Table” activity to add a pivot table in a specific cell of the selected sheet, and set the data source to be the DataTable created in the previous step.
Use the “Configure Pivot Table” activity to configure the pivot table settings, such as choosing the rows, columns, values, and filters for the pivot table based on your requirements.
Use the “Write Cell” activity to update the drop-down list cell with the selected sheet name, and trigger the pivot table to refresh based on the selected sheet.
Close the Excel workbook using the “Excel Application Scope” activity with the “Save Workbook” option checked to save the changes made.
That’s it!
I hope it helps!
Hi, I’m still lost at
Use “Insert/Delete Pivot Table” activity to add a pivot table in a specific cell of the selected sheet, and set the data source to be the DataTable created in the previous step.
Use the “Configure Pivot Table” activity to configure the pivot table settings, such as choosing the rows, columns, values, and filters for the pivot table based on your requirements.
Use the “Write Cell” activity to update the drop-down list cell with the selected sheet name, and trigger the pivot table to refresh based on the selected sheet.
Close the Excel workbook using the “Excel Application Scope” activity with the “Save Workbook” option checked to save the changes made.
I don’t have the activity “insert/delete pivot table” and “configure pivot table”
Also sry I wasn’t clear in the question. The process actually ends after creating the drop-down and pivot.
Then we can manually access the file, choose the sheet we want from drop-down , and the pivot should dynamically refresh ?