Excel Delete Columns from a dynamic sheet tab (Source)

Hey everyone, I am trying to use the “Delete Column” activity to remove column E from within my file - all works well but the problem is the sheet name will change week to week.

So currently, in my “Source” I have the sheet name

File.Sheet(“ServiceUserReport_20211115”)

This is the only tab in the workbook, but it will change its name week to week. I tried and failed adding an aterix as below. Any help most welcome

File.Sheet(“ServiceUserReport_*”)

@CMB80

If the sheet name will regularly change, create a variable for it as an input argument, that way in UiPath Assistant you can write the Sheet name before the program is ran.

Kind Regards

@CMB80

Create Workbook variable for your Excel Application Scope activity and pass Sheet name as varWB.GetSheets(0) instead of hard coded sheet name.

Hi both, thanks for your replies. Can you provide example xaml of how to do that?

Will your excel file have only one sheet?

If so, @lakshman’s suggestion is better.

Example:

image

Hello, yes only one sheet - but always with a different name

See the answer above I have given you. Set strFilePath to the path of the excel workbook.

Or do you mean the file name will change?

Hello there,

Trying using a variable for creating the name of the sheet dynamically.

Use an assign activity

SheetName(Variable Name)

For the value - “ServiceUserReport_” + now.AddDays(x).ToString(“yyyyMMdd”)

Note : instead of x replace with number of days you wanted to add -7 will give date fom last week and 7 will give date from next week, change the number according to your need.

Hope this little tip helps. Happy automation.!!

I don’t have read range that appears the same as you. Mine has Workbook path then below it has “Sheet1” and “A1:A2"”

Maybe it is easier if someone has xaml already for removing a column from an excel worksheet?

The filename is always the same - but the sheet / tab name will be different. That is my problem

Thanks - the problem is when I try to use the Delete Column activity is wont recognise “SheetName” as a variable - it gives a red exclamation mark

image

That’s because you are using MDE, the syntax would be:

Excel.Sheet(SheetName)

@Che thank you - however using that still throws an error here

image

Please send a picture of your excel sheet

Ok, but I had to blank out sensitive information. All I need to do is delete column E - but the tabname is not recognised

Ensure there are no whitespaces in the column name in Excel.

But that is how the column Name is within the received file. The process works correctly when I specify the tab name “hard coded”

And actually removing the spaces in the column name I get the same error

What I meant was after the column name, sometimes people create whitespaces by accident.

Could you send a picture of your full Excel sequence?

If the error is still persisting, I am not sure what the problem is. The only thing I can suggest is reading the excel sheet into memory, deleting the Data Column and then rewriting to the Excel sheet.

image

Kind Regards

Hello,

Issue is we can’t give string variable in Source Parameter of the activity. Use the variable as shown in the screenshot i have attached.

Let me know if you face issue after trying.!

Cheers.!! Happy Automation.!!!