[Dropped] CopyPasteSort Issues with Macro

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Gonzo714
Posts: 46
Joined: Wed Nov 23, 2016 2:52 pm

[Dropped] CopyPasteSort Issues with Macro

Post by Gonzo714 »

Gentlemen...

I am using LibreOffice 5.1.5.2 on OpenSuse 13.2

http://www.hickstele.com/stuff/MacroCopyPasteIssues.ods

You will see on Sheet1 there are check boxes that show/hide cell values
That info is transposed onto Sheet2 where it needs sorted.
Since sorting wont work with cells containing formulas, i need to copy-paste thier value from columkn A into column E to be sorted there.
The values from Sheet1 change daily, hence the need for a macro.
Column D contains the formula you kind folks created / passed on to me in an earlier request.

The Macro in question is listed under MacroCopyPasteIssues / Standard / Module1 / Sort

My trouble is this - the macro works wonderful the first time, but change any value from Sheet1, and the values of Sheet2 / columnA are not pasted properly into column E
If the procedure is done manually, (copy Sheets2 ColumnA, and paste-text into column E, then sort) it works fine time after time, even when values in Sheet1 are altered.

I have noticed that when attempted via the macro, you can look under Edit, to see the Copy & Paste options have beed grayed out. (WTF?)
My uneducated guess is, this is where the issue lies for repeated macro usage.

-Me-
Last edited by MrProgrammer on Thu Aug 17, 2023 4:19 pm, edited 1 time in total.
Reason: Dropped: No response from Gonzo714 -- MrProgrammer, forum moderator
LibreOffice 7.5, Debian 12
Gonzo714
Posts: 46
Joined: Wed Nov 23, 2016 2:52 pm

Re: CopyPasteSort Issues with Macro

Post by Gonzo714 »

Should I be impressed that I stumped the Support Team???
LibreOffice 7.5, Debian 12
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: CopyPasteSort Issues with Macro

Post by UnklDonald418 »

Looking the recorded macro I believe your problem is with the line

Code: Select all

dispatcher.executeDispatch(document, ".uno:PasteOnlyText", "", 0, Array())
The cell contents in column A are all text representations of numerical data, but in this context “OnlyText” appears to be interpreted to be [a-z] and [A-Z], so the paste fails.
When I change that line to

Code: Select all

dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
the paste succeeds.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Gonzo714
Posts: 46
Joined: Wed Nov 23, 2016 2:52 pm

Re: CopyPasteSort Issues with Macro

Post by Gonzo714 »

True.
But Paste Text Only is the requirement needed since using simply Paste will trospose the cell formula, Cell Value is what's needed.
Is there a better method for Pasting Cell Value, because LibreOffice Calc will not give the option of PasteOnlyValue
When PasteOnlyValue is added manually, the macro isnt fond of this change
LibreOffice 7.5, Debian 12
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: CopyPasteSort Issues with Macro

Post by UnklDonald418 »

But Paste Text Only is the requirement needed since using simply Paste will trospose the cell formula
I don't understand. What formula?
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
ThierryT
Posts: 11
Joined: Fri May 13, 2016 8:52 pm

Re: CopyPasteSort Issues with Macro

Post by ThierryT »

Try this macro :

Code: Select all

sub Sort
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")

dim Feuille as object
dim Source as object
dim Dest as object

Feuille = thiscomponent.sheets.getByName("Sheet2")
Source = Feuille.getCellRangeByName("A1:A20")
Dest = Feuille.getCellRangeByName("E1:E20")

Dest.DataArray = Source.DataArray

rem ----------------------------------------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "ToPoint"
args5(0).Value = "$E$1:$E$20"

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

rem ----------------------------------------------------------------------
dim args6(7) as new com.sun.star.beans.PropertyValue
args6(0).Name = "ByRows"
args6(0).Value = true
args6(1).Name = "HasHeader"
args6(1).Value = false
args6(2).Name = "CaseSensitive"
args6(2).Value = false
args6(3).Name = "NaturalSort"
args6(3).Value = false
args6(4).Name = "IncludeAttribs"
args6(4).Value = true
args6(5).Name = "UserDefIndex"
args6(5).Value = 0
args6(6).Name = "Col1"
args6(6).Value = 5
args6(7).Name = "Ascending1"
args6(7).Value = true

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

end sub
LibreOffice 5.3.2.1 x 64 and AOO 4.1.3 Windows x64
Post Reply