I am trying to work on an Excel file stored on One Drive where I need to insert a new row and then copy formulas from a cell in existing row to the cell below that using Office 365 Excel package. When we do that manually using Ctrl+C & Ctrl+V, the formula is updated automatically based on the cell where we are pasting it. Using the package commands, the formula is pasted as it is. Is there an easy way out for this?
ASK- The formula from cell D2 (A2+B2) when pasted to D3 should give us (A3+B3) and not (A2+B2).
Hi @RuchirAgarwal
You can use the simulate key strokes to copy the formula and to move around GoTo cell.
@RuchirAgarwal ,
You can use the Set Cell Formula action with the required formula for this within a loop. For each iteration, increment the row number value.
I am trying to work on an Excel file stored on One Drive where I need to insert a new row and then copy formulas from a cell in existing row to the cell below that using Office 365 Excel package. When we do that manually using Ctrl+C & Ctrl+V, the formula is updated automatically based on the cell where we are pasting it. Using the package commands, the formula is pasted as it is. Is there an easy way out for this?
ASK- The formula from cell D2 (A2+B2) when pasted to D3 should give us (A3+B3) and not (A2+B2).
Hi @RuchirAgarwal
You can use the simulate key strokes to copy the formula and to move around GoTo cell.
Hi @Zaid Chougle,
Keystrokes would not work with Office 365 Excel commands as we do not open the excel in this case.
@RuchirAgarwal ,
You can use the Set Cell Formula action with the required formula for this within a loop. For each iteration, increment the row number value.
Hi @Padmakumar,
Thanks for replying. But we have lots of complicated formulas that are there in an existing excel from the Business. Now if we do it this way, we will have to identify which are dynamic ones and update row numbers accordingly which might be an error prone task. Probably, will have to store all the formulas in a config file stating the dynamic values and replace those at runtime & paste. However this would have a downside that the business cannot change the formulas on their own in the spreadsheet. Probably will have to live with that.
Thanks,
Ruchir Agarwal
HI @RuchirAgarwal ,
You can decide where you need to keep the formula part. But according to your requirement, this is the best option.
Could you please confirm whether you are working on a single Excel file which already contains all formulas, or are you creating a new file during each BOT execution?
Dear @RuchirAgarwal,
When you are copying and pasting formulas in Excel using the Office 365 Excel package in Automation Anywhere, the formulas will not be updated dynamically like they would be when you do it manually. This is because the package commands are designed to replicate the actions you would take when working with Excel manually, rather than providing more advanced functionality.
To achieve the behavior you are looking for, where the formulas are updated dynamically based on the cell they are pasted into, you will need to use a different approach. One possible solution would be to use the "Get Cell Value" and "Set Cell Value" commands to read the value of the source cell and then update the formula in the destination cell accordingly. This would involve writing a custom script or logic to process the formula and update it as needed, rather than simply copying and pasting it.
Another option would be to use a different type of automation tool that is better suited to working with formulas and other complex Excel features. For example, you could use a tool that provides a higher-level API or other advanced features for working with Excel, which would allow you to manipulate formulas and other elements more easily and dynamically. This would require more advanced knowledge and skills, however, and would not be possible using the Office 365 Excel package alone.
Regards,
Dear @RuchirAgarwal ,
Another approach could be to convert the whole spreadsheet content to “Table” and formulas would be updated automatically (bot would only need to fill in those columns that are not formulas)
Hi @Andoni , Converting the spreadsheet to a table makes things a lot easier. The formulas are copied automatically whenever data is added to a new row.
