Ropetin Again Posted August 4, 2008 Share Posted August 4, 2008 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.211If 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' commandRemove any passwords all togetherRemove all tabs apart from oneUse Excel 2003Use Excel 2007Use AutoIt 3.2.13.6Use AutoIt 3.2.12.1Use version 1.5 of Excel.udfUse version 1.4 of Excel.udfI'm totally lost at this point, does anyone have any suggestions? Link to comment Share on other sites More sharing options...
Developers Jos Posted August 4, 2008 Developers Share Posted August 4, 2008 (edited) 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 SciTE4AutoIt3 Full installer Download page - Beta files Read before posting How to post scriptsource Forum etiquette Forum Rules Live for the present, Dream of the future, Learn from the past. Link to comment Share on other sites More sharing options...
Ropetin Again Posted August 4, 2008 Author Share Posted August 4, 2008 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! Link to comment Share on other sites More sharing options...
Developers Jos Posted August 4, 2008 Developers Share Posted August 4, 2008 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. SciTE4AutoIt3 Full installer Download page - Beta files Read before posting How to post scriptsource Forum etiquette Forum Rules Live for the present, Dream of the future, Learn from the past. Link to comment Share on other sites More sharing options...
Ropetin Again Posted August 4, 2008 Author Share Posted August 4, 2008 Just open the main page for COM reference: mk:@MSITStore:C:\Program%20Files\AutoIt3\beta\AutoIt3.chm::/html/intro/ComRef.htmScroll 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.htmlWhich 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.aspxDoes this sound like I'm on the right track, and if so do you have any suggestion on how to get around it? Link to comment Share on other sites More sharing options...
Developers Jos Posted August 4, 2008 Developers Share Posted August 4, 2008 Does the $oMyError.description contain any useful information? SciTE4AutoIt3 Full installer Download page - Beta files Read before posting How to post scriptsource Forum etiquette Forum Rules Live for the present, Dream of the future, Learn from the past. Link to comment Share on other sites More sharing options...
Ropetin Again Posted August 4, 2008 Author Share Posted August 4, 2008 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 Link to comment Share on other sites More sharing options...
PsaltyDS Posted August 4, 2008 Share Posted August 4, 2008 (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' commandRemove any passwords all togetherRemove all tabs apart from oneUse Excel 2003Use Excel 2007Use AutoIt 3.2.13.6Use AutoIt 3.2.12.1Use version 1.5 of Excel.udfUse version 1.4 of Excel.udfI'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 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 Link to comment Share on other sites More sharing options...
Ropetin Again Posted August 4, 2008 Author Share Posted August 4, 2008 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! Link to comment Share on other sites More sharing options...
PsaltyDS Posted August 5, 2008 Share Posted August 5, 2008 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. 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 Link to comment Share on other sites More sharing options...
Ropetin Again Posted August 5, 2008 Author Share Posted August 5, 2008 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? Link to comment Share on other sites More sharing options...
PsaltyDS Posted August 5, 2008 Share Posted August 5, 2008 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. 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now