Jump to content
Sign in to follow this  
Fatum48

Excel Add Worksheet After Worksheets.Count

Recommended Posts

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)

Share this post


Link to post
Share on other sites
picaxe

#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

Share this post


Link to post
Share on other sites
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 :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  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.