Posted: 25 Apr 2017 12:25 EDT Last activity: 5 May 2017 11:04 EDT
Create a New Sheet in Excel Document
This is for Pega Robotics: I have a source Excel and Destination Excel. Is it possible to create the source sheet in the destination, ie create a new sheet and rename the sheet and save it in the Destination Excel.
***Updated by moderator: Lochan to add Categories***
source.xlsx has more than one sheet(say sheets: A,B,C,D,E, F) and destination.xlsx has some sheets (M, N). Both the source and destination are in the same folder. Source.xlsx has formatting, Styles (color, bold etc). Now i need to copy sheets: A,B,C,D,E, F on to the destination.xlsx with formatting and Styles.
The Excel connector in Pega Robotics exposes the basic operations of Excel to an automation, giving the ability to read and set values in cells, etc. For more complex operations you can use a script component and write C# code that uses the Microsoft.Office.Interop.Excel namespace. To create a script that uses the Interop library follow these steps.
1. Add a Script component to your Global Container, here I have added one called ExcelScripts.
2. Add a reference to the Interop library to the script. Right click on the script component and select Edit References. Click on GAC..., find the Microsoft.Office.Interop.Excel entry, click OK and OK.
3. Now create your script. Right click on the script component and select Edit Script. To create a script, click on the green + button. Here is an example of a stubbed out script that works with Excel workbooks.
4. To call the script from an automation use the ExcelWorkbook properties from the Excel connectors