Hi, all
Is there any way to get a row number of the cell that has a specific word? (Assume that word is unique in the sheet)
For example, I want to get "4" as a row number of the cell that has "Settings-001".
I appreciate for your help in advance.
Regards
------------------------------
HongJoo Choi
------------------------------
Is there any way to get a row number of the cell that has a specific word? (Assume that word is unique in the sheet)
For example, I want to get "4" as a row number of the cell that has "Settings-001".
I appreciate for your help in advance.
Regards
------------------------------
HongJoo Choi
------------------------------
1 BEST ANSWER
Best Answers
Hi Hongjoo,
For your use case, I have built a workflow that you can use:.
Workflow Design:
Explanation:
Here, I am connecting to an excel file using the actions 'Create Instance', 'Show' and 'Open Workbook' actions of 'MS Excel - Extended' VBO. Once you are connected to the given file path in the 'Open Workbook' action, the Workbook Name data item will be generated which you can use in the subsequent actions.
The next action that I've used is 'Find' action of 'MS Excel - Extended' VBO where you need to provide the inputs such as handle, workbook name, worksheet name, range, find text and starting cell. What this action does is that it will active the cell where the text is found. In my case, I am searching for the text, 'Cian' which is at cell 'A50' as shown below:
Once you provide the correct parameters, use the action 'Get Active Cell' of 'MS Excel - Extended' VBO which will return you the cell reference that is currently activated which in my case is 'A50'.
Now close the excel instance. In order to now extract just the number field from the cell reference which indicates the row number part, we can use the 'Extract Regex Values' action of 'Utility - String Manipulation' which requires a predefined collection called 'Named Values' having two columns called as 'Name' and 'Value'. Since we want to extract only the row number which is '50' from the cell reference data item which has the current value 'A50' from prior action. I will set up my collection as follows:
Here I have only mentioned one name called as the 'RowNumber' which will refer to the regex expression group that I will be using ahead.
Now for the action, you will need to define the text where the regex extraction will work along with the regex expression which in our case is :
Upon execution of this action, you can see the current value of the 'Named Value' collection where the Value column for the row having 'RowNumber' text is populated with the extracted value:
Referenced VBOS':
Now you can use this value where you require in your process ahead. Do let me know in case you have any queries regarding the solution.
------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily
Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
Wonderbotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com
----------------------------------
------------------------------
For your use case, I have built a workflow that you can use:.
Workflow Design:
Explanation:
Here, I am connecting to an excel file using the actions 'Create Instance', 'Show' and 'Open Workbook' actions of 'MS Excel - Extended' VBO. Once you are connected to the given file path in the 'Open Workbook' action, the Workbook Name data item will be generated which you can use in the subsequent actions.
The next action that I've used is 'Find' action of 'MS Excel - Extended' VBO where you need to provide the inputs such as handle, workbook name, worksheet name, range, find text and starting cell. What this action does is that it will active the cell where the text is found. In my case, I am searching for the text, 'Cian' which is at cell 'A50' as shown below:
Once you provide the correct parameters, use the action 'Get Active Cell' of 'MS Excel - Extended' VBO which will return you the cell reference that is currently activated which in my case is 'A50'.
Now close the excel instance. In order to now extract just the number field from the cell reference which indicates the row number part, we can use the 'Extract Regex Values' action of 'Utility - String Manipulation' which requires a predefined collection called 'Named Values' having two columns called as 'Name' and 'Value'. Since we want to extract only the row number which is '50' from the cell reference data item which has the current value 'A50' from prior action. I will set up my collection as follows:
Here I have only mentioned one name called as the 'RowNumber' which will refer to the regex expression group that I will be using ahead.
Now for the action, you will need to define the text where the regex extraction will work along with the regex expression which in our case is :
"(?<RowNumber>\d+)" and I store the result back in the same 'Named Values' collection.Upon execution of this action, you can see the current value of the 'Named Value' collection where the Value column for the row having 'RowNumber' text is populated with the extracted value:
Referenced VBOS':
Now you can use this value where you require in your process ahead. Do let me know in case you have any queries regarding the solution.
------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily
Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
Wonderbotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com
----------------------------------
------------------------------
Hi Hongjoo,
For your use case, I have built a workflow that you can use:.
Workflow Design:
Explanation:
Here, I am connecting to an excel file using the actions 'Create Instance', 'Show' and 'Open Workbook' actions of 'MS Excel - Extended' VBO. Once you are connected to the given file path in the 'Open Workbook' action, the Workbook Name data item will be generated which you can use in the subsequent actions.
The next action that I've used is 'Find' action of 'MS Excel - Extended' VBO where you need to provide the inputs such as handle, workbook name, worksheet name, range, find text and starting cell. What this action does is that it will active the cell where the text is found. In my case, I am searching for the text, 'Cian' which is at cell 'A50' as shown below:
Once you provide the correct parameters, use the action 'Get Active Cell' of 'MS Excel - Extended' VBO which will return you the cell reference that is currently activated which in my case is 'A50'.
Now close the excel instance. In order to now extract just the number field from the cell reference which indicates the row number part, we can use the 'Extract Regex Values' action of 'Utility - String Manipulation' which requires a predefined collection called 'Named Values' having two columns called as 'Name' and 'Value'. Since we want to extract only the row number which is '50' from the cell reference data item which has the current value 'A50' from prior action. I will set up my collection as follows:
Here I have only mentioned one name called as the 'RowNumber' which will refer to the regex expression group that I will be using ahead.
Now for the action, you will need to define the text where the regex extraction will work along with the regex expression which in our case is :
Upon execution of this action, you can see the current value of the 'Named Value' collection where the Value column for the row having 'RowNumber' text is populated with the extracted value:
Referenced VBOS':
Now you can use this value where you require in your process ahead. Do let me know in case you have any queries regarding the solution.
------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily
Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
Wonderbotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com
----------------------------------
------------------------------
For your use case, I have built a workflow that you can use:.
Workflow Design:
Explanation:
Here, I am connecting to an excel file using the actions 'Create Instance', 'Show' and 'Open Workbook' actions of 'MS Excel - Extended' VBO. Once you are connected to the given file path in the 'Open Workbook' action, the Workbook Name data item will be generated which you can use in the subsequent actions.
The next action that I've used is 'Find' action of 'MS Excel - Extended' VBO where you need to provide the inputs such as handle, workbook name, worksheet name, range, find text and starting cell. What this action does is that it will active the cell where the text is found. In my case, I am searching for the text, 'Cian' which is at cell 'A50' as shown below:
Once you provide the correct parameters, use the action 'Get Active Cell' of 'MS Excel - Extended' VBO which will return you the cell reference that is currently activated which in my case is 'A50'.
Now close the excel instance. In order to now extract just the number field from the cell reference which indicates the row number part, we can use the 'Extract Regex Values' action of 'Utility - String Manipulation' which requires a predefined collection called 'Named Values' having two columns called as 'Name' and 'Value'. Since we want to extract only the row number which is '50' from the cell reference data item which has the current value 'A50' from prior action. I will set up my collection as follows:
Here I have only mentioned one name called as the 'RowNumber' which will refer to the regex expression group that I will be using ahead.
Now for the action, you will need to define the text where the regex extraction will work along with the regex expression which in our case is :
"(?<RowNumber>\d+)" and I store the result back in the same 'Named Values' collection.Upon execution of this action, you can see the current value of the 'Named Value' collection where the Value column for the row having 'RowNumber' text is populated with the extracted value:
Referenced VBOS':
Now you can use this value where you require in your process ahead. Do let me know in case you have any queries regarding the solution.
------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily
Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
Wonderbotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com
----------------------------------
------------------------------
Hi Jeevan,
Yes you are absolutely right, you can also do that just by adding one line of code in the code stage of the given 'Get Active Cell' action. However, this is not there by default I think. Someone might have modified the code to create that kind of an action which you are mentioning. The code changes are as follows:
Here, I have added this line which is,
The reason why I didn't mention any code stage was because I generally use code stages if the requirement is quite complex to achieve with the existing Blue Prism artefacts or if there are concerns regarding the performance optimization. For this use case though I think general Blue Prism actions are more than enough. Also, many times organizations restrict the usage of code stages
------------------------------
----------------------------------
Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
Wonderbotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com
----------------------------------
------------------------------
Yes you are absolutely right, you can also do that just by adding one line of code in the code stage of the given 'Get Active Cell' action. However, this is not there by default I think. Someone might have modified the code to create that kind of an action which you are mentioning. The code changes are as follows:
Here, I have added this line which is,
rowNumber = ActiveCell.Row where you can map the rowNumber variable as an output argument.The reason why I didn't mention any code stage was because I generally use code stages if the requirement is quite complex to achieve with the existing Blue Prism artefacts or if there are concerns regarding the performance optimization. For this use case though I think general Blue Prism actions are more than enough. Also, many times organizations restrict the usage of code stages
------------------------------
----------------------------------
Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
Wonderbotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com
----------------------------------
------------------------------
