i want to work on two different excl sheets and subtract the two columns one from one excel sheet and another from another excel sheet and place the output in one of the excel sheets can i know how to work on this workflow
these are my excel sheets i want to subtract ‘order qty’ from ‘inventory’ column and place the ouput in second excel sheetHi @Manisha541
Use multiple for each row
row(“result”).ToString=CDbl(row(“QTY To Prov”).ToString)-Cint(row(“Inventory”).ToString)
Thanks
Ashwin S
i had used this is assign but its giving error ‘Sequence.xaml (9.9 KB) inventory’ column does not exists in table
Hello,
Pay attention to the spaces you put either in your Excel and your Studio. For instance you have a space in the expression you put in your studio but I’m not sure there’s one in your Excel column name.
Regards
@Manisha541 are they always going to be in the same order? Meaning row 1 from excel1 needs to be subtracted from row 1 in excel2, etc.
If that is the case, you can achieve this with a single for each row loop. I’m assuming you want the output in a new column called ‘Output’ in the excel sheet containing inventory.
1. Read Range activity for both excel files. Save as dt1 (excel with order qty) and dt2 (excel with inventory)
2. Add Data Column to dt2 of type integer named "Output"
3. For each row in dt1 (save the index output property of this activity to variable called rowIndex)
a. Assign dt2.rows(rowindex).item("Output") = CInt(dt2.rows(rowindex).item("Inventory")) - CInt(row.item("Order Qty"))
4. Write range activity to write dt2 back to excel sheet
current.xlsx (10.0 KB) Report.xlsx (10.7 KB)
these two are my excel sheets i want the output in the ‘report’ excel sheet in the i column ‘qty to produce’
inventory -order qty output in i column of report excel sheet .so plz help me @Dave
I attached it below. Keep in mind the assumptions I made above. If there is any possibility that the rows don’t match up, then you should use a linq query instead - this is just quick & easy to understand which is why I did it this way.
NOTE: you had some garbage data in your “report.xlsx” sheet. There is 2 cells with data at K243 and K244. Because of this, uipath is reading the excel as a datatable with 11 rows, then a bunch of blank rows all the way down to excel row 244. I put in a try-catch in the for each row to ignore this, but if it’s possible to get garbage data like this then i would also go with a linq solution
Manisha541.xaml (9.1 KB)
thanks @Dave
Report.xlsx (11.9 KB)
but in report the the whole columns it is printing again i want only output of qty to produce
Manisha541.xaml (8.0 KB)
@Manisha541 it’s because your write range is at starting cell i1. Change it back to blank or a1
Thank you so much @dave
now its executing perfectly
Sequence.xaml (6.4 KB)
it is throwing error exception has thrown by target invocation
current.xlsx (10.0 KB)
report.xlsx (10.5 KB)
what may be the error? @dave
@Manisha541 Why did you change the sequence again? You put the write range within the for each loop. It needs to be outside the loop, so it just writes it all to excel at the end one time
Manisha541.xaml (8.0 KB)
this is the file before one ,but i dont know y it is not executing properly
Sequence.xaml (6.4 KB)
so i tried it without try catch but getting error exception thrown by invocation
i had change the write range out of the for each
Hello Manisha,
What’s the exact error message you have ?
I am getting error of ‘exception thrown by the target of an invocation’
Exception type:system.Reflection.targetinvocationexception
check my sequence.xaml file @Martin_CHAUDEY
@manisha541 I ran the ‘Manisha541.xaml’ file just now and it ran first try without any errors.
The ‘Sequence.xaml’ has the try-catch removed inside the for each loop. That is going to cause issues as I previously mentioned because of the garbage data found within the excel files.
@Manisha541 this screenshot is different from both the ‘Sequence.xaml’ and the ‘Manisha541.xaml’ you provided in your last comment. There is an extra assign within the for each loop that looks like it is incrementing CurrentRowIndex. That is going to cause an error because it is already being autoincremented from the for each activity’s output. Also, it needs a try-catch in there because of all of the blank rows found in your excel files like I stated in earlier comments
some one suggested me to increment current row index so i incremented it ,but i have removed it now
i understood now about the garbage issue.
plz help me to solve the ‘manisha541.xaml’ file with the ‘exception has been thrown by the target of an invocation’. @Dave
The index is auto incremented when it’s coming from the for each activity output. I am not sure why you got that error since I just downloaded and re-ran it now without any errors at all. What changed between now and ~20 hours ago when you said it was working perfectly?
