hi @vvaidya,
do you have any solution on it?
for me the best solution until now is:
Case 1: When you can work with excel tables when you write new rows normally will extend the formula properly.
Case 2: Just use a work around using copy paste, use the first row as “template” then copy it to a selected range it work fine with 1 to 1000 rows.
In case you are good with code… just go with code its 20000 better
Yes I did thanks.
Try to use invoke code instead and use this inside.
Worksheet.Range(“A1”).End(XlDirection.xlDown).Offset(1, 0).activate() 'Specifies last row
lRow = excel.ActiveCell.Row
Worksheet.Range(“C1”,“C”&lRow).Formula = “=A1+B1” ‘Insert the formula based on the range’
Hi @acaciomelo,
I need to calculate formula in excel-1 & I need to write the output in another excel
Can you suggest any way for that
Hi All,
I have a simple solution for this and i tried with 3 lakh records and it worked for me.
I used Invoke Code activity to do all this formula stuff.
Here is the code which i used.
obj = wb.Sheets.Add(Before:=ws,
Count:=1,
Type:=Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet) CType(obj,
Microsoft.Office.Interop.Excel.Worksheet).Name = "Reversing Accruals"
ws = CType(wb.Sheets("Reversing Accruals"), Microsoft.Office.Interop.Excel.Worksheet)
ws.Activate()
ws.Range("A1").Cells(1, 1) = "Projects with Reversing Accruals"
count = 1
With ws
For j = 2 To ReverseAccrusalRange - 4
ws.Range("A" + j.ToString).Formula = "=VLOOKUP(ReverseAccrusal!J" + j.ToString + ",Cost!$J$2:$K$" + CostRange.ToString + ",1,FALSE)"
Next
End With
I have used invoke code activity to apply formula to excel using below code it work fast for me, hope will do same for u.
Try
app = New Microsoft.Office.Interop.Excel.ApplicationClass
workbook= app.Workbooks.Open(FilePath)
worksheet = CType(workbook.Worksheets(“Sheetname”), Microsoft.Office.Interop.Excel.Worksheet)
Dim rows_Count As Integer = worksheet.UsedRange.Rows.Count
For i As Integer = 0 To rows_count
worksheet.Range(“Column to be entered” + i.ToString).FormulaLocal =“Formula to be entered here with = sign”
Next
app.DisplayAlerts = False
workbook.Save
range = Nothing
worksheet = Nothing
workbook.Close
workbook = Nothing
app.Quit
app = Nothing
Catch ex As Exception
End Try
Before sending the hotkey “Ctrl + D”, how to select the required range? I don’t need the whole column to be selected but just to the last row of my data. I tried “Select Range” then send hotkeys but doesn’t work.
Any idea?
Hello folks,
My requirement is to generate the Excel report every day. My doubt is once the execution is finished the excel row counter will back to its default value. If execution starts again on tommorow how does it know it should write the cell value in A4 or A5 etc. Could some suggest on the same
At the end of the workflow export the row number to an external file, like an excel or txt file.
The workflow can then pick this up every day as its starting row when the workflow is started again.
Where do we define the start and end range? Moreover, when I use this code, it gives an error that Sheet, direction, IRow, etc is not declared. Please advise.
Regards,
Deepak.
hi KEntwistle,
Can you let me know how you did it.
means used the macro in uipath
Apart from the macro you can also use data-table operations to write excel formulas.
U can use Autofill Range. Much Quicker
Use invoke VBA instead of macro.
write code in text file with subroutine name then put that subroutine name in invoke VBA and give the path of that text file
The easiest way for this is to set formula for first cell using Write Cell Activity n then use the Auto Fill Range activity and mentioned the range over there, it will simply apply the formula to given range.
No for loop no send hotkey, it will get done with less response time.
which formula use and put Auto fill range activity
–>Workbook Read Range
–>Excel application Scope
→ Write cell (Formula)
→ Fill Range ((C1)(C1:datatable.rows.count.tostring)
It works !
Great it works for me