Excel UDF - "The requested action with this object has failed.:"

[Ropetin Again]

I'm trying to do a fairly simple task; extract a few fields from some fairly large Excel spreadsheets. I've done it before, but for some reason this time it's failing on me at the first hurdle. I attempt to run the very complex(?) code below;

#include <Excel.au3>

$file = "c:\test\test02.xls"
$oExcel = _ExcelBookOpen($file)

If @error = 1 Then
    MsgBox(0, "Error!", "Unable to Create the Excel Object")
    Exit
ElseIf @error = 2 Then
    MsgBox(0, "Error!", "File does not exist - Shame on you!")
    Exit
EndIf

_ExcelBookClose($oExcel)

MsgBox (0, "Never See This", "This will never be seen...")

It fails with;

>Running:(3.2.12.1):C:\Program Files\AutoIt3\autoit3.exe "C:\pdp\pdp.au3"   
C:\Program Files\AutoIt3\Include\Excel.au3 (188) : ==> The requested action with this object has failed.:
.ActiveWorkbook.Sheets(1).Select ()
.ActiveWorkbook.Sheets(1).Select ()^ ERROR
->15:23:59 AutoIT3.exe ended.rc:1
>Exit code: 1   Time: 3.211

If I create a new Excel sheet, and put some random data in it, it will work fine. However, any time I try and open an existing sheet, it fails as above. The sheets are large, ~2MB with 30 tabs, but Excel can handle them fine. I've tried the following things, but all end up with the same error;

• Supply the password in the 'open' command
• Remove any passwords all together
• Remove all tabs apart from one
• Use Excel 2003
• Use Excel 2007
• Use AutoIt 3.2.13.6
• Use AutoIt 3.2.12.1
• Use version 1.5 of Excel.udf
• Use version 1.4 of Excel.udf

I'm totally lost at this point, does anyone have any suggestions?

[Jos]

Have you tried adding an Comm Error Handler and display the details about the error to see whats happening?

Edited August 4, 2008 by Jos

[Ropetin Again]

Have you tried adding an Comm Error Handler and display the details about the error to see whats happening?

I have not, and it sounds an excellent idea. Unfortunately, my quick search through the Help file and the Forum didn't give me any hint at how to do it. Would you happen to have an example on how to do this?

Thanks!

[Jos]

Just open the main page for COM reference: mk:@MSITStore:C:\Program%20Files\AutoIt3\beta\AutoIt3.chm::/html/intro/ComRef.htm

Scroll down and you will find a section about Com Error Handler.

[Ropetin Again]

Just open the main page for COM reference: mk:@MSITStore:C:\Program%20Files\AutoIt3\beta\AutoIt3.chm::/html/intro/ComRef.htm

Scroll down and you will find a section about Com Error Handler.

Thank you for that information. Based on that, I get error '80020009' with no Windescription. Searching the forum I found this page;

http://www.autoitscript.com/forum/lofivers...?t34302-50.html

Which seems to suggest it is related to an 'overly formatted' cell. The following link for a totally different product, but which also uses the Excel COM interface seems to concur;

http://support.softartisans.com/kbview_192.aspx

Does this sound like I'm on the right track, and if so do you have any suggestion on how to get around it?

[Jos]

Does the $oMyError.description contain any useful information?

[Ropetin Again]

Does the $oMyError.description contain any useful information?

I'm learning bunches of new stuff today, thank you! The description shows;

Unable to get the Select property of the Workseet class

[PsaltyDS]

I'm trying to do a fairly simple task; extract a few fields from some fairly large Excel spreadsheets. I've done it before, but for some reason this time it's failing on me at the first hurdle. I attempt to run the very complex(?) code below;

#include <Excel.au3>

$file = "c:\test\test02.xls"
$oExcel = _ExcelBookOpen($file)

If @error = 1 Then
    MsgBox(0, "Error!", "Unable to Create the Excel Object")
    Exit
ElseIf @error = 2 Then
    MsgBox(0, "Error!", "File does not exist - Shame on you!")
    Exit
EndIf

_ExcelBookClose($oExcel)

MsgBox (0, "Never See This", "This will never be seen...")

It fails with;

>Running:(3.2.12.1):C:\Program Files\AutoIt3\autoit3.exe "C:\pdp\pdp.au3"   
C:\Program Files\AutoIt3\Include\Excel.au3 (188) : ==> The requested action with this object has failed.:
.ActiveWorkbook.Sheets(1).Select ()
.ActiveWorkbook.Sheets(1).Select ()^ ERROR
->15:23:59 AutoIT3.exe ended.rc:1
>Exit code: 1   Time: 3.211

If I create a new Excel sheet, and put some random data in it, it will work fine. However, any time I try and open an existing sheet, it fails as above. The sheets are large, ~2MB with 30 tabs, but Excel can handle them fine. I've tried the following things, but all end up with the same error;

• Supply the password in the 'open' command
• Remove any passwords all together
• Remove all tabs apart from one
• Use Excel 2003
• Use Excel 2007
• Use AutoIt 3.2.13.6
• Use AutoIt 3.2.12.1
• Use version 1.5 of Excel.udf
• Use version 1.4 of Excel.udf

I'm totally lost at this point, does anyone have any suggestions?

I note you seem to be using Excel.au3, which randallc has not updated since October 2006 (version 1_3_7), and not Locodarwin's ExcelCOM_UDF.au3. May I ask why? Since you reference version 1.4 and 1.5, perhaps you are confused about which one you meant to use?

Edited August 4, 2008 by PsaltyDS

[Ropetin Again]

I note you seem to be using Excel.au3, which randallc has not updated since October 2006 (version 1_3_7), and not Locodarwin's ExcelCOM_UDF.au3. May I ask why? Since you reference version 1.4 and 1.5, perhaps you are confused about which one you meant to use?

Oh yes, my bad, I just renamed the file for no apparent reason. The one I'm using starts with;

; #INDEX# ====================================================================================================

===================
; Title .........: Microsoft Excel COM UDF library for AutoIt v3
; AutoIt Version: 3.2.3++, Excel.au3 v 1.5 (07/18/2008 @ 8:25am PST)
; Language:    English
; Description:  A collection of functions for creating, attaching to, reading from and manipulating Microsoft Excel
;                Author(s) include: SEO aka Locodarwin, DaLiMan, Stanley Lim, MikeOsdx, MRDev, big_daddy, PsaltyDS, litlmike
; ====================================================================================================

===========================

I notice you are named as one of the authors, thanks for this wonderful UDF!

[PsaltyDS]

I notice you are named as one of the authors, thanks for this wonderful UDF!

Minor contributor would be more like it, but you are welcome all the same.

No help for your problem though, as I can't duplicate your symptoms. Can you pare it down to the smallest sheet that contains the troublesome cells causing the problem? You might be able to chase it down to a particular part of your workbooks.

[Ropetin Again]

Minor contributor would be more like it, but you are welcome all the same.

No help for your problem though, as I can't duplicate your symptoms. Can you pare it down to the smallest sheet that contains the troublesome cells causing the problem? You might be able to chase it down to a particular part of your workbooks.

I tried to do that, but taking one of the problematic files, removing all sheets apart from one. The one sheet left had no text on it at all, but it still failed. With the COM Error Handling recommended above, it allows my script to still keep working, despite the error. Is this a valid 'hack' to get around the problem, or will I cause something else bad to happen down the road?

[PsaltyDS]

I tried to do that, but taking one of the problematic files, removing all sheets apart from one. The one sheet left had no text on it at all, but it still failed. With the COM Error Handling recommended above, it allows my script to still keep working, despite the error. Is this a valid 'hack' to get around the problem, or will I cause something else bad to happen down the road?

Well, I don't know, but it's a good thing that you can still reproduce you symptoms with a reduced .xls file. Keep going that direction...what if you delete the second half of all the rows? Ideally, I'm hoping you'll get it down to something like a 10x10 cell single sheet that still causes the problem, but doesn't have any private (un-postable) information in it. Then you could post that .xls here so we can check it out.