Search data in excel and return the result in a win form text box
Hello,
I am trying to make an automation that uses a windows form and an excel file. I created a combo box with a series of items (names of some templates) and I want to make it so that when the user selects a certain item in the win form drop down menu, the robot will search for that item in excel (A column) and then copy the corresponding text (from B column) to a text box back in the windows form.
Can someone assist me with the steps I have to go through in the automation in order to be able to achieve this?
If somebody can share some sample code for this, it would be very much appreciated.
Regards,
Accepted Solution
Pegasystems Inc.
US
I have attached a quick sample solution of what you might do along with a sample spreadsheet. I am essentially going through all of the rows of the spreadsheet until column A is empty. I then export the data into a LookupTable that I have bound to the combo box. The DisplayMember of the combo box is set to Column A while the ValueMember is set to ColumnB. I can then pull the value based on what is selected. This assumes that the spreadsheet column A has unique values in it (which would stand to reason if you wanted to associate a value in one column with that of another as you indicated.
Pegasystems Inc.
US
Hello,
There are 3 possible solutions:
1. Use MicrosoftExcel component. This is a quite common approach for RDA. Please see more details using the following link: https://help.roboticautomation.pega.com/80/Components/Microsoft_Excel.htm
2. Use MicrosoftExcel component and create a macro in Excel. Then invoke this macro from automation. Please see more details using the following link: https://help.roboticautomation.pega.com/80/Components/Using_the_Microsoft_Excel_Component.htm
3. Use Excel File Connector component that was added in Pega Robotics in 8.0 SP1_2007 version. You can get more details using the following links: https://help.roboticautomation.pega.com/80/Components/Excel_File_Connector_Overview.htm
I recommend to do your task with the help of Excel File Connector. The attached solution take the name of the country from the drop-down list, compare it with the 'Country' column in excel file and return corresponding value from the 'Capital' column.
Hello,
There are 3 possible solutions:
1. Use MicrosoftExcel component. This is a quite common approach for RDA. Please see more details using the following link: https://help.roboticautomation.pega.com/80/Components/Microsoft_Excel.htm
2. Use MicrosoftExcel component and create a macro in Excel. Then invoke this macro from automation. Please see more details using the following link: https://help.roboticautomation.pega.com/80/Components/Using_the_Microsoft_Excel_Component.htm
3. Use Excel File Connector component that was added in Pega Robotics in 8.0 SP1_2007 version. You can get more details using the following links: https://help.roboticautomation.pega.com/80/Components/Excel_File_Connector_Overview.htm
I recommend to do your task with the help of Excel File Connector. The attached solution take the name of the country from the drop-down list, compare it with the 'Country' column in excel file and return corresponding value from the 'Capital' column.
Before start of the project make sure that you have correct path to the file.
In addition the automation Excel_P_ReadFile uses proxy and WriteXml method for testing purposes.
Sykes Enterprises Eastern Europe LTD
RO
@balap Thank you for the information and for the demo! Really helps a lot for getting used to using excel with Pega Studio.
I'll make sure to go trough the recommended links.
Thanks a lot!
Accepted Solution
Pegasystems Inc.
US
I have attached a quick sample solution of what you might do along with a sample spreadsheet. I am essentially going through all of the rows of the spreadsheet until column A is empty. I then export the data into a LookupTable that I have bound to the combo box. The DisplayMember of the combo box is set to Column A while the ValueMember is set to ColumnB. I can then pull the value based on what is selected. This assumes that the spreadsheet column A has unique values in it (which would stand to reason if you wanted to associate a value in one column with that of another as you indicated.