Pega Robotics Excel connector issues
Hello I have 2 questions
1) Iam trying to save/download excel file from a web application and then edit the excel. The process is when i hit save button on the webapp the excel file should be downloaded into my downloads folder. In my automation iam using file exists before i proceed to next step of editing the file but file exists method returns false as file hasnt completed download yet. There is no waitforcreate option for files. Any suggestions how to resolve this
2)I am actually trying to update cells(rows) with same formula and there are 70,000 rows in the excel and doing a for loop and updating using set cell value takes 45 mins. is there a way to bulk updates of cells in excel ? Or is there a way to use SendKeys to flash fill all the cells in the column. Same formula need to be copied to 70k rows. Other option I thought is writing a C# script to create OLEDB connection from Excel and do bulk updates. Please let me know if there is better solution for this.
Pegasystems Inc.
US
To check to see if a file download is complete you can do the following. This will attempt to open the file with sharing disabled - this will throw and exception if the file is in use. Adjust the sleep and the forLoop limit based on your needs - this example will wait up to 30 seconds and exit from the break when the file is accessible.
Upton Hill
US
Thanks Jeff. I did the same . Put a wait/Timer for 20 seconds before I can see if the file exists.
Jeff/Anthony
I found a fastest way to update large number of records in Excel . The ExportData Method of Microsoft Excel Connector is pretty slow and its takes 45 mins to export data(70k) into a data table before i can update rows using C# Script. Instead of MicrosoftExcel Connector I used the excelConnector Object and the method ExportTable to export it into Datatable thenComplete update operations using C# Script and finally use ImportTable to get the data back to Excel. Its pretty fast only takes 20 seconds to export data(70k Records) from excel to datatable. Not sure whats the difference between excelConnector object and MicrosoftExcel Connector object. But excelConnector seems to be perform better when it comes to large number of records.
US Bank
US
Hi @HariB209
I find this document helpful.
https://community.pega.com/knowledgebase/documents/using-microsoft-office-connectors
US Bank
US
Hi @HariB209
Another way to update the formula column is with an Excel Macro called from Pega.
See example image attached of Excel spreadsheet and macro to call which adds formula to multiply Column A value by Column B value to show result in Column C.
Macro in Excel
Sub FormulaMacro()
Application.CutCopyMode = False Range("C2").Select ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]" Range("C2").Select Selection.Copy ActiveCell.Offset(0, -1).Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(0, 1).Range("A1").Select Range(Selection, Selection.End(xlUp)).Select ActiveSheet.Paste Range("C2").Select
End Sub
Upton Hill
US
Anthony
i explored the idea of using a Macro. The only downside to it, Pega robotics has only option to RunMacro and there is no way to attach/Add a Macro to an Excel. Except for Password you cant interrogate any control in Excel using Pega Robotics. The Excel that iam working with will be downloaded from a web application and dynamically generated for different IDs. I saw there was an option to add a macro to an excel using a C# Script and then we can use RunMacro method from pegarobotics to run that macro. But its a little complicated and I was able to achieve end goal by using excelConnector object like i mentioned above.
Thanks
Hari