Hello,
I have an Excel (template), in which I have a robot that inserts values for every row of that Excel sheet. Those values come from a website, and they are already read and inserted into a queue. That is why, in a performer process, I am reading the values that are in the queue and inserting them into the Excel file (that will be the output file).
In the queue I have stored information like the PNR number (which is the reference and cannot be repeated) and the row number (from the original Excel template that comes as an input).
Now, the issue that I have is that I need to insert the data row (that comes from the current queue item as I said) into the Excel file, but, in order to do that, I need to get the row number that contains a certain value in the first column (PNR), so that I can do the write range writing only into that row number:
As you can see in the picture above, it works for a “static” row number, meaning that if the rows do not change, I can just do a Write Range as showed above, by concatenating the column (B) and the row number that was obtained from the queue.
However, this does not work when there is new data, different from the original input. The reason why it does not work is because I need to insert the new data into Excel (which I have done with the activity Insert Rows), and then, insert all the Data that comes from the current queue item into the new Excel row that has been created. The problem is that, by doing that, the screenshot that I showed before does not work anymore, because the row index of the Excel will differ from the real one (obviously, since I created a new Excel row before).
That is the reason why I need the get the current row index of the current queue reference item. I could try to do it by reading all the Excel data, getting a datatable as an output, and looping through that datatable to get the row number that contains the string value, but the problem is the the Excel could quite quite large (more than 600 rows), so doing that for every single item could take some time.
When it comes to the row saved in the queue, I am saving the row in which the reference number was saved in the original Excel template (so, an integer value, 8 for example), and, in case it is a new value (a value that did not exist in the original template), I am saving the row number with a dot. For example 8.1 which means that it should go right after where the original row 8 was in the input Excel template; or 9.2, that would mean that it is a row that would have to go right after 9 and then 9.1; because 8.1 is a “child” of 8 and 9.2 is a “child” of 9. I already got the left and right part by checking it with contains and, in case there is a “.”, getting the left part and the right part with substring.
In summary, I would like to create a VBA code (which I will call with “Invoke VBA” inside an Excel Application Scope) that, given an Excel Sheet (even if there is only one) and a String (that will be in the first column, but it would be nice if it can look over the whole Excel sheet just in case it changes in the future), it will return the row number in which that value is located, and 0 if it does not find anything.
This is the Excel file:
Here are the propierties of the aforementioned Add data Row:
And here is the array report, that comes from a queue item as I said:
I would really appreciate your help. Please let me know if you need anything else.
Thank you.
ashokkarale
Yoichi
postwick
Anil_G
kishan.savaliya
Laura_Hubbard
Tselisehang_Molebatsi
Byrnecut
Lahiru.Fernando
wahyumrizqi