Forum Discussion

Adagio's avatar
Adagio
Frequent Contributor
8 years ago

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

Hello,   Is there a way to create a copy of the worksheet in the same workbook? I'm using a worksheet to read data  from and write tests results to. I need to create a copy of it with timestamp and...
tristaanogre's avatar
tristaanogre
Esteemed Contributor
8 years ago

As Marsha_R, there are any number of methods, properties, etc, in the Excel COM objects.  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.

 

So... what have you tried, if anything?  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.

 

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.

  • Adagio's avatar
    Adagio
    Frequent Contributor
    8 years ago

    Thanks for your time, Robert. Marsha already posted that link above, and I had tried this kind of stuff in the VB which works fine. I usually post on the forum after trying all other options.

    Problem is TC IDE won't suggest what functions are available to push the copied sheet in the End(such as I'm not able to figure out where to use Before and After). Here's what I did without much success. 

     

    I'll post the code here once I can get this working. 

     

     

    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;
    WB.Save();
    Excel.Quit();

    Thanks

    Abhi

    • tristaanogre's avatar
      tristaanogre
      Esteemed Contributor
      8 years ago

      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.  That should be your primary reference point.

       

      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.

       

      So, I THINK... it should be something like

       

      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();