Insert row in excel

Help
1

Hi, is it possible to insert row in excel table?
I found this How to insert row into excel , but it is not good solution for me, because I have complicated table with a lot of formulas, so read range activity wont get those.
Do you know any solution, like invoke macro, vba code or like this?
Thanks

2

You can inject Macro(record macro to insert new Row) to excel and save that as template and use execute macro activity.

3

Follow these steps:

  1. Use the “Excel application scope” activity and select the target excel workbook.
  2. Create a txt document in the project directory with the following code:

Sub Main(RowNumber As Integer)
Rows(RowNumber).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

  1. In the Do block of the Excel application scope add an “Invoke VBA” activity.
  2. Select the txt document created in step 2 as the Code File Path value.
  3. Enter (in brackets) the integer value where you’d like to insert the blank row as the Entry Method Parameters value.
  4. Run the workflow and it should insert a blank row with matching formatting at the target Row Number. Keep in mind you’ll need to make sure the correct sheet is selected or modify the VBA to select the sheet prior to inserting the row.

Let me know if you’d like a zip file demonstrating this.

Note: Both the excel application scope and invoke VBA activities are found in the UiPath.Excel.Activities package.

4

@Marekjaros I reread your post and saw you mentioned a table so I created some VBA to insert a row into an excel table by table name. You’d want to follow the same steps I outlined changing the code in the txt file and the Entry Method Parameters value. It would now need {“SheetName”,“TableName”,RowNumber}. Hope this helps.

Sub Main(SheetName As String, TableName As String, RowNumber As Integer)
Worksheets(SheetName).ListObjects(TableName).ListRows.Add (RowNumber)
End Sub

If you’d like a zip containing an example I’d be happy to provide it.

5

@Tyler_Williams Thanks a lot for your help. It worked for me great. I also needed to copy formulas, so I edited your code a little bit. I don´t know anything about VBA, but it looks like it´s working.

Sub Main(RowNumber As Integer)
Rows(RowNumber).Copy
Rows(RowNumber).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Cells(RowNumber + 1,1).EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
End Sub

1 Like
6

I would like to know about macro. I have basic knowledge in Excel but don’t know much about Marco. How can I do that?

7

Hi, do you mind showing the vba code if I only need sheet name and row number as parameters value?

8

Hey @inyourgravity, You would follow the steps I originally outlined and use this

Sub Main(SheetName As String, RowNumber As Integer)
Worksheets(SheetName).Rows(RowNumber).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

This should insert a blank row in the SheetName provided at RowNumber in excel.

Does that resolve your issue?

1 Like