Excel UDF - activate sheet

[aiter]

I want to open an exel workbook, then get a list of the sheets and display them as a list.

By clicking on a list item, Excel must change to the corresponding sheet.

My code is below , but I think there must be an easier way to do this (and it sometimes fails with error message (console output "(The requested action with this object has failed"

Someone please explain why my script sometimes fails with error message above and also show a more elegant solution.

If I could trap the error message somehow so as to exit gracefully would help.

Thanks

expandcollapse popup

#include <Excel.au3>
#include <GuiListBox.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <FileConstants.au3>
#include <MsgBoxConstants.au3>

Main()

Func Main()


    $sFilePath1 = FileOpenDialog("Choose an excel file", @WindowsDir & "\", "Excel (*.xls*)", $FD_FILEMUSTEXIST)
    Switch @error
        Case 1
            Exit
        Case 2
            MsgBox(0, 'Error', 'Bad file filter')
            Exit
    EndSwitch
    If $sFilePath1 = "" Then
        Exit
    EndIf

    Local $oExcel = _Excel_Open()

    Local $oBook = _Excel_BookOpen($oExcel, $sFilePath1, 0, 1) ;! not readonly , visible

    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")
        Exit
    EndIf

    Local $aArray = _Excel_SheetList($oBook)
    ;_ArrayDisplay($aArray)
    ;exit

    Local $hListBox, $msg, $menustate, $menutext, $i, $cnt, $temp
    $cnt = UBound($aArray) - 1
    ; Create GUI
    GUICreate("List Box Get Sel", 400, 296)
    $hListBox = GUICtrlCreateList("", 2, 2, 396, 296, BitOR($WS_BORDER, $WS_VSCROLL, $LBS_NOTIFY, $LBS_DISABLENOSCROLL, $WS_HSCROLL))
    ;GUISetState()

    GUISetState(@SW_SHOW)

    ; Add strings
    _GUICtrlListBox_BeginUpdate($hListBox)
    For $i = $cnt To 0 Step -1
        _GUICtrlListBox_AddString($hListBox, $aArray[$i][0])
    Next
    _GUICtrlListBox_UpdateHScroll($hListBox)
    _GUICtrlListBox_EndUpdate($hListBox)

    ; Loop until user exits
    Do
        $msg = GUIGetMsg()
        If $msg = $hListBox Then
            $i = String(GUICtrlRead($hListBox))
            $temp = _ArraySearch($aArray, $i)
            If $temp <> -1 Then
                $temp = Number($temp+1)
                $oBook.Sheets($temp).Activate
            EndIf
        EndIf
    Until $msg = $GUI_EVENT_CLOSE
    GUIDelete()
EndFunc   ;==>Main

 

[aiter]

I added #include <array.au3> to above script - seems to have fixed the intermittent fail, but maybe I had a lucky run.

[aiter]

Nope, spoke too soon.

[aiter]

Here is the error I sometimes get

Problem signature:
  Problem Event Name:   APPCRASH
  Application Name: autoit3.exe
  Application Version:  3.3.14.2
  Application Timestamp:    55fc1979
  Fault Module Name:    OLEAUT32.dll
  Fault Module Version: 6.1.7601.23452
  Fault Module Timestamp:   57349ed8
  Exception Code:   c0000005
  Exception Offset: 000211c3
  OS Version:   6.1.7601.2.1.0.256.48
  Locale ID:    7177
  Additional Information 1: 0a9e
  Additional Information 2: 0a9e372d3b4ad19135b953a78882e789
  Additional Information 3: 0a9e
  Additional Information 4: 0a9e372d3b4ad19135b953a78882e789

 

[PACaleala]

Your worksheets list is created in reverse numerical order (see attached file)

 

[water]

Does your script crash when you display the List Box or when you select a sheet?
Which version of Office do you run?

I tested with AutoIt 3.3.12.0 and 3.3.15.0 and it never crashed.