Opened 13 years ago

Last modified 12 years ago

#2199 closed Bug

_ExcelSheetAddNew() abends if specified sheet already exists — at Initial Version

Reported by: Spiff59 Owned by:
Milestone: 3.3.9.5 Component: Standard UDFs
Version: 3.3.8.1 Severity: None
Keywords: Cc:

Description

The _ExcelSheetAddNew() function abnormally terminates script execution with a COM error if called specifying a sheet name that already exists within the workbook. Also, after the error, the workbook will erroneously contain a new generically named sheet ("Sheet 1", "Sheet 2", etc).

The following change adds a loop to check if the sheet is already in the workbook, and kicks out a new @error value (@error = 2) if found.

; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelSheetAddNew
; Description ...: Add new sheet to workbook - optionally with a name.
; Syntax.........: _ExcelSheetAddNew($oExcel[, $sName = ""])
; Parameters ....: $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                  $sName - The name of the sheet to create (default follows standard Excel new sheet convention)
; Return values .: Success      - Returns 1
;                  Failure		- Returns 0 and sets @error on errors:
;                  |@error=1     - Specified object does not exist
;                  |@error=2     - Specified sheet already exists
; Author ........: SEO <locodarwin at yahoo dot com>
; Modified.......: litlmike, Spiff59
; Remarks .......: None
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _ExcelSheetAddNew($oExcel, $sName = "")
	If Not IsObj($oExcel) Then Return SetError(1)
	If $sName Then
		Local $iTemp = $oExcel.ActiveWorkbook.Sheets.Count
		For $xx = 1 To $iTemp
			If $oExcel.ActiveWorkbook.Sheets($xx).Name = $sName Then
				Return SetError(2)
			EndIf
		Next
	EndIf
	Local $oSheet = $oExcel.ActiveWorkBook.WorkSheets.Add().Activate()
	If $sName Then $oExcel.ActiveSheet.Name = $sName
	Return 1
EndFunc   ;==>_ExcelSheetAddNew

Change History (0)

Note: See TracTickets for help on using tickets.