_ExcelBookOpen fails to work

[Safehaven]

Hey Everyone,

I've recently started using AutoIt to speed up my work a bit and ran into a snag..

On my development PC, I've made a small GUI application which opens an .xls file to get part of it's input. Now the funny thing is that it runs great on that pc (no problems, everything works as it was written) but when I compile the script and try to run it onto any other machine, it will fail to open any .xls(x) file and gets thrown out with an @error 2.

It does not seem to be OS related as I tried different configurations. I have tried the Beta Includes but to no avail.

Can anyone help me?

This is the function where it goes wrong:

All elements passed to it checked out okay, it seems to me that something happends in the _ExcelBookOpen code.

expandcollapse popup

Func OpenTestPlan(ByRef $oExcel, ByRef $TestPlanOpen, $Action, ByRef $AutomationTestColumn)
    switch $Action
        Case "Close"
            if $TestPlanOpen = True Then
                _ExcelBookClose($oExcel)
                $TestPlanOpen = False
                GUICtrlSetData($EditTestCase, "")
                GUICtrlSetData($EditResult, "")
                GUICtrlSetData($EditComment, "")
                GUICtrlSetData($EditBug, "")
                $AutomationTestColumn = "No"
            EndIf
        Case "Open"
            If $TestPlanOpen = False Then
            #Region : Variable Declaration
                Local $WorkingDir
                Local $FileName
                Local $Path
                Local $Worksheet
            #EndRegion : Variable Declaration

            #Region : Function Logic
            $WorkingDir = FileSelectFolder("In which folder is your testplan located?","c",2)                                   ; Get the folder where the File is located
                Switch @error
                    case 1
                        $WorkingDir = ""
                        MsgBox(64,"Cancel Pressed","No testplan opened")
                    case Else
                    ListSelectionGUI("Select your testplan", "Click Here", _FileListToArray($WorkingDir), $FileName)                    ; Select the .xls testplan we are going to work on
                    if $FileName <> "Click Here" then
                        $oExcel = _ExcelBookOpen($WorkingDir & "\" & $FileName,1)                                                       ; Open an existing workbook (visible) and returns its object identifier. !!!!!Modify mode!!!!!!
                        Switch @error
                            case 1
                                MsgBox(16, "Error!", "Unable to Create the Excel Object")
                                Msgbox(46,"Debug Info", "Path Directory : " & $WorkingDir & "\" & $FileName)
                                Exit
                            case 2
                                MsgBox(16, "Error!", "The file does not exist")
                        EndSwitch
                        $TestPlanOpen = True
                        ListSelectionGUI("Select the worksheet to be executed","Click Here", _ExcelSheetList($oExcel), $Worksheet)      ; Select the worksheet to be tested
                        if $Worksheet <> "Click Here" Then
                            _ExcelSheetActivate($oExcel, $Worksheet)                                                                        ; Activate the selected worksheet
                            GUICtrlSetData($EditComment,"Now start the process by clicking the <Next> button below!")
                            $AutomationTestColumn = (-64 + Asc ( InputBox("Automation Input","Which column contains the Automation Scripts?","No")))
                            Switch @error
                                case 1                                                                                          ; Cancel pressed
                                    $AutomationTestColumn = ""
                                    MsgBox(64,"Cancel Pressed","No Automation column selected")
                            EndSwitch
                        Else
                            _ExcelBookClose($oExcel)
                        EndIf
                    EndIf
                EndSwitch
            Else
                MsgBox(16, "Warning", "A Testplan is still open, please close it first!")
            EndIf
            #EndRegion : Function Logic
        EndSwitch
EndFunc

This is the _ExcelBookOpen code which is called:

Func _ExcelBookOpen($sFilePath, $fVisible = 1, $fReadOnly = False, $sPassword = "", $sWritePassword = "")
    Local $oExcel = ObjCreate("Excel.Application")
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If Not FileExists($sFilePath) Then Return SetError(2, 0, 0)
    If $fVisible > 1 Then $fVisible = 1
    If $fVisible < 0 Then $fVisible = 0
    If $fReadOnly > 1 Then $fReadOnly = 1
    If $fReadOnly < 0 Then $fReadOnly = 0
    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
    Return $oExcel
EndFunc   ;==>_ExcelBookOpen

Thanks a lot!!

Kris

[Safehaven]

Okay, I'll make this a bit easier:

#include <Excel.au3>
$PathName = InputBox("PathName","PathName","F:\05-Scripts\IEE_1v3.9_OMS7v1.14-TestPlan.xlsx")
_ExcelBookOpen($PathName)
    switch @error
        case 1
            MsgBox(0,"Unable to Create Object","")
        case 2
            MsgBox(0,"File Does Not Exist","")
        case 0
            MsgBox(0,"Works!","")
    EndSwitch

Does not work on my pc. It always exits in case 1.

The include is as stated in the post above.

[Juvigy]

Try specifying the full path to the file , like :

$oExcel = _ExcelBookOpen("c:\test\test2\file.xls",1)

Or maybe:

oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 1
$oExcel.WorkBooks.Open("c:\test\test2\file.xls")

Edited April 16, 2010 by Juvigy

[Safehaven]

Try specifying the full path to the file , like :

$oExcel = _ExcelBookOpen("c:\test\test2\file.xls",1)

Or maybe:

oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 1
$oExcel.WorkBooks.Open("c:\test\test2\file.xls")

Hey Juvigy,

I tried your first and second proposal but both failed,

The first one failed in the same way as my original attempt (Failed to create object)

The second one was more interesting and produced this running messages:

>Running:(3.3.6.0):D:\Private\AutoIt3\autoit3.exe "C:\Documents and Settings\kris.g\Desktop\e.au3"    
C:\Documents and Settings\kris.g\Desktop\e.au3 (3) : ==> Variable must be of type "Object".:
$oExcel.Visible = 1
$oExcel^ ERROR
->11:52:28 AutoIT3.exe ended.rc:1
>Exit code: 1    Time: 1.847

Code used:

#include <Excel.au3>
$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 1
$oExcel.WorkBooks.Open("F:\05-Scripts\IEE_1v3.9_OMS7v1.14-TestPlan.xlsx")
    switch @error
        case 1
            MsgBox(0,"Unable to Create Object","")
        case 2
            MsgBox(0,"File Does Not Exist","")
        case 0
            MsgBox(0,"Works!","")
    EndSwitch

The speed on this forum is awesome, keep up the good work!

Kris

[Juvigy]

Do you have excel installed and which version?

[l3ill]

When working cross platform I have found this method of opening (especially Excel) files saves a lot of debugging...

#include<Excel.au3>
    #include<File.au3>

    $message = "Select Workbook"
    $var = FileOpenDialog($message, @DesktopDir & "\", "Excel (*.xls;*.xlsx)", 1)
    If @error Then
        MsgBox(4096, "", "No File Selected - Exiting")
        Return 0

    Else
        $var = StringReplace($var, "|", @CRLF)
    EndIf
    $oExcel = _ExcelBookOpen($var)

switch @error
    case 1
    MsgBox(0,"Unable to Create Object","")
    case 2
    MsgBox(0,"File Does Not Exist","")
    case 0
    MsgBox(0,"Works!","")
EndSwitch

Good Luck

[Safehaven]

Hey Billo,

I've used part of your code (OpenFileDialog) to further simplify my script. Thanks a lot for that!

Back on topic: It seems that the problem for my @error message lies in the fact that the .xls application is working via a hosted application. This is the reason why the script works on one pc (with .xls installed locally) but not on the other pc (where it is "streamed in").

If I could get the object handle I am sure I could use the ObjCreate to start my application.

Is there an easy way of getting this info (ObjGet didn't do the trick..))

Thanks again!

Kris

[Juvigy]

So you dont have excel installed?

What do you mean by hosted application?

Try with

Shelexecute("F:\05-Scripts\IEE_1v3.9_OMS7v1.14-TestPlan.xlsx")

Which should open the file

[Safehaven]

So you dont have excel installed?

What do you mean by hosted application?

Try with

Shelexecute("F:\05-Scripts\IEE_1v3.9_OMS7v1.14-TestPlan.xlsx")

Which should open the file

Juvigy,

You are the man

This worked perfectly, thanks a lot! This solves a major headache!

To come back to your question, or mgmt has decided to work with hosted applications to cut back on some $$. Now how this works is that the applications (like .xls) are hosted off site and "dispatched" to you on demand. This will cause the normal Obj definitions not to work since they are handled via the "gateway" software.

But I haven't thought about the fact that it would be possible to pull them trough the shell and let windows assign the correct app for the job!

Thx Again and have a good weekend!

Kris

[vrsrinivas2000]

Now this code was not working, we get below error,

For $i = 1 To .activeworkbook.sheets.count
For $i = 1 To .activeworkbook^ ERROR

Please give me suggestion how rectify it. 

[water]

You know that this thread is ~ 13 years old?
The OP has left 2011, Excel has changed a lot and the Excel UDF had a full rewrite.
So please open a new thread and provide more information so we can help you to  fix your problem.
At least post your source code, the version of AutoIt you run plus the error messages you get in the SciTE console when running your script.