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]]]]]] )
| $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) |
| 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 |
$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
#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'.")
#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'.")
#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.")
#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'.")