Apache OpenOffice Community Forum

Posted: **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-

Posted: **Sun Apr 02, 2017 6:06 pm**

Should I be impressed that I stumped the Support Team???

Posted: **Tue Apr 04, 2017 4:34 pm**

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.

Posted: **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

Posted: **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?

Posted: **Thu Apr 06, 2017 11:00 am**

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

Apache OpenOffice Community Forum

[Dropped] CopyPasteSort Issues with Macro

[Dropped] CopyPasteSort Issues with Macro

Re: CopyPasteSort Issues with Macro

Re: CopyPasteSort Issues with Macro

Re: CopyPasteSort Issues with Macro

Re: CopyPasteSort Issues with Macro

Re: CopyPasteSort Issues with Macro