Question
Infosys
IN
Last activity: 21 Nov 2023 0:13 EST
Copying data from one excel file to another excel file.
Hi
I have a requirement for a copy of one Excel file into another Excel file by using components in Robotics Studio.
Thanks.
Accepted Solution
Updated: 28 Nov 2023 8:26 EST
Pegasystems Inc.
US
@Sai Guggilla Here is a simple example using the Excel Connector. If you need the worksheet visible to the user, you can use the Microsoft Excel Connector, however it will be much slower and the steps will be different. If possible, use the Excel Connector and then open the file for the user. If you need to use the Micorostf Excel Connector, let me know and I can provide an example for that connector.
- Define the file that the Excel connector is going to open in its properties. If this is going to change at Runtime, then set the FileName property at Runtime and call the Open method.
- Use the GetLastCellAddress method of the connector to determine the last address in the worksheet.
- Use the AddressToRowColumn method to convert this value into rows and column values.
- Use the AddSheet method to add a new worksheet with the specified name. You can add logic to check if the worksheet exists if necessary. This method would fail if the sheet already exists.
- Use the CopyRows method to move the data from the current worksheet to the new one. You can use "1" as the startRow and destinationRow parameters.
- Call the Save method to save the file.
Pegasystems Inc.
US
@Sai GuggillaIf you simply wish to make a copy of the entire file, you can do so in several simple ways in 22.1.
1. On the Toolbox, search for "copy" and you will see a CopyFile method. This will copy the entire file from one location to another.
2. From either Excel connector; you can use the SaveAs method to save a copy of the file to wherever you wish.
If you wish to copy only certain worksheets within a file, that is also possible. Let me know that is something you wish to see.
Infosys
IN
Accepted Solution
Updated: 28 Nov 2023 8:26 EST
Pegasystems Inc.
US
@Sai Guggilla Here is a simple example using the Excel Connector. If you need the worksheet visible to the user, you can use the Microsoft Excel Connector, however it will be much slower and the steps will be different. If possible, use the Excel Connector and then open the file for the user. If you need to use the Micorostf Excel Connector, let me know and I can provide an example for that connector.
- Define the file that the Excel connector is going to open in its properties. If this is going to change at Runtime, then set the FileName property at Runtime and call the Open method.
- Use the GetLastCellAddress method of the connector to determine the last address in the worksheet.
- Use the AddressToRowColumn method to convert this value into rows and column values.
- Use the AddSheet method to add a new worksheet with the specified name. You can add logic to check if the worksheet exists if necessary. This method would fail if the sheet already exists.
- Use the CopyRows method to move the data from the current worksheet to the new one. You can use "1" as the startRow and destinationRow parameters.
- Call the Save method to save the file.
Infosys
IN
Thanks for your response.