Sign in to follow this  
Followers 0
Montfrooij

Insert row into Excel

4 posts in this topic

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

Share this post


Link to post
Share on other sites



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


MEASURE TWICE - CUT ONCE

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

-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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0