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: function changeExcelTabName() { var excelFile = Excel.Open("C:\\Users\\chk\\Downloads\\Task groups with no tasks_2024_3_11 16_59_34.xlsx"); excelFile.SheetByIndex(0).Title <the code to change sheet title would go here> excelFile.Save(); }

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. using Microsoft.Office.Interop.Excel; // command line execution example: // C:\ExcelChangeSheetName\bin\Debug\net6.0>ExcelChangeSheetName.exe "C:\\Temp\\Task_2024_3_11 16_59_34.xlsx" class Program { static int Main(string[] args) { var argCount = args.Length; if(argCount > 0) { Console.WriteLine("ARG 0 is: " + args[0]); // Create microsoft application object var xlApp = new Application(); // Create Excel workbook object var xlWorkbook = xlApp.Workbooks.Open(args[0], 0, false, Type.Missing, "", "", true, XlPlatform.xlWindows, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing); // Grab the worksheet and get it's name var xlWorksheet = (Worksheet)xlWorkbook.Sheets[1]; var sheetName = xlWorksheet.Name; // Find the start of the date var indexOfDate = sheetName.IndexOf("_20"); // If the date exists, remove it from the name of the sheet and save the workbook if (indexOfDate != -1) { var baseName = sheetName.Substring(0, indexOfDate); xlWorksheet.Name = baseName; xlWorkbook.Save(); } // Close the workbook and the application xlWorkbook.Close(); xlApp.Quit(); } else { Console.WriteLine("EXPECTED 1 argument. The argument should be the path to the excel workbook which needs it's sheet name modified."); } return 0; } }

Forum Discussion

torus

Contributor

9 months ago

Excel: how do you change sheet name in Excel file

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:

<SPAN class="token keyword">function</SPAN> <SPAN class="token function">changeExcelTabName</SPAN><SPAN class="token punctuation">(</SPAN><SPAN class="token punctuation">)</SPAN>
<SPAN class="token punctuation">{</SPAN>
  <SPAN class="token keyword">var</SPAN> excelFile <SPAN class="token operator">=</SPAN> Excel<SPAN class="token punctuation">.</SPAN><SPAN class="token function">Open</SPAN><SPAN class="token punctuation">(</SPAN><SPAN class="token string">"C:\\Users\\chk\\Downloads\\Task groups with no tasks_2024_3_11 16_59_34.xlsx"</SPAN><SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">;</SPAN>
  excelFile<SPAN class="token punctuation">.</SPAN><SPAN class="token function">SheetByIndex</SPAN><SPAN class="token punctuation">(</SPAN><SPAN class="token number">0</SPAN><SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">.</SPAN>Title
  <SPAN class="token operator"><</SPAN>the code to change sheet title would go here<SPAN class="token operator">></SPAN>
  excelFile<SPAN class="token punctuation">.</SPAN><SPAN class="token function">Save</SPAN><SPAN class="token punctuation">(</SPAN><SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">;</SPAN>
<SPAN class="token punctuation">}</SPAN>

Script Tests

torus

9 months ago

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.

<SPAN class="token keyword">using</SPAN> <SPAN class="token namespace">Microsoft<SPAN class="token punctuation">.</SPAN>Office<SPAN class="token punctuation">.</SPAN>Interop<SPAN class="token punctuation">.</SPAN>Excel</SPAN><SPAN class="token punctuation">;</SPAN>

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

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

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

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

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

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

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

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

Marsha_R

Moderator

9 months ago

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

torus

Contributor

9 months ago

<SPAN class="token keyword">using</SPAN> <SPAN class="token namespace">Microsoft<SPAN class="token punctuation">.</SPAN>Office<SPAN class="token punctuation">.</SPAN>Interop<SPAN class="token punctuation">.</SPAN>Excel</SPAN><SPAN class="token punctuation">;</SPAN>

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

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

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

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

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

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

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

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

torus
Contributor
9 months ago
Note: Although I specified that the code was C#, for some reason the code snippets are always posted as HTML for some reason.

rraghvani

Champion Level 3

9 months ago

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
Contributor
9 months ago
Just pasting a picture since the 'insert code' functionality doesn't seem to be working as expected.

Recent Discussions

How is test complete using our data while using the inbuilt AI feature ?
5 hours ago
FFCB
Problems by validating a tooltip of a wpf application
5 hours ago
sborno
Login for Zephyr is through Microsoft 365 and TestComplete can't complete login
7 hours ago
lslechta
Testing on a local iPad
8 hours ago
Wyatt7989
Is it possible to add Device Info from a remote browser to the Log Folder?
9 hours ago
Wyatt7989

Forum Discussion

Excel: how do you change sheet name in Excel file

Related Content

How to select Excel sheet in python?

Insert PDF file into excel sheet

JavaScript API for Excel sheet

Opening and updateing an excel sheet using jxl

Using Excel Sheet for Data Driven Loop with Empty Cells

Recent Discussions

How is test complete using our data while using the inbuilt AI feature ?

Problems by validating a tooltip of a wpf application

Login for Zephyr is through Microsoft 365 and TestComplete can't complete login

Testing on a local iPad

Is it possible to add Device Info from a remote browser to the Log Folder?