Function Reference

_Excel_SheetAdd

Adds new sheet(s) to a workbook and sets their names

#include <Excel.au3>
_Excel_SheetAdd ( $oWorkbook [, $vSheet = Default [, $bBefore = True [, $iCount = 1 [, $sName = ""]]]] )

Parameters

$oWorkbook A workbook object
$vSheet [optional] Object, index or name of the sheet before/after which the new sheet is inserted.
    -1 = insert before/after the last worksheet (default = keyword Default = active worksheet)
$bBefore [optional] The new sheet will be inserted before $vSheet if True, after $vSheet if False (default = True)
$iCount [optional] Number of worksheets to be inserted (default = 1). Maximum is 255
$sName [optional] Name(s) of the sheet(s) to create (default = "" = follows standard Excel new sheet convention).
    When $iCount > 1 multiple names can be provided separated by | (pipe character). Sheets are named from left to right

Return Value

Success: an object of the (first) added worksheet.
Failure: 0 and sets @error.
@error: 1 - $oWorkbook is not an object or not a workbook object
2 - $vSheet is invalid. Name or index does not exist. @extended is set to the COM error code
3 - Specified sheet already exists. @extended is set to the number of the name in $sName
4 - Error occurred when adding the sheet. @extended is set to the COM error code
5 - Error occurred when setting the name of the new sheet(s). @extended is set to the COM error code
6 - Parameter $iCount > 255

Related

_Excel_SheetDelete

Example

Example 1

#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetAdd Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Extras\_Excel1.xls")
If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetAdd Example", "Error opening workbook '" & @ScriptDir & "\_Extras\Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
EndIf

; Insert two sheets after the last sheet and name them
_Excel_SheetAdd($oWorkbook, -1, False, 2, "Test1|Test2")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetAdd Example 1", "Error adding sheets." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetAdd Example 1", "Two sheets added after the last one.")

Example 2

#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetAdd Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Extras\_Excel1.xls", True)
If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetAdd Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
EndIf

; Insert a sheet before sheet 2. Name is default name
_Excel_SheetAdd($oWorkbook, 2)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetAdd Example 2", "Error adding sheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetAdd Example 2", "Sheet added before sheet 2.")

Example 3

#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetAdd Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Extras\_Excel1.xls", True)
If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetAdd Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
EndIf

; Insert an index sheet with links to all other sheets.
; Handles Sheet names with spaces correctly.
Local $oSheet = _Excel_SheetAdd($oWorkbook, 1, True, 1, "Index")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetAdd Example 3", "Error adding sheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
$oSheet.Range("A1").Value = "Index"
Local $iRow = 2
For $iSheet = 2 To $oWorkbook.Sheets.Count
        $oSheet.Cells($iRow, 1).Value = $iRow - 1
        $oSheet.Cells($iRow, 2).Value = $oWorkbook.Worksheets($iRow).Name
        $oSheet.Hyperlinks.Add($oSheet.Cells($iRow, 2), "", "'" & $oSheet.Cells($iRow, 2).Value & "'!A1")
        $iRow = $iRow + 1
Next
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetAdd Example 3", "Index Sheet inserted as sheet 1.")