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:
- Howto convert datatable column to array? How do I know what the columnname of the datatable is using assign to convert?
- 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,
Store the sheet names in an array.
For Each sheetName in arrSheets
Insert/Delete Rows for sheetName
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,
Get the excel application scope “OUTPUT”. this gives us the out variable with name “Out_workbook” with value having in workbook datatype.
With this workbook datatype, we can get the sheets name like
sheetnames = Out_workbook.GetSheets.ToList()
while sheetnames is string array
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
What do I insert in the “Insert/Delete Rows” activity under “Sheetname”? The array which I have called “arrSheets”?
For each item in arrSheets
item is the SheetName.
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?
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.
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
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…!
thanks! I have redefined my questions
I keep getting the message that Insert/Delete rows “sheet does not exist”.
Arrayname(1) does not work as sheetname.
Is this a VBA input you have written?
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…
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?
I think i have mentioned that in the same workflow as well, no problem here is the screenshot to add rows to the sheet.