Change Pivot table fields and copy data from excel to another
Hi,
Using openspan, how to automate the following scenarios in an Excel spreadsheet.
1. Open an excel attachment from Sharepoint, update a pivot table data source
2. Change the Pivot table fields(Column Headers)
3. Copy couple of rows within a pivot column, and update it in another excel spreadsheet after ungrouping the contents
Thanks,
Arun
**Moderation Team has archived post**
This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.
Accepted Solution
Pegasystems Inc.
US
Arunk,
No you cannot. Our connectors allow basic functionality with the excel sheet. Any additional functionality needed will have to be done through a custom script referencing the Office.Interop.Excel Namespace.
Below are a list of available functions through the Microsoft Office connector.
Pegasystems Inc.
US
Arun,
OpenSpan works within confines of the available methods and properties of the Office connectors that are provided to us by Microsoft.
Unfortunately, there are no interface functionality with using pivot tables for Excel. You will need to collect the necessary information from the excel worksheet through the getCellValue method within the connector and build automation logic to summarize the needed info for each field.
Once you have done so you will need to then transfer the collected information to the corresponding excel sheet using the setCellValue .
Accenture
US
Thank you Thomz, Can we sort, group and paste special as formulas in an excel sheet?
- Sort Column K by Largest to Smallest
- Group values with their total less than |1M|
- Paste special as Formulas
Thanks,
Arun
Accepted Solution
Pegasystems Inc.
US
Arunk,
No you cannot. Our connectors allow basic functionality with the excel sheet. Any additional functionality needed will have to be done through a custom script referencing the Office.Interop.Excel Namespace.
Below are a list of available functions through the Microsoft Office connector.