Sign in to follow this  
Followers 0
Ropetin Again

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

12 posts in this topic

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?

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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

Edited by Jos

Visit the SciTE4AutoIt3 Download page for the latest versions        Beta files                                                          Forum Rules
 
Live for the present,
Dream of the future,
Learn from the past.
  :)

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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.

:P


Visit the SciTE4AutoIt3 Download page for the latest versions        Beta files                                                          Forum Rules
 
Live for the present,
Dream of the future,
Learn from the past.
  :)

Share this post


Link to post
Share on other sites

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.

:P

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

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?

:P

Edited by PsaltyDS

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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?

:P

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!

Share this post


Link to post
Share on other sites

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.

:P


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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.

:P

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?

Share this post


Link to post
Share on other sites

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.

:P


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0