Change Excel Sheet Name

Help Activities
, ,
1

Hi team,

This is what I have:

  1. Browse For File

    • Output - SelectedFile: “QuotePath”

  2. Excel Application Scope

    • WorkbookPath: “QuotePath”
    • Output - Workbook: “QuoteWorkbook” (This variable is the type WorkbookApplication)

  3. Get Workbook Sheet (inside Excel Application Scope)

    • Input - Index: 0
    • Output - Sheet: “oldSheetName”

Here, I have the sheet name saved in “oldSheetName”

Now, I want to change the name of the sheet. How can I do it?

Thank you, all!

2

With the newer Excel options we got a rename Sheet Activity

A renaming of the first Worksheet would look like this:

grafik
grafik581×723 21.7 KB

3

You can also do it with very simple code.

image

image
image764×211 9.17 KB

CheckName is there because the goal was to rename the sheet only if its current name is something specific.

But in the end all you have to do to rename a sheet is:

workbook = New XLWorkbook(SourceFile)
workbook.Worksheets(0).Name = NewName
workbook.Save

Adjust that middle line to select the sheet you want to rename.

4

Hi @mardoza

FYI, another approach

image
image549×537 12.7 KB

Regards

5

@mardoza

Please use this approach to get the sheetnames

6

Hi @mardoza

Use the below code to get to get the first the s=first sheet and rename that sheet:

Dim app As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
Dim firstSheet As Microsoft.Office.Interop.Excel.Worksheet

app = New Microsoft.Office.Interop.Excel.Application()
app.Visible = False
app.AskToUpdateLinks = False
wb = app.Workbooks.Open(ExcelFilePath, False, False)

If wb.Sheets.Count > 0 Then
    firstSheet = CType(wb.Sheets(1), Microsoft.Office.Interop.Excel.Worksheet)
    sheetName = firstSheet.Name

    ' Rename the first sheet
    firstSheet.Name = "type your required NewSheet Name" ' Replace "New Sheet Name" with your desired name
Else
    ' Handle the case where there are no sheets in the workbook
    ' You can set a default value or raise an error as needed.
    sheetName = "No Sheets Found"
End If

wb.Save()
wb.Close()
app.Quit()
'System.Runtime.InteropServices.Marshal.ReleaseComObject(wb)
'System.Runtime.InteropServices.Marshal.ReleaseComObject(app)

Below are invoked Arguments:

image
image1920×526 14 KB

In ExcelFilePath argument pass the excel file path. Create a variable sheetName in the process and pass that in sheetName argument you can get the sheetname out in a variable you can use the new sheet name in the next below workflow i.e for example Read Range Workbook.

Or

Since you have done half of the process the below link will help you complete the rest too

Regards

7

To rename an Excel sheet in UiPath, you can use the Invoke VBA activity within an Excel Application Scope. Here’s how you can do it:

  1. Excel Application Scope*: Use this activity to open the Excel file where you want to rename the sheet. Set the WorkbookPath property to your “QuotePath” variable.
  2. Invoke VBA*: Place this activity inside the Excel Application Scope.
  • Write a VBA script to rename the sheet. Here is an example of a VBA function that renames a sheet:

vbaCopy code

Public Sub RenameSheet(oldName As String, newName As String)
    Sheets(oldName).Name = newName
End Sub
  • Save the VBA script in a .txt file on your machine.
  1. Invoke VBA Properties*:
  • Set the CodeFilePath property to the path of the .txt file containing your VBA code.
  • Set the EntryMethodName property to the name of the subroutine in your VBA code, e.g., “RenameSheet”.
  • In the EntryMethodParameters, pass an array with the old and new sheet names, e.g., {"oldSheetName", "NewSheetName"}.
  1. Execution*: Run the workflow. The Invoke VBA activity will execute the VBA script and rename the sheet accordingly.

Here’s an example of how to configure the Invoke VBA activity within UiPath:

vbCopy code

Explain

Invoke VBA
  CodeFilePath: "C:\PathToYourVBAScript.txt"
  EntryMethodName: "RenameSheet"
  EntryMethodParameters: {"oldSheetName", "NewSheetName"}

Please note that to use the Invoke VBA activity, you need to have ‘Trust access to the VBA project object model’ enabled in your Excel settings.

1 Like
8

That’s a lot of code when you can just do…

workbook = New XLWorkbook(SourceFile)
workbook.Worksheets(0).Name = NewName
workbook.Save

1 Like
9

Hi @ppr, thank you for the response. I tried this before creating the topic. Sadly, I can’t find a way to use this solution in the background, so it doesn’t meet my needs.

Hey @postwick, thank you for your approach. Unfortunately, it is giving me this error:

image
image1315×1112 61.7 KB

Not sure how to handle it.

PS: Thank you the rest of ppl for your responses!!

10

Did you create the necessary arguments? Also, it shouldn’t be inside Excel Application Scope. Maybe that’s what is causing the error. Your Excel Application Scope has hold of the file and the Invoke Code is erroring when trying to access the file.

1 Like
11

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.