Forum Discussion

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 keywo...

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>

torus

Contributor

<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.

Recent Discussions

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