I am using multiple Notepad files and reading one particular value each file. That value is written to a cell in excel and then added to other values from other files.
In Excel it was easy if the value from the first file is 5, second file is 10, and third file is 20. I can write =5+10+20
@pawan , you can actually achieve this by putting the numbers from your notepads into integer variables, and then simply adding them together with an assign into another integer variable.
Kindly find the below details to get your issue resolved buddy…
Use assign activity to get the file path of each notepad file…like this
Out_filepath_array = Directory.GeyFiles(“yourfolderpath”,“*.txt”)
This would give us the array of filepaths
Use a for each loop to open each filepath in the above array with input of Out_filepath_array and type argument as string
Inside the for each loop, use a read text activity and pass the input as item from for each loop, in file path and get the output as out_text of string type…
Once you get the text use a excel application scope with file path and inside use a write cell activity with sheetname and cell range like “A1” or “A2”… Like that…
use a counter to change the number part in the range like this using a counter of integer type with default value 1, in range part of write cell like
“A”+counter.Tostring
And use a assign activity to increment the count like this
Counter = counter + 1
-------All being inside the for each loop…
Then you can add them with assign activity
Like
Out_sum = convert.toint32(item.tostring)
Out_sum_final = Out_sum_final + Out_sum
Where Out_sum_final is integer type of default value 0…
I shared the graphical representation which will help explain what I am trying to do.
@Palaniyappan Thanks for your descriptive solution and I am doing Steps 1, 2 and 3 as mentioned by you. I can have any number of text files and need the values added to one cell only.
Of course you can grt the sum in such a way buddy…like once you get the text from notepad using read text with variable out_text you need to get that numerical valu alone and can be done by this
Out_numerical = convert.toint32(out_text.split(environment.newline.toarray())(1).ToString.Split(" = ")(1).ToString)
Ths would give you the number alone buddy
2. Then you sum up and add to excel with write cell buddy… @pawan
Thanks for all your replies. The question is actually we are trying to update excel file.
Please refer this example to simplify the task.
1: Cell B5 has a value 10
2: I have a new value (15) this value has to be added in the same cell B5. NOTE: We need to add the value in the same cell so that it sums up with existing value. We are not replacing.
So the problem is that Write Cell activity replaces the existing value with the new one so B5 cell becomes 15 where is should become 10+15 that is 25. So B5 should become 25.
I know I can use this method as I am doing in the screenshot but is there any other way?
so is there any VB expression or any other method that I can use in Write cell where it sum up the value in existing value not replacing the value.???