Function Reference


_Excel_RangeInsert

Inserts one or multiple empty cells, rows or columns into the specified worksheet

#include <Excel.au3>
_Excel_RangeInsert ( $oWorksheet, $vRange [, $iShift = Default [, $iCopyOrigin = Default]] )

Parameters

$oWorksheet Excel worksheet object
$vRange Range can be a range object, an A1 range (e.g. "A1:B2", "1:2" (row 1 to 2), ""D:G" (columns D to G) etc.
$iShift [optional] Specifies which way to shift the cells. Can be xlShiftToRight or xlShiftDown of the XlInsertShiftDirection enumeration.
    If set to keyword Default, Excel decides based on the shape of the range (default = keyword Default)
$iCopyOrigin [optional] Specifies which formatting option to copy. Can be any of the XlInsertFormatOrigin enumeration (default = keyword Default)

Return Value

Success: the range object containing the inserted cells, rows or columns
Failure: 0 and sets @error.
@error: 1 - $oWorksheet is not an object or not a worksheet object
2 - $vRange is invalid. @extended is set to the COM error code
3 - Error occurred when inserting empty cells. @extended is set to the COM error code

Remarks

The supplied range is inserted above or to the left of the supplied range, depending on the $iShift parameter.

Example

Example 1

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

; *****************************************************************************
; Insert a range with 3 rows and 2 columns before A1 on the active worksheet
; and shift the cells down.
; *****************************************************************************
_Excel_RangeInsert($oWorkbook.Activesheet, "A1:B3", $xlShiftDown)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeInsert Example 1", "Error inserting cells." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeInsert Example 1", "Range successfully inserted.")

Example 2

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

; *****************************************************************************
; Insert 3 columns before colum B on the active worksheet
; *****************************************************************************
_Excel_RangeInsert($oWorkbook.Activesheet, "B:D")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeInsert Example 2", "Error inserting columns." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeInsert Example 2", "3 columns successfully inserted before column B.")

Example 3

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

; *****************************************************************************
; Insert 2 rows before row 1 on worksheet 2
; *****************************************************************************
_Excel_RangeInsert($oWorkbook.Worksheets(2), "1:2")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeInsert Example 3", "Error inserting rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeInsert Example 3", "Rows successfully inserted on worksheet 2.")