Forum Discussion

Contributor

2 months ago

Solved

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

2 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

Champion Level 3

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

torus

Contributor

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

Unbinding a project suite from source control
2 hours agodhundley
how to use Random Data every time you run a TC
5 hours agoGill
Chrome/Edge Extension not always working... why?
5 hours agoSlickRick
Issues with dropdowns and ClickItem() not working if the dropdown opens in the up direction
Solved
22 hours agojoe_brooks
Project error stopping execution
2 days agoJustinL22

Forum Discussion

Excel: how do you change sheet name in Excel file

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

Unbinding a project suite from source control

how to use Random Data every time you run a TC

Chrome/Edge Extension not always working... why?

Issues with dropdowns and ClickItem() not working if the dropdown opens in the up direction

Project error stopping execution