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

[obaid_shirwani]

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]

---

@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]

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]

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.

[obaid_shirwani]

Hy Shankar,

I am unable to access 'book.Sheets' alone... it crashes here. How am i supposed to get past it?

Please view the attached images in previous posts and read the text again under "//Issue 02 (Intellisense popup)".

Thanks & regards, 

Shirwani

[shankar_r]

This error is not related TestComplete use case, It is part of Excel settings.

I tried the same code with my machine it is working fine. [TC 12.42].

Does your excel sheet have any Macros?

When you open your excel workbook, does is shows Enable Editing?

Are there any recent Excel upgrade you did?

[obaid_shirwani]

Shankar, I am not using any macros. The problem is beyond file enabled or disabled for editing.

However as pointed out in message three in this thread above, i too doubt that this seems to be related to some version conflicts. I have provided details for all related software I am using.

The issue at hand still stands that why is TestComplete trying to open the excel file during Code Development and why is it throwing the wrong message at code execution? The workbook is getting opened by code and object get returned successfully and file gets locked for manual manipulation, The error message 'book.Sheets is not a function' is wrong as I can see this property in the workbook object. I have the shot attached in above posts.

[shankar_r]

You would probably need to create a support ticket.

https://support.smartbear.com/message/?prod=TestComplete