Check if Excel sheet exists

[rosaz]

Hi - is there an easy way to check whether a worksheet with a certain name exists within a workbook? Thanks for any help!!

[PsaltyDS]

Hi - is there an easy way to check whether a worksheet with a certain name exists within a workbook? Thanks for any help!!

_ExcelSheetList()

[rosaz]

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...

[PsaltyDS]

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?

[reb]

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

[picaxe]

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

[rosaz]

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!