CopyPasteSort Issues with Macro

Creating a macro - Writing a Script - Using the API

CopyPasteSort Issues with Macro

Postby Gonzo714 » Sat Mar 25, 2017 8:00 pm

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-
LibreOffice 5.0.6.3 on open Suse 13.2
Gonzo714
 
Posts: 15
Joined: Wed Nov 23, 2016 2:52 pm

Re: CopyPasteSort Issues with Macro

Postby Gonzo714 » Sun Apr 02, 2017 6:06 pm

Should I be impressed that I stumped the Support Team???
LibreOffice 5.0.6.3 on open Suse 13.2
Gonzo714
 
Posts: 15
Joined: Wed Nov 23, 2016 2:52 pm

Re: CopyPasteSort Issues with Macro

Postby UnklDonald418 » Tue Apr 04, 2017 4:34 pm

Looking the recorded macro I believe your problem is with the line
Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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 subject line
Apache OpenOffice 4.1.4 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 580
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: CopyPasteSort Issues with Macro

Postby Gonzo714 » Wed Apr 05, 2017 12:25 am

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 5.0.6.3 on open Suse 13.2
Gonzo714
 
Posts: 15
Joined: Wed Nov 23, 2016 2:52 pm

Re: CopyPasteSort Issues with Macro

Postby UnklDonald418 » Wed Apr 05, 2017 5:49 am

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 subject line
Apache OpenOffice 4.1.4 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 580
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: CopyPasteSort Issues with Macro

Postby ThierryT » Thu Apr 06, 2017 11:00 am

Try this macro :
Code: Select all   Expand viewCollapse view
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
ThierryT
 
Posts: 11
Joined: Fri May 13, 2016 8:52 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: Yahoo [Bot] and 12 guests