Create copy of a worksheet and rename it in the same workbook

Question

Hello,&nbsp;Is there a way to create a copy of the worksheet in the same workbook? I'm using a worksheet to read data&nbsp; from and write tests results to. I need to create a copy of it with timestamp and save it in the same workbook. I'm using Oleobject to interact with Excel.&nbsp;any help would be appreciated!&nbsp;Thank youAbhi

tristaanogre · Answer

As&nbsp;Marsha_R, there are any number of methods, properties, etc, in the Excel COM objects.&nbsp; There probably is a way to create a copy of a worksheet however, I'm not expert at using Excel COM (there may be others who are) so for me to come up with the JavaScript for you would mean I'd spend my work hours or personal time doing so.
&nbsp;
So... what have you tried, if anything?&nbsp; The Excel COM objects are documented on Microsoft support forums so you may want to try googling your need and see if there is anything out there for you... what you are asking for is not a uniquely TestComplete thing since it's interacting with Excel... the same thing you could probably do in JavaScript via a web page or something.
&nbsp;
Perhaps someone will post here what they've tried but it might be a good experience for you to see what's out there in forums more specifically dedicated to working with Excel via COM.

tristaanogre · Answer

To prove my point, I took 5 minutes and found the following link by googling "copy sheet in workbook via COM".&nbsp; The code is in VB, but the concepts are the same and should be easily translatable to JavaScript.https://msdn.microsoft.com/en-us/vba/excel-vba/articles/worksheet-copy-method-excel

tristaanogre · Answer

That site (MSDN), if you look down the left side of the screen, you'll see all the objects, methods, and properties available to you.&nbsp; That should be your primary reference point.
&nbsp;
As for your code, the problem is that you're calling the Copy method without assigning an "After" parameter... without those parameters, it creates a new workbook.
&nbsp;
So, I THINK... it should be something like
&nbsp;
var Excel = Sys.OleObject("Excel.Application");
var fileName = "C:\Users\Me\Desktop\123.xlsx";

var WB = Excel.Workbooks.Open(fileName);
    var WS = WB.Sheets.Item("Sheet1"); 
    Excel.DisplayAlerts = false;
    
    WS.Copy("Sheet2");
    WB.Save();
    Excel.Quit();
&nbsp;

marsha_r · Answer

Looks like there's a Copy feature available
&nbsp;
https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.worksheet_members.aspx

adagio · Answer

Thanks Marsha! I'm looking for ways to do it using JavaScript in TestComplete.&nbsp;Thank youAbhi

Forum Discussion

Create copy of a worksheet and rename it in the same workbook

9 Replies

Related Content

Unable to retrieve the worksheet list from the specified workbook. - ERROR load Excel

Excel worksheet can not be edited

Script to verify DB Table workbook

Add New Worksheet at the End

Compare worksheets within a Workbook

Recent Discussions

Access a child in a tree Structure through Keyword Strokes

Turn off all Edge messageboxes

How to verify data from another application, the data which keeps changing

Audit and Reporting purposes

Textbox values disappear after adding Year value from Date Picker