Function Reference


_Excel_SheetCopyMove

Copies or moves the specified sheet before or after a specified sheet in the same or a different workbook

#include <Excel.au3>
_Excel_SheetCopyMove ( $oSourceBook [, $vSourceSheet = Default [, $oTargetBook = $oSourceBook [, $vTargetSheet = 1 [, $bBefore = True [, $bCopy = True]]]]] )

Parameters

$oSourceBook Object of the source workbook where the sheet should be copied/moved from
$vSourceSheet [optional] Name, index or object of the sheet to copy/move (default = keyword Default = active sheet)
$oTargetBook [optional] Object of the target workbook where the sheet should be copied/moved to (default = keyword Default = $oSourceBook)
$vTargetSheet [optional] The copied/moved sheet will be placed before or after this sheet (name, index or object) (default = keyword Default = first sheet)
$bBefore [optional] The copied/moved sheet will be placed before $vTargetSheet if True, after it if False (default = True)
$bCopy [optional] Copy the specified sheet if True, move the sheet if False (default = True)

Return Value

Success: an object of the copied/moved sheet.
Failure: 0 and sets @error.
@error: 1 - $oSourceBook is not an object or not a workbook object
2 - $oTargetBook is not an object or not a workbook object
3 - Specified source sheet does not exist. Name or index is invalid. @extended is set to the COM error code
4 - Specified target sheet does not exist. Name or index is invalid. @extended is set to the COM error code
5 - Error occurred when copying/moving the sheet. @extended is set to the COM error code

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_SheetCopyMove Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
; Open Workbook 1
Local $oWorkbook1 = _Excel_BookOpen($oExcel, @ScriptDir & "\Extras\_Excel1.xls")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetCopyMove Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

; *****************************************************************************
; Copy Sheet 1 after Sheet 3. Set the name of the new Sheet
; *****************************************************************************
Local $oCopiedSheet = _Excel_SheetCopyMove($oWorkbook1, 1, Default, 3, False)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetCopyMove Example 1", "Error copying sheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
; Set the name of the new sheet
#forceref $oCopiedSheet
$oCopiedSheet.Name = "Copied sheet"
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetCopyMove Example 1", "Sheet 1 copied after sheet 3 and renamed")

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_SheetCopyMove Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
; Open Workbook 1
Local $oWorkbook1 = _Excel_BookOpen($oExcel, @ScriptDir & "\Extras\_Excel1.xls")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetCopyMove Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

; *****************************************************************************
; Move Sheet 2 before Sheet 1. Set the name of the moved Sheet
; *****************************************************************************
Local $oMovedSheet = _Excel_SheetCopyMove($oWorkbook1, 2, Default, 1, Default, False)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetCopyMove Example 2", "Error moving sheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
; Set the name of the new sheet
#forceref $oMovedSheet
$oMovedSheet.Name = "Moved sheet"
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetCopyMove Example 2", "Sheet 2 moved before sheet 1")

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_SheetCopyMove Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
; Open Workbook 2
Local $oWorkbook2 = _Excel_BookOpen($oExcel, @ScriptDir & "\Extras\_Excel3.xls")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetCopyMove Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel3.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf
; Open Workbook 1
Local $oWorkbook1 = _Excel_BookOpen($oExcel, @ScriptDir & "\Extras\_Excel1.xls")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetCopyMove Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

; *****************************************************************************
; Copy Sheet 3 of Workbook 1 after Sheet 3 of Workbook 2
; *****************************************************************************
_Excel_SheetCopyMove($oWorkbook1, 3, $oWorkbook2, 3, False)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetCopyMove Example 3", "Error copying sheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetCopyMove Example 3", "Workbook 1 Sheet 3 copied to Workbook2 after sheet 3")

Example 4

#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_SheetCopyMove Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
; Open Workbook 2
Local $oWorkbook2 = _Excel_BookOpen($oExcel, @ScriptDir & "\Extras\_Excel3.xls", True)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetCopyMove Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel3.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf
; Open Workbook 1
Local $oWorkbook1 = _Excel_BookOpen($oExcel, @ScriptDir & "\Extras\_Excel1.xls", True)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetCopyMove Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

; *****************************************************************************
; Move Sheet 1 of Workbook 1 before Sheet 1 of Workbook 2
; *****************************************************************************
_Excel_SheetCopyMove($oWorkbook1, 1, $oWorkbook2, 1, Default, False)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetCopyMove Example 4", "Error moving sheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetCopyMove Example 4", "Workbook 1 Sheet 1 moved to Workbook2 before sheet 1")