Insert row into Excel

[Montfrooij]

Hello, as part of a larger script I am trying to insert row(s) into a Excel workbook.

I know of the function _ExcelInsertRow(), but I am confused why my original script did give a syntax error and still worked.

Here is a simplified example.

#include <Excel.au3>

$oExcel = _ExcelBookNew() ;Open new workbook

;Write some data
_ExcelWriteCell($oExcel,1,"A1")
_ExcelWriteCell($oExcel,2,"A2")
_ExcelWriteCell($oExcel,3,"A3")

For $i = 1 to 10 ;Insert 10 rows
$oExcel.Rows("1:1").Select
$oExcel.Selection.Copy
$oExcel.Rows("2:2").Select
$oExcel.Selection.Insert 'Shift:=xlDown'

Next

As you will see, it gives a syntax error on th 'shift...' part, but if I press 'continue anyway', it does work perfectly inserting 10 rows.

For now _ExcelRowInsert() will work, but in the nearby future I will probably want to use similar pieces of code so my question is:

How can I prevent this syntax error?

Thanks in advance.

Montfrooij

[reb]

Hello, as part of a larger script I am trying to insert row(s) into a Excel workbook.

I know of the function _ExcelInsertRow(), but I am confused why my original script did give a syntax error and still worked.

Here is a simplified example.

#include <Excel.au3>

$oExcel = _ExcelBookNew() ;Open new workbook

;Write some data
_ExcelWriteCell($oExcel,1,"A1")
_ExcelWriteCell($oExcel,2,"A2")
_ExcelWriteCell($oExcel,3,"A3")

For $i = 1 to 10 ;Insert 10 rows
$oExcel.Rows("1:1").Select
$oExcel.Selection.Copy
$oExcel.Rows("2:2").Select
$oExcel.Selection.EntireRow.Insert    ;<------ use this instead  Not tested   REB
Next

As you will see, it gives a syntax error on th 'shift...' part, but if I press 'continue anyway', it does work perfectly inserting 10 rows.

For now _ExcelRowInsert() will work, but in the nearby future I will probably want to use similar pieces of code so my question is:

How can I prevent this syntax error?

Thanks in advance.

Montfrooij

[Juvigy]

This works:

#include <Excel.au3>

$oExcel = _ExcelBookNew() ;Open new workbook

;Write some data
_ExcelWriteCell($oExcel,1,"A1")
_ExcelWriteCell($oExcel,2,"A2")
_ExcelWriteCell($oExcel,3,"A3")

For $i = 1 to 10 ;Insert 10 rows
$oExcel.Rows("1:1").Select
$oExcel.Selection.Copy
$oExcel.Rows("2:2").Select
$oExcel.Selection.Insert(-4121)
Next

-4121 is the numerical value of xlDown.

[Montfrooij]

-4121 is the numerical value of xlDown.

Ok, that is handy to know.

I never knew about Excel constants. (never had a use for them also, until now)

Thanks a lot!

Montfrooij