My Entire project base on SAP.
In the First sheet , the data will be updated manually,
In the second sheet , data downloaded from SAP from corresponding Sales order.
Then I have updated the Index no in sheet 2.
Based on sheet 1, BOT Will search data from sheet 2 and written in the Sheet 3 ( completed)
Then I will do Vlookup and enter the Index no in the sheet 3.
There will be multiple Sales order in Sheet1 during live project.
That is what I have mentioned before. If you want to have output value - CellValue - you need to specify RowIndex and Target Column/ColumnName.
I have done simple component for your issue. Please check the attachment with excel file. Try to run it on your end and let me know if that is what you have been trying to achieve.
Keep in mind that some values needs to be converted toString to make it working properly.
In our case the output value is Double so I have just used Assign to convert it to string and then write to the excel (Sheet3).
I have noticed that the index is the same row number which is RowIndex output from Lookup activity. If it is always like that you do not even need to read Index column but just write RowIndex to Sheet3.
Wouldn’t be easier to write Index column in Sheet1?
" genericIndexfromExcel" is just the variable which is Generic type - I choose generic type as we do not know sometimes what type is the data in the excel file that you want to get. In our case it is double (number) so you can also put there a variable type double. The name is build using convention like this: ‘type+VariableName’.
intRowIndex is the variable type Int32. It is empty because it is used by Lookup activity to store row index in which the searched values was found. It has to be created as a placeholder to be able to get cellvalue as output.
Cell_value cannot be Int as it is Double type after getting it from Excel.
that is why I choose Generic type, it allow us to store almost any type in the Generic.
this is completely wrong. You are trying to search column? You have converted the whole column to string instead of just the value from the row.
Please follow my solution and try to debug more. Use log messages and debugging as it looks like you are missing basic checks
Ok, we are almost there. We made a small mistake I suppose. DataTable is indexing from 0 but Excel first row is 1. In that case we need to add 1 to RowInd first and then put it in Write Range
Please put one more Assign an To RowInd giv value: RowInd+1 .
Right to keep headers you neeed to add 2 to rowInd.
I have no idea why there are two rows, you need to show me the entire flow.
May be you have duplicates in the source table or so.
How you write columns A,B,c and D ?
So, there are many unnecessary activities like Read and Write range multiple times.
also Excel scope is duplicated unnecessary. I think you are getting two the same rows because either there are duplicates in Overriding table or because you have to many ForEachRow inside each other.
Try to split the flow and do the data preparing separately step by step (each sheet in separate sequence.
You do not need to write cell/range each time you add a row to datatble, prepare one datatable then write range to excel, then prepare another table separately and write it the excel. Also once you wrote the table to the excel you do not need read it right after writing as it is still in your variable.
If you would like to, please register there and I can offer you a mentoring. We would be able to schedule a short meeting on which I could review your solution and give you more tips, suggestions and so on
