aiter

Excel UDF - activate sheet

6 posts in this topic

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

#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

 

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

Nope, spoke too soon.

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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

 

ScreenCapture.PNG

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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