Forum Discussion

obaid_shirwani's avatar
obaid_shirwani
Contributor
7 years ago

Previously working code w.r.t Sys.OleObject("Excel.Application") not working with 12.42

Hi,

 

I had the below code working absolutely fine in TestComplete version 10. I have revisited it only to find it not working in version 12.

 

//No Issue

var book = Excel.Workbooks.Open(inputFileName);

     

//Issue 01:

//The following code is no more working and error is displayed here saying

//TypeError: book.Sheets is not a function

var sheet = book.Sheets(desiredSheetName);

     

//Issue 02 (Intellisense popup)

var sheet2 = book.Sheets.

//(here, intellisense causes the popup to appear highlighted in the attached screenshot)

 

  

      //5. Identify the desired data row as per the testCaseId and caseNumber variables value

      //row = 1 because this is the first row on excel sheet.

      var row = 1;

      var desiredRow = 0;

 

      //Find the last row number that is used on the Excel Sheet. This is the limit

      var maximumRowNumber = sheet.UsedRange.Rows.Count;

……

 

The input excel file is the same. Moreover, intellisense pops up a dialog calling for file usage instead of displaying the available methods and properties. Please find the attached image.

 

 

I am using the following:

1. MS Excel 2016 32 bit.

2. TestComplete 32 bit.

3. Windows 64 bit.

4. Microsoft 12.0 Access Database Engine OLE DB provided 32 bit version of installed.

5. My excel sheet is 'Microsoft Excel 97-2003 Worksheet (.xls)'

 

 

Help is urgently required.

  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    7 years ago
    obaid_shirwani wrote:

    Hi,

     

    I had the below code working absolutely fine in TestComplete version 10. I have revisited it only to find it not working in version 12.

     

    //No Issue

    var book = Excel.Workbooks.Open(inputFileName);

     


    As Sherlock Holmes once said, when you eliminate possibilities, whatever is left, however improbable, must be the case.

     

    The above code you won't necessarily get an error if it fails.  If it fails to open the workbook, it may just return a workbook object that has no Sheets or anything associated with it.  So, it's possible that this is the cause of all your other issues.  So, I'd start your investigation here... drop a breakpoint in to see if you are actually getting a valid "Workbook" object.  If the object being returned is null, than "Sheets" would not be a recognized function.

     

    Please upload the snapshot photo that you promised so we can help further, but I think I'd start first with investigating why the workbook isn't actually opening.

     

     

    • obaid_shirwani's avatar
      obaid_shirwani
      Contributor
      7 years ago

      Hi Martin,

       

      I had obviously checked that before posting. The returned Workbooks object holds the number of Sheets available in the file. So i think that is not the issue. I am attaching the screenshot for the object returned.

       

      The visible class is throwing error. I assume it might be some version conflict or the driver issue.

       

       

      Best regards,

       

      Shirwani

      • shankar_r's avatar
        shankar_r
        Community Hero
        7 years ago

        var sheet = book.Sheets(desiredSheetName)

        Instead of this line, Can you try using below,

        var sheet = book.Sheets.Item(desiredSheetName);

        It works fine for me long time.