Forum Discussion

divernut's avatar
divernut
Occasional Contributor
14 years ago

Using the COM for opening Excel, can't select a particular sheet.

I am using the COM to work with Excel 2007. When I use the following code, it opens to the first sheet by default.

 Excel = Sys.OleObject("Excel.Application");

  Delay (3000); // Wait until Excel starts

  Excel.Visible = true;

  Excel.Workbooks.Open("G:\\Documentation\\CalCit Excel Files\\2004 Test Data v3 FINAL_new.xlsx");



I need to select different sheets. I tryed using the following code from the DDTdriver code.

 Excel = Sys.OleObject("Excel.Application");

  Delay (3000); // Wait until Excel starts

  Excel.Visible = true;

  Excel.Workbooks.Open("G:\\Documentation\\CalCit Excel Files\\2004 Test Data v3 FINAL_new.xlsx", "sheet2", true);

But this does not work. I have tryed many other configs and still get nothing.



Bob

3 Replies

  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    14 years ago
    Do you need to have Excel open and visible or are you just trying to read/write from it?



    I think you can call a "sheets" property off the "book" object that is returned from the Open command.  Then you indicate "book.Sheets(SheetName)" to return the specific sheet you want.



    Don't know if this helps.
  • Jackson_L's avatar
    Jackson_L
    Contributor
    14 years ago

    Hello,

    Excel COM objects and DDT Driver objects are completly different and you cannot use the methods of one in the other. So you cannot create a Excel COM object and use DDT driver methods on it.


    Using Excel COM, to activate another worksheet, try using the following:


    Excel = Sys.OleObject("Excel.Application");

    Delay(3000);  

    Excel.Visible = true; 

    Excel.Workbooks.Open("G:\\Documentation\\CalCit Excel Files\\2004 Test Data v3 FINAL_new.xlsx").Worksheets("sheet2").Activate;


    If you're only using the excel sheet for data driven tests then using DDT drivers would probably be better.

    http://smartbear.com/support/viewarticle/11918/


    Cheers,

    Jackson

  • divernut's avatar
    divernut
    Occasional Contributor
    14 years ago
    Once again you guys ROCK!!!!. Thanks for clearing that up.





    Bob