cancel
Showing results for 
Search instead for 
Did you mean: 

how to check if sheet in excel driver exist or not

SOLVED
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 Technical Writer
________________________
Vote up helpful replies.
Accept this reply if you think it's the best solution to your question.
3 REPLIES 3
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 Expert Group]
____
[Community Expert Group] members 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. Postings made by [Community Expert Group] members
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.
[Community Expert Group] signature is used with permission by SmartBear Software.
http://smartbear.com/forums/f83/t86934/community-experts/
================================
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 Technical Writer
________________________
Vote up helpful replies.
Accept this reply if you think it's the best solution to your question.
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:
Top Kudoed Authors