Excel Add Worksheet After Worksheets.Count

[Fatum48]

Trying to find the workaround to add Worksheet in Excel After the Specified sheet and not Before (AutoIt seems to add them before the specified sheet)

[picaxe]

expandcollapse popup

#include <Excel.au3>

$oExcel = _ExcelBookNew()

For $i = 1 To 3
    _ExcelSheetAddNew($oExcel, "S" & $i)
Next

ConsoleWrite("return=" & _ExcelSheetAddAfter($oExcel, "S2", "Add After S2") & " @error=" & @error & @LF)
ConsoleWrite("return=" & _ExcelSheetAddAfter($oExcel) & " @error=" & @error & @LF)
ConsoleWrite("return=" & _ExcelSheetAddAfter($oExcel, "", "Add After Last") & " @error=" & @error & @LF)


; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelSheetAddAfter
; Description ...: Add a new sheet (optionally with a name) to a workbook after a named sheet.
; Syntax.........: _ExcelSheetAddAfter($oExcel [, $sAfterSheet = "" [, $sNewSheetName = ""]])
; Parameters ....: $oExcel  - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;   $sAfterSheet - Name of the sheet after which a new sheet will be added, add to end if not supplied
;   $sNewSheetName - Name of the added new sheet, use default name if none supplied
; Return values .: Success  - Returns 1
;   Failure  - Returns 0 and sets @error on errors:
;   @error=1    - Specified object does not exist
;   @error=2    - $sAfterSheet name not found
; Author ........: picaxe
; Remarks .......: None
; ===============================================================================================================================
Func _ExcelSheetAddAfter($oXl, $sAfterSheet = "", $sNewSheetName = "")
    If Not IsObj($oXl) Then Return SetError(1, 0, 0)
    Local $iSheetCount = $oXl.ActiveWorkbook.Sheets.Count
    If $sAfterSheet = "" Then
        $oXl.ActiveWorkBook.WorkSheets.Add(Default, $oXl.ActiveWorkbook.Sheets($iSheetCount)).Activate
    Else
        For $i = 1 To $iSheetCount
            If $oXl.ActiveWorkbook.Sheets($i).Name = $sAfterSheet Then
                $oXl.ActiveWorkBook.WorkSheets.Add(Default, $oXl.ActiveWorkbook.Sheets($i)).Activate
                ExitLoop
            EndIf
        Next
        If $i > $iSheetCount Then Return SetError(2, 0, 0)
    EndIf
    If $sNewSheetName <> "" Then $oXl.ActiveSheet.Name = $sNewSheetName
    Return 1
EndFunc

[Fatum48]

Thank You picaxe for the reply!

Just too much information for what I needed to know. The only thing I needed to know is how to simulate the "After:=" statement ... and the (Default, ...) made it.

Example: $oExcel.Worksheets.Add(Default, $oExcel.WorkSheets($oExcel.WorkSheets.Count))

But I still thanks you for the reply cause you provided me with the answer I needed. I'm just not a big fan of UDF cause most part of the time they test things I really do not need to test but they are still well builded.

I'm new to AutoIt and so far so good I realy like it!

Thanks