cancel
Showing results for 
Search instead for 
Did you mean: 

how to check if sheet in excel driver exist or not

SOLVED
Highlighted
Contributor

how to check if sheet in excel driver exist or not

Hi, 

 

I want to check if Sheet1 exists or not. Sometimes 'Sheet1' is named as Error in our application, and hence is an actual error in export. Even though file is exported correctly. 

 

Attempt:

var excelDDT= DDT.ExcelDriver(ProjectSuite.Variables.PlanExportDirectory, "Sheet1", true);

      if(excelDDT.){
          Log.Message("excel DDT exists");
      }
      else{
          Log.Error("Exported file contains error");
      }

     while(!DDT.CurrentDriver.EOF()){
       //Test
       Log.message(DDT.CurrentDriver.Value(0)); 
      }

      // Close driver
      DDT.CloseDriver(DDT.CurrentDriver.Name); 

 

It throws Jscript error on first line because Sheet1 doesn't exist. 

 

Thanks in advance,

Shiva

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Moderator

Re: how to check if sheet in excel driver exist or not

As Alex said, you can use Excel's COM object, Excel.Application for that. Something along the lines of:

 

var oExcel = Sys.OleObject("Excel.Application");
var oWb = oExcel.Workbooks.Open("C:\\MyFile.xls");
var oSheet = null; try { oSheet = oWb.Sheets("Sheet1"); } catch (e) {} if (oSheet != null) { Log.Message("The specified sheet exists."); } else { Log.Error("The specified sheet does not exists."); } oExcel.Quit();

Helen Kosova
SmartBear Documentation Team Lead
________________________
Learn SmartBear products in a fun and easy way and prove your knowledge!
>>Participate in the TechCorner Challenge today

View solution in original post

3 REPLIES 3
Highlighted
Community Hero

Re: how to check if sheet in excel driver exist or not

Hi Shiva,

 

I am not sure if this can be done using the DDT object, but you can get the list of the sheets that are within the given Excel file by using either ADO or Excel COM model. E.g.:

http://answers.microsoft.com/en-us/office/forum/office_2007-excel/create-list-of-tabs-in-excel/08db2...

http://stackoverflow.com/questions/13618224/how-to-get-sheets-name-from-excel-2007-xlsx-using-vb-net...

http://www.mathworks.com/matlabcentral/answers/102131-how-can-i-rename-a-sheet-in-excel-using-the-co...

Regards,
  /Alex [Community Hero]
____
[Community Heroes] are not employed by SmartBear Software but
are just volunteers who have some experience with the tools by SmartBear Software
and a desire to help others. Posts made by [Community Heroes]
may differ from the official policies of SmartBear Software and should be treated
as the own private opinion of their authors and under no circumstances as an
official answer from SmartBear Software.
The [Community Hero] signature is used with permission by SmartBear Software.
https://community.smartbear.com/t5/custom/page/page-id/hall-of-fame
================================
Highlighted
Moderator

Re: how to check if sheet in excel driver exist or not

As Alex said, you can use Excel's COM object, Excel.Application for that. Something along the lines of:

 

var oExcel = Sys.OleObject("Excel.Application");
var oWb = oExcel.Workbooks.Open("C:\\MyFile.xls");
var oSheet = null; try { oSheet = oWb.Sheets("Sheet1"); } catch (e) {} if (oSheet != null) { Log.Message("The specified sheet exists."); } else { Log.Error("The specified sheet does not exists."); } oExcel.Quit();

Helen Kosova
SmartBear Documentation Team Lead
________________________
Learn SmartBear products in a fun and easy way and prove your knowledge!
>>Participate in the TechCorner Challenge today

View solution in original post

Super Contributor

Re: how to check if sheet in excel driver exist or not

Hey,

 

Sometime "

Sys.OleObject( "Excel.Application" )

" may give you error, so instead to "Sys.OleObject ()" use "CreateObject( "Excel.Application" )"

 

 

In order to be able to get an OLE object for an already running application, this application must be registered in the Running Object Table (ROT). However, Microsoft Office applications that are launched from the shell (for example, from the Start menu or the TestedApps project item) do not register their running objects at startup, but rather once the application loses focus. So, if you obtain an OLE object for an already running Office application, you may get errors when accessing its properties and methods (for more information on this problem, see http://support.microsoft.com/kb/238610/).

New Here?
Join us and watch the welcome video:
Announcements
Join TechCorner Challenge!
Want a fun and easy way to learn TestComplete? Try solving weekly TechCorner challenges and get into the Leaderboard!

Challenge Status

Changing options from outside of TestComplete

Participate!

How to execute remote test and obtain results via Test Runner REST API

Participate!

Comparing content of HTML table with Excel file data

Participate!

Compare HTML table with Excel file and correct data in the file

Participate!
Top Kudoed Authors