Sign in to follow this  
Followers 0
pierlob

_ExcelBookOpen issue

8 posts in this topic

hi,

Before starting I'd like to warn you, english is not my native language so I'll try to use the best english I can, especially in a technical contexte like that.

I'm currently developing a dashboard that displays financial data from a .xlsx file. this file is a official microsoft financial repport, using a lot of macro and things like that inside excel (about which I have almost not any knowledge). So I first created a AutoIt macro that's supposed to simply open this .xlsx file. (i tried it with a homemade .xlsx file and it worked properly).

#include <Excel.au3>

$oExcel = _ExcelBookOpen("C:\...\file.xlsx")

that's it..

But as the file is being openned (there is a connection to MS database etc... to write the data into the file), after around 60 seconds the AutoIt script ends with this error:

>"C:\Program Files\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "E:\Documents\Stage Microsoft\dashboard\macro\autoIT\testextractexcel.au3"

C:\Program Files\AutoIt3\Include\Excel.au3 (190) : ==> The requested action with this object has failed.:

.ActiveWorkbook.Sheets(1).Select ()

.ActiveWorkbook.Sheets(1).Select ()^ ERROR

>Exit code: 1 Time: 61.791

And I have absolutly no idea of what the problem could be (i'm an AutoIt novice). The document might be protected somehow, or maybe there is a compatibility issue between autoIt and some Excel 2007 functionnality used in it. Does (190) specified the error type ?

these are all the usefull informations I can think about to make it easier for you to help me, if there are any other details I should give, plz ask! I really need to solve this problem...

thanks a lot in advance :)

Share this post


Link to post
Share on other sites



The 190 specifies the line number the error occurred in, it may not be the offending line though. Can you post the code relevant to this line?

Share this post


Link to post
Share on other sites

That's line 190 of the Excel.au3 as shipped with the production version of AutoIt. It is line 196 in the version shipped with Beta. There was a change there to select the first VISIBLE sheet vice the first sheet in the collection. Perhaps you need to run the Beta version because there is a hidden sheet in the collection before the one you wanted.

Prod version:

With $oExcel
        .Visible = $fVisible
        If $sPassword <> "" And $sWritePassword <> "" Then .WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, $sPassword, $sWritePassword)
        If $sPassword = "" And $sWritePassword <> "" Then .WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, Default, $sWritePassword)
        If $sPassword <> "" And $sWritePassword = "" Then .WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, $sPassword, Default)
        If $sPassword = "" And $sWritePassword = "" Then .WorkBooks.Open($sFilePath, Default, $fReadOnly)
        .ActiveWorkbook.Sheets(1).Select ()
    EndWith

Beta version:

With $oExcel
        .Visible = $fVisible
        If $sPassword <> "" And $sWritePassword <> "" Then .WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, $sPassword, $sWritePassword)
        If $sPassword = "" And $sWritePassword <> "" Then .WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, Default, $sWritePassword)
        If $sPassword <> "" And $sWritePassword = "" Then .WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, $sPassword, Default)
        If $sPassword = "" And $sWritePassword = "" Then .WorkBooks.Open($sFilePath, Default, $fReadOnly)

        ; Select the first *visible* worksheet.
        For $i = 1 To .ActiveWorkbook.Sheets.Count
            If .ActiveWorkbook.Sheets($i).Visible = $xlSheetVisible Then
                .ActiveWorkbook.Sheets($i).Select()
                ExitLoop
            EndIf
        Next
    EndWith

:)


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

the use of the beta version solved the problem, thank you so much ! :)

Share this post


Link to post
Share on other sites

I get the same message, I am running with the Beta include. When I run My script on a New Sheet, everthing words fine, with an existing sheet (witd renamed , and probably deleted tabs) , I get the message

C:\Program Files\AutoIt3\Include\Excel.au3 (190) : ==> The requested action with this object has failed.:

.ActiveWorkbook.Sheets(1).Select ()

.ActiveWorkbook.Sheets(1).Select ()^ ERROR

Any help would be nice

Share this post


Link to post
Share on other sites

Hi,

the path within the error message looks as if you actually used the production version, not the Beta.

Make sure you use the correct version.

Path to the includes would then look like:

C:\Program Files\AutoIt3\Beta\Include\Excel.au3

Regards,

Mike

Share this post


Link to post
Share on other sites

Try this one.

$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 1
$oExcel.WorkBooks.Open("C:\...\file.xlsx")

Share this post


Link to post
Share on other sites

I get the same message, I am running with the Beta include. When I run My script on a New Sheet, everthing words fine, with an existing sheet (witd renamed , and probably deleted tabs) , I get the message

C:\Program Files\AutoIt3\Include\Excel.au3 (190) : ==> The requested action with this object has failed.:

.ActiveWorkbook.Sheets(1).Select ()

.ActiveWorkbook.Sheets(1).Select ()^ ERROR

Any help would be nice

You need to update. The current Production version is 3.3.4.0, and the current Beta is 3.3.5.1. That line number (190) is wrong for both. In addition, the change I mentioned earlier was made to the production version of Excel.au3 and that UDF hasn't changed since, so Beta is exactly the same at this time.

:D


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