Jump to content

Excel Inserting Row(s)


JohnOne
 Share

Go to solution Solved by water,

Recommended Posts

Needing to Insert some rows into an excel doc.

Went to the help file and found _Excel_RangeInsert and Example 3

The example works fine, but when I try to modify it, to insert 2 rows before row 2 in active worksheet, it only inserts 1 row.

Can anyone see what I'm doing wrong.

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object and open an example workbook
Local $oAppl = _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($oAppl, @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($oAppl)
    Exit
EndIf
Sleep(1000)
; *****************************************************************************
; Insert 2 rows before row 2 on Active sheet
; *****************************************************************************
_Excel_RangeInsert($oWorkbook.ActiveSheet, "2:2") ; "Before row, number of rows to insert "
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.")

Actual goal is to insert rows at end of sheet, but if I cannot understand this I've no chance of other.

EDIT:

If I change the money line to...

_Excel_RangeInsert($oWorkbook.ActiveSheet, "2:3")

Then it inserts 2 rows instead of 3.

EDIT2:

I think I've established that those numbers ("2:2") I thought meant "Before row number, number of rows to insert" do not mean that at all, after trying to insert rows at end with this...

$insert =  String($oWorkbook.ActiveSheet.UsedRange.Rows.Count + 1) & ":2"
_Excel_RangeInsert($oWorkbook.ActiveSheet, $insert)
Which inserted 2000 rows into the sheet.
 
I know one thing for sure... I'm doing it wrong.
 
EDIT3:
 
Solution:
 
Problem was, I was using the completely wrong function (_Excel_RangeInsert)
Where I should have been using _Excel_RangeWrite.
Edited by JohnOne

AutoIt Absolute Beginners    Require a serial    Pause Script    Video Tutorials by Morthawt   ipify 

Monkey's are, like, natures humans.

Link to comment
Share on other sites

Hello John,

I tried to execute the script you shared.

The problem is in this line

_Excel_RangeInsert($oWorkbook.ActiveSheet, "2:2") ; "Before row, number of rows to insert "

In the above line 2:2 indicates, adding of rows from 2nd row to 2nd row. That is the reason it inserts only one row.

If you want to add two rows this is how it should be

_Excel_RangeInsert($oWorkbook.ActiveSheet, "2:3") ; "Before row, number of rows to insert "

Good luck :)

Link to comment
Share on other sites

At least I think I got it, I'm trying to insert 2 rows at end of used rows in sheet.

But this inserts 0 rows...

$insert =  String($oWorkbook.ActiveSheet.UsedRange.Rows.Count + 1) & ":" & String($oWorkbook.ActiveSheet.UsedRange.Rows.Count + 3)
_Excel_RangeInsert($oWorkbook.ActiveSheet(), $insert)

And this inserts 3 rows.

$insert =  String($oWorkbook.ActiveSheet.UsedRange.Rows.Count) & ":" & String($oWorkbook.ActiveSheet.UsedRange.Rows.Count + 2)
_Excel_RangeInsert($oWorkbook.ActiveSheet(), $insert)

So it looks like I'm still not getting it.

Edited by JohnOne

AutoIt Absolute Beginners    Require a serial    Pause Script    Video Tutorials by Morthawt   ipify 

Monkey's are, like, natures humans.

Link to comment
Share on other sites

Hi

As santhoshkumargr stated, the "2:2" is specified as a ExcelRange

So for your goal to insert rows at the end of sheet, you have to get where the sheet actually ends. Therefore you can use something like this:

Local $aResult = _Excel_RangeRead($oWorkbook, Default,$oWorkbook.ActiveSheet.Usedrange.Columns("A:A"))
$iCells = UBound($aResult)-1
MsgBox(0,"UsedRange in Column A",$iCells)

So you know where your sheet actually ends and then you can insert rows with something like that:

$iRows = 3 ;Number of Rows to Insert
_Excel_RangeInsert($oWorkbook.ActiveSheet, $iCells & ":" & $iCells+$iRows)





Edited by draien
Link to comment
Share on other sites

  • Solution

OK, I think I need to enhance documentation how _Excel_RangeInsert works.

Then the bugs in the example scripts need to removed.

@JohnOne

If you want to add data to the end of the worksheet use

$oWorkbook.ActiveSheet.UsedRange.Rows.Count

add 1 and you have the row where you can write data using _Excel_RangeWrite.

This is true as long as the usedrange starts in row 1.

For details and examples please check the wiki.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

I mentioned before water, I tried that.

Here is current code...

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object and open an example workbook
Local $oAppl = _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($oAppl, @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($oAppl)
    Exit
EndIf

$NumRowsToInsert = 2

$rowcount = $oWorkbook.ActiveSheet().UsedRange.Rows.Count
ConsoleWrite("Start with " & $rowcount & " rows" & @LF)
$rowcount += 1 ; want to add rows below that
$rowcountplus = $rowcount + $NumRowsToInsert

ConsoleWrite("So I insert " & $NumRowsToInsert & " rows from " & $rowcount & " to " & $rowcountplus & @LF)

$insert =  String($rowcount) & ":" & String($rowcountplus)

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

$NewRowCount = $oWorkbook.ActiveSheet.UsedRange.Rows.Count
ConsoleWrite("Now I have " & $NewRowCount & " rows" & @LF)

MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeInsert Example 3", "Rows successfully inserted on worksheet 2.") 

Here is the output from console..

Start with 2000 rows
So I insert 2 rows from 2001 to 2003
Now I have 2000 rows
Edited by JohnOne

AutoIt Absolute Beginners    Require a serial    Pause Script    Video Tutorials by Morthawt   ipify 

Monkey's are, like, natures humans.

Link to comment
Share on other sites

As draien said inserting rows after the last used row doesn't make sense.

As I said in post #7 use _Excel_RangeWrite.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Example

#include <Excel.au3>
Global $aStart = [["Row1", "Col1"], ["Row2", "Col1"], ["Row3", "Col1"]]
Global $aAppend = [["Append1", "Col1"], ["Append2", "Col1"], ["Append3", "Col1"]]
Global $oExcel = _Excel_Open()
Global $oWorkBook = _Excel_BookNew($oExcel)
_Excel_RangeWrite($oWorkBook, Default, $aStart, "A1")
Global $iLastUsedRow = $oWorkBook.ActiveSheet.UsedRange.Rows.Count
_Excel_RangeWrite($oWorkBook, Default, $aAppend, "A" & $iLastUsedRow + 1)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

OK, I think I need to enhance documentation how _Excel_RangeInsert works.

I will insert the following text in the help file:

"The specified range is inserted above or left of the top left cell of the specified range depending on the value of $iShift."

Does this explain how the function works? Can someone translate it to better english? Or even make it clearer?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Here's my offering.

"The supplied range is inserted above, or to the left of the target range by default, and can be overridden using $iShift parameter, to below  or to right of target range.
 
The desired position in work sheet must be within the bounds of used range cells."
Edited by JohnOne

AutoIt Absolute Beginners    Require a serial    Pause Script    Video Tutorials by Morthawt   ipify 

Monkey's are, like, natures humans.

Link to comment
Share on other sites

You can only insert the range above or to the left based on the $iShift parameter. Inserting outside the bounds of the used range is possible but doesn't make sense.

So I suggest:

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

You can insert outside the used range. The function does not return an error.

It just doesn't make sense to insert nothing into nothing and move nothing to the right or downwards.

So _Excel_RangeWrite was the function to solve your problem.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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
 Share

×
×
  • Create New...