Opened 12 years ago

Closed 11 years ago

#2199 closed Bug (Fixed)

_ExcelSheetAddNew() abends if specified sheet already exists

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

Description (last modified by water)

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) 
        Local $oSheet = $oExcel.ActiveWorkBook.WorkSheets.Add().Activate() 
        If $sName Then $oExcel.ActiveSheet.Name = $sName 
        Return 1 
EndFunc   ;==>_ExcelSheetAddNew 

Attachments (0)

Change History (6)

comment:1 Changed 12 years ago by anonymous

I suppose I could have made the @error = 2 test a single-line If/Then statement, if anyone cares...

comment:2 Changed 12 years ago by trancexx

  • Resolution set to Rejected
  • Status changed from new to closed

That function doesn't follow its specification regarding return values.
If you want to submit some code then either do it correctly or don't do it at all. I understand the desire to be useful but please don't do it by adding new bugs.

I'll close this as "Rejected", and I'm hoping you will report it again, only correctly next time. By correctly I mean with explanation of the bug, reproducer (where is it now?), and suggestions if you have any.

comment:3 Changed 12 years ago by anonymous

Oh, the SetError() returns... shame on me.
Leaving it as rejected works for me.

comment:4 Changed 11 years ago by water

  • Component changed from AutoIt to Standard UDFs
  • Description modified (diff)
  • Resolution Rejected deleted
  • Status changed from closed to reopened

comment:5 Changed 11 years ago by water

  • Description modified (diff)

comment:6 Changed 11 years ago by water

  • Milestone set to
  • Owner set to water
  • Resolution set to Fixed
  • Status changed from reopened to closed

Fixed by revision [7352] in version:

Guidelines for posting comments:

  • You cannot re-open a ticket but you may still leave a comment if you have additional information to add.
  • In-depth discussions should take place on the forum.

For more information see the full version of the ticket guidelines here.

Add Comment

Modify Ticket

as closed The owner will remain water.

E-mail address and user name can be saved in the Preferences.

Note: See TracTickets for help on using tickets.