Function Reference


_Excel_RangeCopyPaste

Cuts or copies one or multiple cells, rows or columns to a range or from/to the clipboard

#include <Excel.au3>
_Excel_RangeCopyPaste ( $oWorksheet, $vSourceRange [, $vTargetRange = Default [, $bCut = False [, $iPaste = Default [, $iOperation = Default [, $bSkipBlanks = False [, $bTranspose = False]]]]]] )

Parameters

$oWorksheet Object of the source worksheet
$vSourceRange Source range to copy/cut from. Can be a range object or an A1 range.
    If set to keyword Default then the range will be copied from the clipboard.
$vTargetRange [optional] Target range to copy/cut to. Can be a range object or an A1 range.
    If set to keyword Default then the range will be copied to the clipboard (default = keyword Default)
$bCut [optional] If set to True the source range isn't copied but cut out (default = False)
    This parameter is ignored when $vSourceRange is set to keyword Default.
$iPaste [optional] The part of the range to be pasted from the clipboard (formulas, formats ...). Must be a value of the XlPasteType enumeration
    (default = keyword Default)
$iOperation [optional] The paste operation (add, divide, multiply ...). Must be a value of the XlPasteSpecialOperation enunmeration
    (default = keyword Default)
$bSkipBlanks [optional] If set to True blank cells from the clipboard will not be pasted into the target range (default = False)
$bTranspose [optional] Set to True to transpose rows and columns when the range is pasted (default = False)

Return Value

Success: the object of the target range if $vTargetRange <> Default, else 1.
Failure: 0 and sets @error.
@error: 1 - $oWorksheet is not an object or not a worksheet object
2 - $vSourceRange is invalid. @extended is set to the COM error code
3 - $vTargetRange is invalid. @extended is set to the COM error code
4 - Error occurred when pasting cells. @extended is set to the COM error code
5 - Error occurred when cutting cells. @extended is set to the COM error code
6 - Error occurred when copying cells. @extended is set to the COM error code
7 - $vSourceRange and $vTargetRange can't be set to keyword Default at the same time

Remarks

$vSourceRange and $vTargetRange can't be set to keyword Default at the same time.
If $vSourceRange = Default then:
* the range will be copied from the clipboard using the PasteSpecial method
* $bCut will be ignored
* $iPaste, $iOperation, $bSkipBlanks and $bTranspose will be honored
If $vSourceRange and $vTargetRange are specified parameters $iPaste, $iOperation, $bSkipBlanks and $bTranspose are ignored

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

; *****************************************************************************
; Copy a range with 3 rows and 2 columns on the active worksheet.
; Pass the source range as object.
; *****************************************************************************
Local $oRange = $oWorkbook1.ActiveSheet.Range("I2:J4")
_Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $oRange, "G7")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 1", "Error copying cells." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 1", "Range 'I2:J4' successfully copied to 'G7'.")

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_RangeCopy 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_RangeCopy 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_RangeCopy Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

; *****************************************************************************
; Copy a single cell from another workbook. Pass the source range as object.
; *****************************************************************************
Local $oRange = $oWorkbook2.Worksheets(1).Range("A1")
_Excel_RangeCopyPaste($oWorkbook1.Worksheets(1), $oRange, "G15")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 2", "Error copying cells." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 2", "Range 'A1' from workbook _Excel3.xls successfully copied to 'G15'.")

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_RangeCopy 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_RangeCopy Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

; *****************************************************************************
; Copy 2 rows (1 and 2) from worksheet 2 to the clipboard
; *****************************************************************************
_Excel_RangeCopyPaste($oWorkbook1.Worksheets(2), "1:2")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 3", "Error copying rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 3", "Rows 1+2 successfully copied from worksheet 2 to the clipboard.")

; *****************************************************************************
; Paste the range written by Example 3 from the clipboard to the active worksheet.
; Only values without formatting will be pasted.
; *****************************************************************************
_Excel_RangeCopyPaste($oWorkbook1.Activesheet, Default, "1:1", Default, $xlPasteValues)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "Error copying rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "2 Rows successfully pasted from the clipboard to row 1.")

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

; *****************************************************************************
; Paste the format of a cell to other cells
; *****************************************************************************
_Excel_RangeCopyPaste($oWorkbook1.Activesheet, "A1") ; Copy the cell to the clipboards
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example5", "Error copying rcell A1." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_RangeCopyPaste($oWorkbook1.Activesheet, Default, "B1:E16", Default, $xlPasteFormats) ; paste the format to the target range
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example5", "Error pasting cells." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 5", "Format of cell 'A1' successfully pasted to 'B1:E16'.")