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.
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).
Correction: my initial info should have said "Right from ooo to lo 5.0.4 this has worked"
Have just tried the 5.0.5 rc and it handles the macro correctly. No issues.
Hello [user's name], Thank you for filing the bug. Please send us a sample document, as this makes it easier for us to verify the bug. I have set the bug's status to 'NEEDINFO', so please do change it back to 'UNCONFIRMED' once you have attached a document. (Please note that the attachment will be public, remove any sensitive information before attaching it.) How can I eliminate confidential data from a sample document? https://wiki.documentfoundation.org/QA/FAQ#How_can_I_eliminate_confidential_data_from_a_sample_document.3F Thank you
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.
Created attachment 122224 [details] Same macro as before
> Created attachment 122224 [details] > Same macro as before
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.
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.
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 f97beeef352fdd9fd4f157a7a4c4f6af8341cbe6 source 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]
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.
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 };
*** Bug 98384 has been marked as a duplicate of this bug. ***
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
*** Bug 98368 has been marked as a duplicate of this bug. ***
Hmm looks like pherworth isn't really working on this bug, so resetting the "Assigned To" field.
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.
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 };
*** Bug 100846 has been marked as a duplicate of this bug. ***
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
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.
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.
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.
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.
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.
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?