aiter Posted August 4, 2016 Share Posted August 4, 2016 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 Link to comment Share on other sites More sharing options...
aiter Posted August 4, 2016 Author Share Posted August 4, 2016 I added #include <array.au3> to above script - seems to have fixed the intermittent fail, but maybe I had a lucky run. Link to comment Share on other sites More sharing options...
aiter Posted August 4, 2016 Author Share Posted August 4, 2016 Nope, spoke too soon. Link to comment Share on other sites More sharing options...
aiter Posted August 4, 2016 Author Share Posted August 4, 2016 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 Link to comment Share on other sites More sharing options...
PACaleala Posted August 9, 2016 Share Posted August 9, 2016 Your worksheets list is created in reverse numerical order (see attached file) Link to comment Share on other sites More sharing options...
water Posted August 9, 2016 Share Posted August 9, 2016 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now