How to make the items in list as the sheet name of excel

Help Studio
, ,
1

I have few txt files, from that I extracted headers by regex after that I added that to Add to collection. Now all those headers are there in the Add to collection. how I can pass that headers as the sheet name of the excel.
If data1,data2,data3 are the headers in the Add to collection, how can I change the sheet name 1(Sheet1) as data 1.

1 Like
2

Hi @Chippy_Kolot ,

Have you tried using Rename Sheet Activity ?
image

3

Yes…Apart from Rename sheet Activity ,Is there any method…

4

@Chippy_Kolot ,

We could have a VBA code to perform the same task, but may we ask as to what kind of method are you looking for ?

Also, Is there a reason you cannot use Excel Activities ?

5

We cant directly mention the names to sheet(Excel).Based on the header name it has to take, that’s why I passed that in to list,after performing the regex

6

@Chippy_Kolot ,
Is there a Mapping available in the Text file, that Sheet1 needs to be renamed as data 1?

If there is a mapping available then we can create dictionary and store it as Key Value pair and then loop through it for renaming the sheets in the Excel.

7

The way in which you can accomplish that is if you put an Invoke Code activity inside your Excel Application Scope
Input your workbook variable from the wb variable of the Excel application scope output

And use this 3 lines inside a loop maybe which goes through the sheets that already exist, and this loop, could be inside another loop who goes through the names we want to rename.

wbToCheck.SetSheet("Sheet1",False) //Here we set the sheet to be the active one, the sheet which we want to change the name
wbToCheck.CurrentWorksheet.Name="Sheet2" //By modifying this property, we change the name
wbToCheck.SaveWorkbook //Very important, we need to save the changes