Forum Discussion

torus's avatar
torus
Contributor
5 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>
  • torus's avatar
    torus
    5 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>

5 Replies

  • rraghvani's avatar
    rraghvani
    Champion Level 3

    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

  • Marsha_R's avatar
    Marsha_R
    Champion Level 3

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

    • torus's avatar
      torus
      Contributor

      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>
      • torus's avatar
        torus
        Contributor

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

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