_Excel_SheetAdd
Show description in
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
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.")