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

Creating a macro - Writing a Script - Using the API

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

Postby 2CV67oo » Mon Apr 23, 2012 1:39 pm

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   Expand viewCollapse view
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
LibreOffice4.4.2.2 on Ubuntu 15.04.1 & LibreOffice ??? on W8.1 & LibreOffice 3.3.4 on Win7
2CV67oo
 
Posts: 55
Joined: Sat Nov 27, 2010 11:12 pm
Location: Alsace

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

Postby JohnSUN-Pensioner » Mon Apr 23, 2012 2:10 pm

Try this version of your macro (without DispatchHelper):
Code: Select all   Expand viewCollapse view
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
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.4, LibreOffice 5.4.2.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 761
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

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

Postby 2CV67oo » Tue Apr 24, 2012 11:21 am

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?
LibreOffice4.4.2.2 on Ubuntu 15.04.1 & LibreOffice ??? on W8.1 & LibreOffice 3.3.4 on Win7
2CV67oo
 
Posts: 55
Joined: Sat Nov 27, 2010 11:12 pm
Location: Alsace

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

Postby 2CV67oo » Tue Apr 24, 2012 11:34 am

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   Expand viewCollapse view
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?
LibreOffice4.4.2.2 on Ubuntu 15.04.1 & LibreOffice ??? on W8.1 & LibreOffice 3.3.4 on Win7
2CV67oo
 
Posts: 55
Joined: Sat Nov 27, 2010 11:12 pm
Location: Alsace

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

Postby Villeroy » Tue Apr 24, 2012 12:03 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26255
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 5 guests