Hi @Adrian_Brinas ,
Thank you for your reply. If possible, could you please provide a sample workflow with my scenario. This may help us a lot .
Thanks in advance
Hello, I had tried myself the folowing working sequence:
First use the Build Data Table activity.
It should be created a new empty data table with 2 columns:
ColumnName and Formula, both columns of type String.
Also create a new variable data of type: System.Data.DataTable
Please see the next picture:
Next you’ll have to use the Use Excel File and read Formula.xlsx
There’s nothing special to this activity, just select the file.
In the body of Use Excel File add For Each Excel Row activity.
Uncheck the option Don’t save after each row
But Has headers should be checked to skip the first title row.
In the body of For Each Excel Row add a new If activity
This will check if the read value string is not empty.
The condition for this If statement is the following:
CurrentRow.FirstCell <> ""
At this If activity add a new Add Data Row activity.
The DataTable is the new created data variable.
Keep empty the Add Data Row argument and set Add list as row.
Here you can write a list having the 2 required items:
{ CurrentRow.ByIndex(0).StringValue, CurrentRow.ByIndex(1).StringValue }
Now insert another Use Excel File for the another Result.xlsx file.
This time you should select the Save Option to update the file.
In this new Use Excel File place a Set Variable Value
This will set a new Integer (number) variable for column index.
Also here use a new For Each Data Row activity to parse the data.
Use for each CurrentRow in data and the Column Count can be 2
Then add 2 Write Cell activities.
For the first one:
The range is:
Chr(65 + index) + "1"
Sheet is: Sheet1
and the Value is:
CurrentRow.ItemArray(0).ToString
For the second one:
The range is:
Chr(65 + index) + "2"
Sheet is: Sheet1
and the Value is:
"=" + CurrentRow.ItemArray(1).ToString
Don’t forget to increase the index column number at the end.
Just use a Set Variable Value:
index = index + 1
The difference between Value and Formula is just an equal sign: “=”.
Please see the next picture:
This is all.
I also made an archive containing this project:
Excel_Formula.zip (197.7 KB)
Hope it would be of any help, Adrian
Hi @Adrian_Brinas ,
Thank you for your valuable reply. In my scenario, I want to apply the formulas to data table not in excel. Is there any approach to apply the formulas to data table.
Thanks in advance
Hello, I think Formula is just a property of a cell in Excel.
I didn’t know, that Formula is just a ‘read-only’ property.
And also the Item() function doesn’t work, as I knew before.
If you want to read formulas from DataTable, I don’t know…
You can set formulas right from beginning with an Equal sign.
Or in the Formula.xlsx file write formulas string with an “=”
Or check the string, if it contains already an “=” character.
Just can keep formulas as String values in the data DataTable.
Best regards, Adrian
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.