Excel: how do you change sheet name in Excel file

Question

How do you change the name of a sheet in excel? This is what I have so far but can't find the method which changes the sheet 'Title'. Please do not delete this question:&nbsp;&lt;SPAN class="token keyword"&gt;function&lt;/SPAN&gt; &lt;SPAN class="token function"&gt;changeExcelTabName&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;(&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;)&lt;/SPAN&gt;
&lt;SPAN class="token punctuation"&gt;{&lt;/SPAN&gt;
  &lt;SPAN class="token keyword"&gt;var&lt;/SPAN&gt; excelFile &lt;SPAN class="token operator"&gt;=&lt;/SPAN&gt; Excel&lt;SPAN class="token punctuation"&gt;.&lt;/SPAN&gt;&lt;SPAN class="token function"&gt;Open&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;(&lt;/SPAN&gt;&lt;SPAN class="token string"&gt;"C:\Users\chk\Downloads\Task groups with no tasks_2024_3_11 16_59_34.xlsx"&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;)&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;;&lt;/SPAN&gt;
  excelFile&lt;SPAN class="token punctuation"&gt;.&lt;/SPAN&gt;&lt;SPAN class="token function"&gt;SheetByIndex&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;(&lt;/SPAN&gt;&lt;SPAN class="token number"&gt;0&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;)&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;.&lt;/SPAN&gt;Title
  &lt;SPAN class="token operator"&gt;&lt;&lt;/SPAN&gt;the code to change sheet title would go here&lt;SPAN class="token operator"&gt;&gt;&lt;/SPAN&gt;
  excelFile&lt;SPAN class="token punctuation"&gt;.&lt;/SPAN&gt;&lt;SPAN class="token function"&gt;Save&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;(&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;)&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;;&lt;/SPAN&gt;
&lt;SPAN class="token punctuation"&gt;}&lt;/SPAN&gt;

torus · Accepted Answer

I ended up just creating a C# program that will open the excel file, change the sheet name to be equal to the sheet name which is stored in TC Files. This was I could then successfully use TestComplete's excel compare tool. The TC excel compare tool requires the sheet name in the actual excel file to be the same as the sheet name in the expected (stored) file. For some reason the developers I work with put the date as part of the tab name (so the tab name always changes). The C# program was short and works.&nbsp;&nbsp;&lt;SPAN class="token keyword"&gt;using&lt;/SPAN&gt; &lt;SPAN class="token namespace"&gt;Microsoft&lt;SPAN class="token punctuation"&gt;.&lt;/SPAN&gt;Office&lt;SPAN class="token punctuation"&gt;.&lt;/SPAN&gt;Interop&lt;SPAN class="token punctuation"&gt;.&lt;/SPAN&gt;Excel&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;;&lt;/SPAN&gt;

&lt;SPAN class="token comment"&gt;// command line execution example:&lt;/SPAN&gt;
&lt;SPAN class="token comment"&gt;// C:\ExcelChangeSheetName\bin\Debug
et6.0&gt;ExcelChangeSheetName.exe "C:\Temp\Task_2024_3_11 16_59_34.xlsx"&lt;/SPAN&gt;
&lt;SPAN class="token keyword"&gt;class&lt;/SPAN&gt; &lt;SPAN class="token class-name"&gt;Program&lt;/SPAN&gt;
&lt;SPAN class="token punctuation"&gt;{&lt;/SPAN&gt;
  &lt;SPAN class="token keyword"&gt;static&lt;/SPAN&gt; &lt;SPAN class="token return-type class-name"&gt;&lt;SPAN class="token keyword"&gt;int&lt;/SPAN&gt;&lt;/SPAN&gt; &lt;SPAN class="token function"&gt;Main&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;(&lt;/SPAN&gt;&lt;SPAN class="token class-name"&gt;&lt;SPAN class="token keyword"&gt;string&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;[&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;]&lt;/SPAN&gt;&lt;/SPAN&gt; args&lt;SPAN class="token punctuation"&gt;)&lt;/SPAN&gt;
  &lt;SPAN class="token punctuation"&gt;{&lt;/SPAN&gt;
    &lt;SPAN class="token class-name"&gt;&lt;SPAN class="token keyword"&gt;var&lt;/SPAN&gt;&lt;/SPAN&gt; argCount &lt;SPAN class="token operator"&gt;=&lt;/SPAN&gt; args&lt;SPAN class="token punctuation"&gt;.&lt;/SPAN&gt;Length&lt;SPAN class="token punctuation"&gt;;&lt;/SPAN&gt;

&lt;SPAN class="token keyword"&gt;if&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;(&lt;/SPAN&gt;argCount &lt;SPAN class="token operator"&gt;&gt;&lt;/SPAN&gt; &lt;SPAN class="token number"&gt;0&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;)&lt;/SPAN&gt;
    &lt;SPAN class="token punctuation"&gt;{&lt;/SPAN&gt;
       
      Console&lt;SPAN class="token punctuation"&gt;.&lt;/SPAN&gt;&lt;SPAN class="token function"&gt;WriteLine&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;(&lt;/SPAN&gt;&lt;SPAN class="token string"&gt;"ARG 0 is: "&lt;/SPAN&gt; &lt;SPAN class="token operator"&gt;+&lt;/SPAN&gt; args&lt;SPAN class="token punctuation"&gt;[&lt;/SPAN&gt;&lt;SPAN class="token number"&gt;0&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;]&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;)&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;;&lt;/SPAN&gt;

&lt;SPAN class="token comment"&gt;// Create microsoft application object&lt;/SPAN&gt;
      &lt;SPAN class="token class-name"&gt;&lt;SPAN class="token keyword"&gt;var&lt;/SPAN&gt;&lt;/SPAN&gt; xlApp &lt;SPAN class="token operator"&gt;=&lt;/SPAN&gt; &lt;SPAN class="token keyword"&gt;new&lt;/SPAN&gt; &lt;SPAN class="token constructor-invocation class-name"&gt;Application&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;(&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;)&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;;&lt;/SPAN&gt;

&lt;SPAN class="token comment"&gt;// Create Excel workbook object&lt;/SPAN&gt;
      &lt;SPAN class="token class-name"&gt;&lt;SPAN class="token keyword"&gt;var&lt;/SPAN&gt;&lt;/SPAN&gt; xlWorkbook &lt;SPAN class="token operator"&gt;=&lt;/SPAN&gt; xlApp&lt;SPAN class="token punctuation"&gt;.&lt;/SPAN&gt;Workbooks&lt;SPAN class="token punctuation"&gt;.&lt;/SPAN&gt;&lt;SPAN class="token function"&gt;Open&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;(&lt;/SPAN&gt;args&lt;SPAN class="token punctuation"&gt;[&lt;/SPAN&gt;&lt;SPAN class="token number"&gt;0&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;]&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;,&lt;/SPAN&gt; &lt;SPAN class="token number"&gt;0&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;,&lt;/SPAN&gt; &lt;SPAN class="token boolean"&gt;false&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;,&lt;/SPAN&gt; Type&lt;SPAN class="token punctuation"&gt;.&lt;/SPAN&gt;Missing&lt;SPAN class="token punctuation"&gt;,&lt;/SPAN&gt; &lt;SPAN class="token string"&gt;""&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;,&lt;/SPAN&gt; &lt;SPAN class="token string"&gt;""&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;,&lt;/SPAN&gt; &lt;SPAN class="token boolean"&gt;true&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;,&lt;/SPAN&gt; XlPlatform&lt;SPAN class="token punctuation"&gt;.&lt;/SPAN&gt;xlWindows&lt;SPAN class="token punctuation"&gt;,&lt;/SPAN&gt; 
        Type&lt;SPAN class="token punctuation"&gt;.&lt;/SPAN&gt;Missing&lt;SPAN class="token punctuation"&gt;,&lt;/SPAN&gt; Type&lt;SPAN class="token punctuation"&gt;.&lt;/SPAN&gt;Missing&lt;SPAN class="token punctuation"&gt;,&lt;/SPAN&gt; Type&lt;SPAN class="token punctuation"&gt;.&lt;/SPAN&gt;Missing&lt;SPAN class="token punctuation"&gt;,&lt;/SPAN&gt;
        Type&lt;SPAN class="token punctuation"&gt;.&lt;/SPAN&gt;Missing&lt;SPAN class="token punctuation"&gt;,&lt;/SPAN&gt; &lt;SPAN class="token boolean"&gt;true&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;,&lt;/SPAN&gt; Type&lt;SPAN class="token punctuation"&gt;.&lt;/SPAN&gt;Missing&lt;SPAN class="token punctuation"&gt;,&lt;/SPAN&gt; 
        Type&lt;SPAN class="token punctuation"&gt;.&lt;/SPAN&gt;Missing&lt;SPAN class="token punctuation"&gt;)&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;;&lt;/SPAN&gt;

&lt;SPAN class="token comment"&gt;// Grab the worksheet and get it's name&lt;/SPAN&gt;
      &lt;SPAN class="token class-name"&gt;&lt;SPAN class="token keyword"&gt;var&lt;/SPAN&gt;&lt;/SPAN&gt; xlWorksheet &lt;SPAN class="token operator"&gt;=&lt;/SPAN&gt; &lt;SPAN class="token punctuation"&gt;(&lt;/SPAN&gt;Worksheet&lt;SPAN class="token punctuation"&gt;)&lt;/SPAN&gt;xlWorkbook&lt;SPAN class="token punctuation"&gt;.&lt;/SPAN&gt;Sheets&lt;SPAN class="token punctuation"&gt;[&lt;/SPAN&gt;&lt;SPAN class="token number"&gt;1&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;]&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;;&lt;/SPAN&gt; 
      &lt;SPAN class="token class-name"&gt;&lt;SPAN class="token keyword"&gt;var&lt;/SPAN&gt;&lt;/SPAN&gt; sheetName &lt;SPAN class="token operator"&gt;=&lt;/SPAN&gt; xlWorksheet&lt;SPAN class="token punctuation"&gt;.&lt;/SPAN&gt;Name&lt;SPAN class="token punctuation"&gt;;&lt;/SPAN&gt;

&lt;SPAN class="token comment"&gt;// Find the start of the date&lt;/SPAN&gt;
      &lt;SPAN class="token class-name"&gt;&lt;SPAN class="token keyword"&gt;var&lt;/SPAN&gt;&lt;/SPAN&gt; indexOfDate &lt;SPAN class="token operator"&gt;=&lt;/SPAN&gt; sheetName&lt;SPAN class="token punctuation"&gt;.&lt;/SPAN&gt;&lt;SPAN class="token function"&gt;IndexOf&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;(&lt;/SPAN&gt;&lt;SPAN class="token string"&gt;"_20"&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;)&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;;&lt;/SPAN&gt;

&lt;SPAN class="token comment"&gt;// If the date exists, remove it from the name of the sheet and save the workbook&lt;/SPAN&gt;
      &lt;SPAN class="token keyword"&gt;if&lt;/SPAN&gt; &lt;SPAN class="token punctuation"&gt;(&lt;/SPAN&gt;indexOfDate &lt;SPAN class="token operator"&gt;!=&lt;/SPAN&gt; &lt;SPAN class="token operator"&gt;-&lt;/SPAN&gt;&lt;SPAN class="token number"&gt;1&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;)&lt;/SPAN&gt;
      &lt;SPAN class="token punctuation"&gt;{&lt;/SPAN&gt;
        &lt;SPAN class="token class-name"&gt;&lt;SPAN class="token keyword"&gt;var&lt;/SPAN&gt;&lt;/SPAN&gt; baseName &lt;SPAN class="token operator"&gt;=&lt;/SPAN&gt; sheetName&lt;SPAN class="token punctuation"&gt;.&lt;/SPAN&gt;&lt;SPAN class="token function"&gt;Substring&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;(&lt;/SPAN&gt;&lt;SPAN class="token number"&gt;0&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;,&lt;/SPAN&gt; indexOfDate&lt;SPAN class="token punctuation"&gt;)&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;;&lt;/SPAN&gt;
        xlWorksheet&lt;SPAN class="token punctuation"&gt;.&lt;/SPAN&gt;Name &lt;SPAN class="token operator"&gt;=&lt;/SPAN&gt; baseName&lt;SPAN class="token punctuation"&gt;;&lt;/SPAN&gt;
        xlWorkbook&lt;SPAN class="token punctuation"&gt;.&lt;/SPAN&gt;&lt;SPAN class="token function"&gt;Save&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;(&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;)&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;;&lt;/SPAN&gt;
      &lt;SPAN class="token punctuation"&gt;}&lt;/SPAN&gt;

&lt;SPAN class="token comment"&gt;// Close the workbook and the application&lt;/SPAN&gt;
      xlWorkbook&lt;SPAN class="token punctuation"&gt;.&lt;/SPAN&gt;&lt;SPAN class="token function"&gt;Close&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;(&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;)&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;;&lt;/SPAN&gt;
      xlApp&lt;SPAN class="token punctuation"&gt;.&lt;/SPAN&gt;&lt;SPAN class="token function"&gt;Quit&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;(&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;)&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;;&lt;/SPAN&gt;
    &lt;SPAN class="token punctuation"&gt;}&lt;/SPAN&gt;
    &lt;SPAN class="token keyword"&gt;else&lt;/SPAN&gt;
    &lt;SPAN class="token punctuation"&gt;{&lt;/SPAN&gt;
      Console&lt;SPAN class="token punctuation"&gt;.&lt;/SPAN&gt;&lt;SPAN class="token function"&gt;WriteLine&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;(&lt;/SPAN&gt;&lt;SPAN class="token string"&gt;"EXPECTED 1 argument. The argument should be the path to the excel workbook which needs it's sheet name modified."&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;)&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;;&lt;/SPAN&gt;
    &lt;SPAN class="token punctuation"&gt;}&lt;/SPAN&gt;
    &lt;SPAN class="token keyword"&gt;return&lt;/SPAN&gt; &lt;SPAN class="token number"&gt;0&lt;/SPAN&gt;&lt;SPAN class="token punctuation"&gt;;&lt;/SPAN&gt;
  &lt;SPAN class="token punctuation"&gt;}&lt;/SPAN&gt;
&lt;SPAN class="token punctuation"&gt;}&lt;/SPAN&gt;

marsha_r · Answer

Are you testing Excel? Why is this needed in your test?&nbsp;

rraghvani · Answer

Sorry for the late reply, but if you use Excel COM, you can rename the worksheet in JavaScript like so,
function main()
{
    var myExcel = getActiveXObject("Excel.Application");
    var workbook = myExcel.Workbooks.Open("C:\Temp\Book2.xlsx");
    myExcel.Visible = true;
    
    //var worksheet = workbook.Sheets.Item("Sheet3");
    var worksheet = workbook.Sheets.Item(3);
    worksheet.Name = "TestIt"; // Rename worksheet from 'Sheet3' to 'TestIt'

workbook.Save();
    workbook.Close();
    myExcel.Quit();
}
refer to the worksheet either by it's name or index value

torus · Answer

Just pasting a picture since the 'insert code' functionality doesn't seem to be working as expected.

torus · Answer

Note: Although I specified that the code was C#, for some reason the code snippets are always posted as HTML for some reason.&nbsp;

Forum Discussion

Excel: how do you change sheet name in Excel file

5 Replies

Related Content

Using Excel Sheet for Data Driven Loop with Empty Cells

How to select Excel sheet in python?

Insert PDF file into excel sheet

Data Sink always updates the 1st row in excel sheet

JavaScript API for Excel sheet

Recent Discussions

Dynamic Object

Pulling in Dynamic Links from a web page

Multiple execution of a test plan with different values from an Excel table

Your thoughts about a REST API in TestComplete

Keyword Test - Click - use -1, -1 by default?