LibO Calc: Paste-special macro - wrong result...

[2CV67oo]

I have numerous Calc files where I regularly update data by copy/pasting the last line (of data & derived results) by "Copy - Paste Special - Shift cells - Down" before updating a few cells.

Tiring of the repetition, I thought this was a good candidate for recording a tiny Macro & adding a toolbar button to launch it from any Calc file.

This appeared to work OK, but then I noticed errors in the results.

Boiling it down to a simple example:
If I have cell B2=A2-A1
then copy/pasting line 2 manually gives me B3=A3-A2 (required result).
but running the macro gives me B3=A3-A1...

I didn't find any explanation or fix for this yet.

Any advice welcome!

Details :
LibreOffice 3.4.4. in Lubuntu11.10

Method:
1. Select line 2
2. Record macro
3. Ctrl+C
4. Select line 3
5. Right click
6. Paste spacial...
7. Shift cells - Down (Selection - Paste all is already ticked)
8. OK
9. Stop macro

The macro as recorded:

Code: Select all

sub mac3
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args2(5) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Flags"
args2(0).Value = "A"
args2(1).Name = "FormulaCommand"
args2(1).Value = 0
args2(2).Name = "SkipEmptyCells"
args2(2).Value = false
args2(3).Name = "Transpose"
args2(3).Value = false
args2(4).Name = "AsLink"
args2(4).Value = false
args2(5).Name = "MoveMode"
args2(5).Value = 0

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args2())


end sub

[JohnSUN-Pensioner]

Try this version of your macro (without DispatchHelper):

Code: Select all

Sub CopyLastRowDown
Dim oCurrentController As Variant	' Controller of Current Workbook
Dim oActiveSheet As Variant			' Current sheet
Dim oCurs As Variant				' Cursor for find last row
Dim aRangeAddress As New com.sun.star.table.CellRangeAddress
Dim aCellAddress As New com.sun.star.table.CellAddress
	oCurrentController = ThisComponent.getCurrentController()
	oActiveSheet = oCurrentController.getActiveSheet()
	oCurs = oActiveSheet.createCursor()
	oCurs.gotoEndOfUsedArea(True)
	aRangeAddress = oCurs.getRangeAddress()
	aCellAddress.Column = 0
	aCellAddress.Row = aRangeAddress.EndRow + 1
	aCellAddress.Sheet = aRangeAddress.Sheet
	aRangeAddress.StartRow = aRangeAddress.EndRow
	oActiveSheet.copyRange(aCellAddress, aRangeAddress)
End Sub

[2CV67oo]

Thanks, JohnSUN-Pensioner, for your custom macro!

It certainly copies down the last line & keeps the correct cell references.

It does not actually do what I need though, which maybe I did not explain clearly enough.
I use the "Paste-special, with Shift-cells-Down" method because I have charts below my lines of data & these charts need to shift down as new lines of data are added.
Your custom macro leaves my charts in the same place, so they will soon interfere with the data.
I suppose one answer would be to insert a blank line at the end of each run.

Actually, I am almost more interested in finding out how/why my recorded macro produces the "wrong" result & whether it can be tweaked to do what I want.

I get into macros about once a year, so forget everything I ever learned & need to start again every time.
I just downloaded Andrew Pitonyak's "OpenOffice.org Macros Explained", but as it is 514 pages it seems not an efficient way to get rid of 7 mouse clicks...
Is there a simpler way to get started?

[2CV67oo]

Actually, I just tried recording a macro without paste-special & that works OK...

Method:
1. Select line 2
2. Record macro
3. Ctrl+C
4. Down
5. Ctrl+V
6. Down..
7. Insert > Rows
8. Stop macro

This is the recorded macro & the cell references are OK:

Code: Select all

sub cvi
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args2(1) as new com.sun.star.beans.PropertyValue
args2(0).Name = "By"
args2(0).Value = 1
args2(1).Name = "Sel"
args2(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args2())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())

rem ----------------------------------------------------------------------
dim args4(1) as new com.sun.star.beans.PropertyValue
args4(0).Name = "By"
args4(0).Value = 1
args4(1).Name = "Sel"
args4(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args4())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:InsertRows", "", 0, Array())


end sub

Why?

[Villeroy]

You may have noticed that the LibreOffice team disabled the macro recorder by default and that you have to enable it as an "experimental feature". You may play with it and get some results that happen to be the wanted results in one case and which may destroy your document in some other case when the document does not meet unknown assumption you made. There should be no macro recorder at all. I doubt that the keystrokes and clicks saved by this feature comes even close to the keystrokes and clicks it had caused in this forum alone.