Excel renaming

#1

Hi,

i have one excel file with mutiple sheets
Want to rename the sheets name

excel_sheet

want to change as these names(As names may add…so have to dynamic)

excel_rename

How we can try?Is there any activity

Save as .xlsx
#2

Hi,

Anybody have Solution?
Is anybody have faced similar
Can Someone guide me on this?

#3

Hi,

Here is one idea:

Using an Excel Scope use a WorkbookApplication type variable as the output parameter, which will allow you to look at every sheet name in the file. Then, you can perform an Alt-key combination to rename each sheet.

Let’s use “wb” for Workbook and sheetNames for list of sheet names…for the psuedocode below.

Assign sheetNames as Array = { "IPS","IMS","ISP","EMI","LSE","SEO" }
Excel Scope (with wb being output variable)
    For each sheet In wb.GetSheets
        If sheet<>sheetNames(Array.IndexOf(wb.GetSheets.ToArray,sheet)).ToString
            Select Range => A1  ...   sheet
            TypeInto "[k(alt)]hor"+sheetNames(Array.IndexOf(wb.GetSheets.ToArray,sheet)).ToString+"[k(enter)]"

The pseudocode loops through each sheet in wb, selects the sheet, then performs Type Into to do rename sheet from Format menu option using the Index of the sheet for the index of the sheetNames array -EDIT: only if the sheet isn’t already the correct name (shown by the If statement).

I hope this helps.

Regards.

#4

I have done with this, Some error am facing
During variable type…Pls help with this

Rename_1
Rename_1.PNG1608×873 40.9 KB
Rename_2
Rename_2.PNG1230×828 29.9 KB
Rename_3PNG
Rename_3PNG.PNG705×722 18.4 KB

#5

Hi,

see if adding .ToArray next to wb.GetSheets fixes the error.

Array.IndexOf(wb.GetSheets.ToArray,sheet)

I will fix my previous post. Thanks.

Regards.

#6

in, if(sheet<>sheetNames(Array.IndexOf(wb.GetSheets.ToArray,sheet))
i mentioned, is it right? and variable type…
bcz still am facing error, Thanks

#7

Hi,

Can you be more specific on what the error says that you are still getting? The line you show looks correct.

Also, you will need to remove the “Array” and “sheet” variables that you created. Array is a vb.net function builtin and not a variable, and sheet is defined in the For Each loop so doesn’t need to be declared in variables.

Thanks.

#8

This is am facing

Condition
Condition.png1920×1080 174 KB

#9

Hi, thanks.

Try using sheet.ToString instead of sheet on that line
If(sheet.ToString<> …

or if that doesn’t help, try doing what the error says and change <> to IsNot
If(sheet IsNot …

Regards.

#10

sheet IsNot sheetNames(Array.IndexOf(wb.GetSheets.ToArray,sheet)) now its not showing error,

But while running, its throw this

if
if.PNG723×593 29.4 KB

and while giving Select Range, specified range with " ", Is it Correct?

#11

Nope, but you almost got it. So I pulled up Studio and tested this and you just need to use sheet.ToString<>

Here is an image:

image
image.png802×265 10.2 KB

That worked for me.

Thanks and regards.

#12

Oh also check the TypeInto activity, it should be:

“[k(alt)]hor”+sheetNames(Array.IndexOf(wb.GetSheets.ToArray,sheet)).ToString+"[k(enter)]"

#13

You can use VBA code to rename multiple worksheets by the name you want at once.

#14

Am still facing Error in If Condition
and Type Into, its no error.
Can u send the xaml file, or send the screen shot fully.

#15

This is my XAML file, what i did wrongRename.xaml (22.7 KB)

#16

Hi there @Sharanabasava

You are using the variable “items” as your sheetNames variable so replace “sheetNames(” to items(
and that should take care of the validation error.

Below is the screenshot, where I highlight the sheeNames type says List<WorkbookApplication> which is wrong, however you are not using that as your variable so you can delete it completely. I also highlight items under variables because that is the variable you are using to store your sheet names in.

image
image.png916×339 9.6 KB

I hope that makes sense. So, just replace sheetNames( in both your if condition and Type into activity to items( where you are assigning your sheet names in.

fixed version: Rename.xaml (22.3 KB)

Regards.

1 Like
#17

Programmatically renaming - test it

excelRename.xaml (11.4 KB)

1 Like
#18

Hi,

Thanks to all.
and Special Thanks to ClaytonM and Vvaidya:grinning:

1 Like
#19

Hi Everyone - I know this is a very late post, but do you think its possible to rename excel off a list? The List could be contained within the excel sheet if needed.

Right now this specific solution goes off a predetermined list. I would like it to be dynamic, that goes off any list.

Thanks!

#20

Hi ,

This will help you.

Regards
Balamurugan.S