Jump to content
Sign in to follow this  

Excel Add Worksheet After Worksheets.Count

Recommended Posts


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)

Share this post

Link to post
Share on other sites

#include <Excel.au3>

$oExcel = _ExcelBookNew()

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

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
        For $i = 1 To $iSheetCount
            If $oXl.ActiveWorkbook.Sheets($i).Name = $sAfterSheet Then
                $oXl.ActiveWorkBook.WorkSheets.Add(Default, $oXl.ActiveWorkbook.Sheets($i)).Activate
        If $i > $iSheetCount Then Return SetError(2, 0, 0)
    If $sNewSheetName <> "" Then $oXl.ActiveSheet.Name = $sNewSheetName
    Return 1

Share this post

Link to post
Share on other sites

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 :blink:

Share this post

Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this