Bug Hunting Session
Bug 97193 - macro to copy/paste using dispatchhelper not working correctly(or as it did previously)
Summary: macro to copy/paste using dispatchhelper not working correctly(or as it did p...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.1.0.3 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:5.3.0 target:5.1.6 target:5.2.1
Keywords: bibisected, bisected, regression
: 98368 98384 100846 (view as bug list)
Depends on:
Blocks:
 
Reported: 2016-01-16 18:45 UTC by pherworth
Modified: 2016-10-25 19:09 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
a portion of a longer macro that actually works the copy/paste (950 bytes, text/plain)
2016-01-16 18:45 UTC, pherworth
Details
Same macro as before (17.48 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-01-26 22:08 UTC, pherworth
Details

Note You need to log in before you can comment on or make changes to this bug.
Description pherworth 2016-01-16 18:45:34 UTC
Created attachment 122009 [details]
a portion of a longer macro that actually works the copy/paste

I have a macro in basic to copy data (inc some formulae) which changes each month in one sheet to the end of another sheet in the same document.
I attach the appropriate portion of the code.
Variables are all set up outside this portion correctly and the 'findend' is my own function to return the number of the first availble empty cell in col A. This also still works correctly. I have 2 MsgBox entries - the first is a safety measure in case the wrong button is clicked - the second I just inserted to check that the correct range for paste is being selected (and it is).
Right from old ooo to lo 5.0.2 this has worked.
What happens now is that the "uno.insertcontents" instructions appends the data exactly where it should be (say, A300:E316) but then moves all the data above this to the right by the 5 columns (ie F1:M299). This should obviously all remain as A1:G299. (There are 3 extra columns not applying to the new data yet).
Other code I have eg to insert a row of new data using the "findend" function works as it should.
I assume it's either the "Frame" or the actual "uno.insert..." going wrong.
Comment 1 pherworth 2016-01-16 18:52:24 UTC
I should perhaps add that I have tried the "copyrange" method. This does work correctly in terms of the pasting but as you know, doesn't do the paste special so I get unformatted content (formula instead of date, for example).
Comment 2 pherworth 2016-01-18 15:49:08 UTC
Correction: my initial info should have said "Right from ooo to lo 5.0.4 this has worked"
Comment 3 pherworth 2016-01-20 20:56:39 UTC
Have just tried the 5.0.5 rc and it handles the macro correctly. No issues.
Comment 4 raal 2016-01-25 21:43:42 UTC Comment hidden (obsolete)
Comment 5 pherworth 2016-01-25 22:14:18 UTC
The Doc is for various financial arrangements inc bank details. The macro is an integrated part of the whole so it would be difficult to show the way it works without including detail that I wouldn't want anyone to see.
I will try to write a new document with anonymous info using the same macros and send that to you.
Comment 6 pherworth 2016-01-26 22:08:54 UTC
Created attachment 122224 [details]
Same macro as before
Comment 7 pherworth 2016-01-26 22:09:48 UTC
> Created attachment 122224 [details]
> Same macro as before
Comment 8 pherworth 2016-01-26 22:12:05 UTC
Sorry about duplicate. I'm not sure what I did.
Click button on Sheet1 of new attachment.
It works in 5.0.x and before - not in 5.1.
Comment 9 pherworth 2016-02-10 18:10:58 UTC
Since my original reports I've been trying to find the problem here.
I think I've fixed it.
By adding and extra argument:
NoArg1(6).Name = "ShiftCells"
Noarg1(6).Value = false

I've added this to the list of args within the macro and this seems to have solved it.
Comment 10 raal 2016-02-11 12:19:23 UTC
This seems to have begun at the below commit.
Adding Cc: to Philippe Jung; Could you possibly take a look at this one? Thanks

8ef74cbc022169ee0eee0984a9e914a26418fdbe is the first bad commit
commit 8ef74cbc022169ee0eee0984a9e914a26418fdbe
Author: Norbert Thiebaud <nthiebaud@gmail.com>
Date:   Sun Jul 12 03:49:46 2015 -0700

    source sha:f97beeef352fdd9fd4f157a7a4c4f6af8341cbe6

    source sha:f97beeef352fdd9fd4f157a7a4c4f6af8341cbe6

:040000 040000 984c2daa77628b48a35edb559040a42886590a61 f8375feeb16d6214d2f2b27eac142e1c9b9f085a M      instdir

author	Philippe Jung <phil.jung@free.fr>	2015-05-20 14:54:27 (GMT)
committer	Yousuf Philips <philipz85@hotmail.com>	2015-05-31 17:58:21 (GMT)
commit	f97beeef352fdd9fd4f157a7a4c4f6af8341cbe6 (patch)
Calc: Insert row/col before/after [1/2]
Comment 11 pherworth 2016-02-11 18:24:12 UTC
I assume that previously Shift Cells default was false and is now true. If I raise the Paste Special dialogue box it is set to false so it must be some background issue.
Comment 12 Bernard Marcelly 2016-02-21 16:18:41 UTC
According to this forum thread (french)
http://www.developpez.net/forums/d1568843/logiciels/logiciels-libres-open-source/bureautique/openoffice-libreoffice/compatibilite-macros-open-office-libre-office/

An enum of source file  sc\inc\global.hxx has different values from previous versions of LibreOffice.
Consequence : macros using Paste Special, recorded in previous versions, do not work correctly in 5.1.

Before version 5.1 :
enum InsCellCmd
    {
        INS_CELLSDOWN,
        INS_CELLSRIGHT,
        INS_INSROWS,
        INS_INSCOLS,
        INS_NONE
    };

From version 5.1 :
enum InsCellCmd
    {
        INS_CELLSDOWN,
        INS_CELLSRIGHT,
        INS_INSROWS_BEFORE,
        INS_INSROWS_AFTER,
        INS_INSCOLS_BEFORE,
        INS_INSCOLS_AFTER,
        INS_NONE
    };
Comment 13 Maxim Monastirsky 2016-03-06 09:41:22 UTC
*** Bug 98384 has been marked as a duplicate of this bug. ***
Comment 14 Gitsy 2016-03-06 10:26:09 UTC
I am also facing the same problem. 

For example, the following code in previous LO versions doesn't shift or move cells. Now it does

  args1(5).Name = "MoveMode"
    args1(5).Value = 4
    dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args1())

In it, the value of args1(5) should be changed as 6 to make it work as in previous versions.

The above is just an example. Another one is a macro with a variable's name as "round" results in error. Putting a variable's name as "round" in LO versions up to 5.0 doesn't results error.Now it does.

Many other Basic macros which were working in previous versions are currently not working or working strangely after upgrading to 5.1. 

Tested in both Ubuntu and Windows
Comment 15 Gitsy 2016-03-15 14:32:38 UTC
*** Bug 98368 has been marked as a duplicate of this bug. ***
Comment 16 Maxim Monastirsky 2016-03-23 10:45:19 UTC
Hmm looks like pherworth isn't really working on this bug, so resetting the "Assigned To" field.
Comment 17 pherworth 2016-03-23 13:13:43 UTC
I didn't know you were expecting me to. I reported it as a user and fixed it for myself but I wouldn't know what to do after that. 
There is a neater fix from Gitsy although the result is the same.
Anyway, I'm not sure it was a bug as much as a change in setup that wasn't published for users to be aware of.
Comment 18 fsoltrash 2016-03-24 09:19:01 UTC
Replacing the new enum with the following one would keep backward compatibility with old macros:

enum InsCellCmd
    {
        INS_CELLSDOWN,
        INS_CELLSRIGHT,
        INS_INSROWS_BEFORE,
        INS_INSCOLS_BEFORE,
        INS_NONE,
        INS_INSROWS_AFTER,
        INS_INSCOLS_AFTER
    };
Comment 19 Aron Budea 2016-07-23 02:54:05 UTC
*** Bug 100846 has been marked as a duplicate of this bug. ***
Comment 20 Aron Budea 2016-07-23 16:26:57 UTC Comment hidden (obsolete)
Comment 21 Walt Roberts 2016-08-02 15:23:31 UTC
It appears I have a similar problem.  I just upgraded to 5.1.4.2.  
I have a spreadsheet which has multiple tabs, one of which is a "Values" table.

The recorded macro I use does the following:

1.  Copies a column populated by the function getyahoo() (extension) to a "backup" column.
2.  Copies a date from Sheet1 which is a Now() function and places it in the header of that column.
3.  saves the updated spreadsheet.

In 5.0.5 this worked correctly.

In 5.1 it does what pherworth described.  The macro was pretty simple as I am not a macro programmer and had to be executed on the page it was programmed for, so it's not very sophisticated.  

In this case the goal on the page in question is to get a series of values from the web via the getyahoo() function and populate the sheet (8 of 10).

The sheet is designed as:
Col A:  look up key for function in Col B
Col B:  look up macro (extension macro getyahoo())
Col C:  backup column which is a copy of column B via paste special.
Cell C3:  copy of date found in Sheet 1 Cell G2 which contains NOW().

When the keystroke recorded macro is run on 5.0.5 this works as expected.

When the macro is run (unchanged) on 5.1.4.2 it moves everything over 2 columns and appears to do this:

Inserts 2 columns: D and E
insert in D:3 an unformated date number (copy of sheet 1 cell G2)
inserts in E4:E71 the expected copy from B4:B71
leaves unchanged the columns shifted right.

If the macro is run on the now errored sheet, it repeats the behavior.

Synopsis:
The macro using paste special appears to improperly incorporate an INSERT COLUMN prior to doing a paste special function.

Another thought:  while this is problematic for this specific application, having a function like this is probably useful for tracking historical data.

-------
following is the macro generated by 5.0.1 (the pertinent part, I think.  If there is need for the entire macro file, I'll post it.  This was generated by using the function in Tools>Macros>Record.
-------
ub UpdateValue
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 ----------------------------------------------------------------------
dim args1(1) as new com.sun.star.beans.PropertyValue
args1(0).Name = "By"
args1(0).Value = 1
args1(1).Name = "Sel"
args1(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoUp", "", 0, args1())

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:GoUp", "", 0, args2())

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

dispatcher.executeDispatch(document, ".uno:GoUp", "", 0, args3())

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:GoUp", "", 0, args4())

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

dispatcher.executeDispatch(document, ".uno:GoUp", "", 0, args5())

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

dispatcher.executeDispatch(document, ".uno:GoUp", "", 0, args6())

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

dispatcher.executeDispatch(document, ".uno:GoUp", "", 0, args7())

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

dispatcher.executeDispatch(document, ".uno:GoUp", "", 0, args8())

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

dispatcher.executeDispatch(document, ".uno:GoUp", "", 0, args9())

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

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

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

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

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

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

rem ----------------------------------------------------------------------
dim args13(0) as new com.sun.star.beans.PropertyValue
args13(0).Name = "ToPoint"
args13(0).Value = "$B$4:$B$66"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args13())

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

rem ----------------------------------------------------------------------
dim args15(0) as new com.sun.star.beans.PropertyValue
args15(0).Name = "ToPoint"
args15(0).Value = "$D$4:$D$66"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args15())

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

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


end sub
Comment 22 pherworth 2016-08-02 17:20:33 UTC
If you read comment #14 from Gitsy above you will see the neatest solution for your problem.
Change Args16(5).Value = 4 to 6.
This should fix your problem. My earlier solution, although it worked, wasn't quite as neat as this.
Comment 23 Commit Notification 2016-08-03 15:46:49 UTC
Caolán McNamara committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=7ec4a495291c7427bba5256bf2c54f1da999ea52

Resolves: tdf#97193 restore pre 5-1 values for INS_INSROWS and INS_INSCOLS

It will be available in 5.3.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 24 Caolán McNamara 2016-08-03 15:52:25 UTC
Lets try and put things back the way they were pre 5-1. Unfortunately this will cause the same problem the other way around on moving to versions with the revert in place. The macro recording thing is very "literal". Backports to 5-2 and 5-1 in gerrit.
Comment 25 Commit Notification 2016-08-03 17:09:54 UTC
Caolán McNamara committed a patch related to this issue.
It has been pushed to "libreoffice-5-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=715664eb09c460e7f00fe4d114b65bb34bb051a3&h=libreoffice-5-1

Resolves: tdf#97193 restore pre 5-1 values for INS_INSROWS and INS_INSCOLS

It will be available in 5.1.6.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 26 Commit Notification 2016-08-03 17:11:26 UTC
Caolán McNamara committed a patch related to this issue.
It has been pushed to "libreoffice-5-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=7d7386bd5476dfe761978684715d3008b12701dd&h=libreoffice-5-2

Resolves: tdf#97193 restore pre 5-1 values for INS_INSROWS and INS_INSCOLS

It will be available in 5.2.1.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 27 OrangeJoe 2016-10-25 18:47:13 UTC
I use 4.2.something and 5.1.4.2 on different machines. As a workaround, rather than change MoveMode from 4 to 6, I commented out the MoveMode entirely, and that seems to have worked on both versions, as if INS_NONE is the default. Hopefully it will continue working (for a while, at least) in fixed versions. Although, if the default is whatever is last in the enum, then it probably won't. Is there any way of using constants in the macro code, like args(5).value = INS_NONE?