rosaz Posted September 11, 2009 Share Posted September 11, 2009 Hi - is there an easy way to check whether a worksheet with a certain name exists within a workbook? Thanks for any help!! Link to comment Share on other sites More sharing options...
PsaltyDS Posted September 11, 2009 Share Posted September 11, 2009 Hi - is there an easy way to check whether a worksheet with a certain name exists within a workbook? Thanks for any help!!_ExcelSheetList() Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Link to comment Share on other sites More sharing options...
rosaz Posted September 11, 2009 Author Share Posted September 11, 2009 For some reason its not working for me... here's my code: WinWaitActive("Microsoft Excel") #include <Excel.au3> #Include <Array.au3> $title = WinGetTitle("[active]") $oExcel = _ExcelBookAttach ($title, "Title") $eArray = _ExcelSheetList($oExcel) _ArrayDisplay ($eArray) I can't figure out what's wrong with it... Link to comment Share on other sites More sharing options...
PsaltyDS Posted September 11, 2009 Share Posted September 11, 2009 For some reason its not working for me... here's my code: WinWaitActive("Microsoft Excel") #include <Excel.au3> #Include <Array.au3> $title = WinGetTitle("[active]") $oExcel = _ExcelBookAttach ($title, "Title") $eArray = _ExcelSheetList($oExcel) _ArrayDisplay ($eArray) I can't figure out what's wrong with it... You didn't post what happened when you ran that. What do you get in the SciTE console? Is $oExcel valid? Does the array display at all? Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Link to comment Share on other sites More sharing options...
reb Posted September 11, 2009 Share Posted September 11, 2009 (edited) LOOK AT EXAMPLE 3 IN THE HELP FILE FOR ExcelSheetList() Any of them will shed light on the subject REB Sorry my caps lock was on and I didn't notice. Didn't mean to shout. REB Edited September 11, 2009 by reb MEASURE TWICE - CUT ONCE Link to comment Share on other sites More sharing options...
picaxe Posted September 12, 2009 Share Posted September 12, 2009 (edited) As documented in help _ExcelSheetList uses the Excel object but _ExcelBookAttach returns a Workbook object. Here's the modified function which works for me.expandcollapse popup#include <Excel.au3> #include <Array.au3> $sFilePath = @ScriptDir & "\test.xls" WinActivate("Microsoft Excel") Sleep(100) $title = WinGetTitle("[active]") $oExcel = __ExcelBookAttach($title, "title") $eArray = _ExcelSheetList($oExcel) _ArrayDisplay($eArray) ; #FUNCTION# ==================================================================================================================== ; Name...........: __ExcelBookAttach (modified to return Excel object for title search mode) ; Description ...: Attach to the first existing instance of Microsoft Excel where the search string matches based on the selected mode. ; Syntax.........: _ExcelBookAttach($s_string, $s_mode = "FilePath") ; Parameters ....: $s_string - String to search for ; $s_mode - Optional: specifies search mode: ; |FileName - Name of the open workbook ; |FilePath - (Default) Full path to the open workbook ; |Title - Title of the Excel window ; Return values .: Success - Returns an object variable pointing to the Excel.Application, workbook object ; Failure - Returns 0 and sets @ERROR = 1 ; Author ........: Bob Anthony (big_daddy) ; Modified.......: ; Remarks .......: ; Related .......: _ExcelBookNew, _ExcelBookOpen ; Link ..........; ; Example .......; Yes ; =============================================================================================================================== Func __ExcelBookAttach($s_string, $s_mode = "FilePath") Local $o_Result, $o_workbook, $o_workbooks If $s_mode = "filepath" Then $o_Result = ObjGet($s_string) If Not @error And IsObj($o_Result) Then Return $o_Result EndIf EndIf $o_Result = ObjGet("", "Excel.Application") If @error Or Not IsObj($o_Result) Then ConsoleWrite("--> Warning from function _ExcelAttach, No existing Excel.Application object" & @CR) Return SetError(1, 0, 0) EndIf $o_workbooks = $o_Result.Application.Workbooks If Not IsObj($o_workbooks) Or $o_workbooks.Count = 0 Then ConsoleWrite("--> Warning from function _ExcelAttach, No existing Excel.Application windows" & @CR) Return SetError(1, 0, 0) EndIf For $o_workbook In $o_workbooks Switch $s_mode Case "filename" If $o_workbook.Name = $s_string Then Return $o_workbook EndIf Case "filepath" If $o_workbook.FullName = $s_string Then Return $o_workbook EndIf Case "title" If ($o_workbook.Application.Caption) = $s_string Then ;Return $o_workbook Return $o_Result EndIf Case Else ConsoleWrite("--> Error from function _ExcelAttach, Invalid Mode Specified" & @CR) Return SetError(1, 0, 0) EndSwitch Next ConsoleWrite("--> Warning from function _ExcelAttach, No Match" & @CR) Return SetError(1, 0, 0) EndFunc ;==>__ExcelBookAttach Edit: remove debugging lines and WinWaitActivate Edited September 13, 2009 by picaxe Link to comment Share on other sites More sharing options...
rosaz Posted September 18, 2009 Author Share Posted September 18, 2009 Thank you picaxe! I hadn't realized _ExcelSheetList needed the Excel object - when I tried it that way it worked! Here's the code I ended up using: #include <Excel.au3> #Include <Array.au3> $title = WinGetTitle("[active]") $oExcel = _ExcelBookAttach($title, "Title") $objExcel = ObjGet( $oExcel,"Excel.Application") $aArray = _ExcelSheetList($objExcel) _ArrayDisplay ($aArray) Thank you so much for your help! 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