Excel - Insert rows on multiple sheets

Help
1

Hi,

My goal is to read a range containing sheetnames in excel-sheet 1, and insert 7 empty rows in excel-sheet 2 which contains those sheetnames.

I have the following questions:

  1. Howto convert datatable column to array? How do I know what the columnname of the datatable is using assign to convert?
  2. Howto to let UIpath insert rows on every excelsheet of which the name is in the (converted) array? The 'insert/delete rows" activity needs a sheetname. How can you loop through the sheetnames which have been read?

If possible, please show me an example project, because I have difficulties placing the text answers in UIpath.

Thank you in advance,

2

Store the sheet names in an array.

For Each sheetName in arrSheets
{
      Insert/Delete Rows for sheetName
}
1 Like
3

Hi @John_Brown,

Great…!

So lets take like you have a excel with many sheets and using excel application scope to the read or write data to that excel… then kindly execute as below,

  1. Get the excel application scope “OUTPUT”. this gives us the out variable with name “Out_workbook” with value having in workbook datatype.

  2. With this workbook datatype, we can get the sheets name like
    assign activity
    sheetnames = Out_workbook.GetSheets.ToList()
    while sheetnames is string array

  3. Use for each loop to insert data to those sheets by mentioning the sheetname like
    sheetnames(sheet name), if we know the names else pass the index like sheetnames(sheet index) starting from 0.

Hope this would help you

cheers…!

1 Like
4

Thank you.
What do I insert in the “Insert/Delete Rows” activity under “Sheetname”? The array which I have called “arrSheets”?

5

For each item in arrSheets

Here item is the SheetName.

6

sorry, I don’t understand. I have assigned my datatable values to an array, which now contains the sheetnames I need. Now I want to let the robot insert rows on those specific sheets.
“Insert/Delete Rows” requires a sheetname. What do I put in the Input->sheetname field?

7

thank you, it helps a lot!
Last thing that is not clear is how I can insert rows on multiple sheets as the “insert/delete rows” requires a sheetname.

8

Great…!

So if you want to delete or insert rows, you are right we need sheet name as input…

Once you get the “sheetname” array ready, you can pass the sheetname input with this value like
input:
sheetname = arrayname(index of the sheet or column name as string)
where array name is name of the our array of sheets obtained from workbook variable…

Hope this would work…!

Cheeers…!

9

thanks! I have redefined my questions

10

I keep getting the message that Insert/Delete rows “sheet does not exist”.
Arrayname(1) does not work as sheetname.

11

Is this a VBA input you have written?

12

Hi @John_Brown

Buddy take this xaml for reference, how to get the sheetname from a workbook and how to use it in insert or delete rows with sheetname as input.

excel.xaml (8.8 KB)

Book11.xlsx (15.6 KB)

Hope this would help you…

Cheers…!

1 Like
13

Thank you very much @Palaniyappan!
Now I have the sheetnames in a string.
How can I insert rows in all the sheets in this string?

14

Buddy …!

I think i have mentioned that in the same workflow as well, no problem here is the screenshot to add rows to the sheet.

image

Cheres!