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

Recent Discussions

How to pass in a parameter to DB connection string

RebootAndContinue

TestComplete update pending

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