Trying to delete the same rows and columns from multiple excel sheets with different sheet names

[Kevin_Legendre]

Hi everyone, I’m having issues deleting the same rows and columns from multiple excel sheets. I know you have to use a for each activity to loop through each excel sheet. The issue is when I put in a read range activity it requires a sheet name that defaults to “sheet1” but all my sheets have a different name so when it loops through the first file it can not recognize the sheet where the data is located. Does anyone have a solution for this issue?

Thanks!

[Em126351]

Have you tried getting all the sheet names and adding these to a list? Then using this list to loop through in your for each? Might be easier to help if you could upload you’re work flow

[Em126351]

I just checked and this should work. Use the get workbook sheets activity inside you’re excel application scope and this will save the workbook sheets names variable this creates as the list to loop through in you’re for each

[NIVED_NAMBIAR (NIVED N)]

Hi @Kevin_Legendre welcome to forum

For you query you can try this

1. Use excel application scope with the file path of excel workbook
2. iniside it u can use get workbook sheets activity to retrive all sheet names and the output would be stored in list datatype
3. use for each loop to loop through all sheet names and use Insert/Delete rows or Inser/Delete Columns activity to delete the particular row or column in all sheets by specifying sheet name

Hope it helps you

Regards

Nived N
Happy Automation
3.

[Kevin_Legendre]

Hi @NIVED_NAMBIAR thank you for the response! This is very helpful. I have sheets that are also in different workbooks that I need to delete the same rows and columns, would this query still work with sheets in different workbooks or would it require a different query?

[NIVED_NAMBIAR (NIVED N)]

Hi @Kevin_Legendre it is possible

1. For this u need to store the excel files in the folder

2. Then use file_list = Directory.GetFiles(Folder path) which retrive all file path in folder

3. Use for each loop to loop through all files (for each item in file_list)

Inside the for each use excel application scope by providing excel path as item

Then after this use get workbook sheets to retrive all the workbook sheets and store in list

After this use for each loop to loop through all sheets , inside it use insert/delete rows or columns activity to delete the rows or column of the sheet

U can get this way

Hope it helps

Mark it as solution if u got answers to ur query and like it

Regards

Nived N

Happy Automation

[Kevin_Legendre]

Hi Nived,

I’m still experiencing some issues. When I put in the Insert/Delete Columns and Rows activity it still wants a sheet name and I can not put the list of sheets in there because it needs a string value. I’ve attached my workflow for reference. Foreach sheet ex.xaml (8.2 KB)

[NIVED_NAMBIAR (NIVED N)]

Hi @Kevin_Legendre since you are looping through all the sheet of an excel file

This Insert/Delete rows activity deletes only the row of one sheet at a time not all sheets

Since you are iterating through sheets like this

For each sheet in Sheets (i had rewritten your code like that)

Iniside it , in Insert/Delete rows activity you had provided the allSheets instead of sheet
(since The insert/delete rows or columns activity delete the rows/column of one sheet at a time)

So i had rewritten the Delete/Insert rows property panel like this

Updated workflow
excel_foreach.xaml (8.6 KB)

Hope it helps you

Regards

Nived N

Happy automation

[Kevin_Legendre]

Hi @NIVED_NAMBIAR so this worked through the first excel but when it went to loop through the next file I keep getting the following error message. Do you know how to debug this error. I appreciate the help.