_ExcelBookOpen issue

[pierlob]

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

[Authenticity]

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?

[PsaltyDS]

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

[pierlob]

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

[wimhek]

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

[Mikesch]

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

[Juvigy]

Try this one.

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

[PsaltyDS]

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.