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-
[Dropped] CopyPasteSort Issues with Macro
[Dropped] CopyPasteSort Issues with Macro
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
Reason: Dropped: No response from Gonzo714 -- MrProgrammer, forum moderator
LibreOffice 7.5, Debian 12
Re: CopyPasteSort Issues with Macro
Should I be impressed that I stumped the Support Team???
LibreOffice 7.5, Debian 12
-
- Volunteer
- Posts: 1548
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: CopyPasteSort Issues with Macro
Looking the recorded macro I believe your problem is with the line
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
the paste succeeds.
Code: Select all
dispatcher.executeDispatch(document, ".uno:PasteOnlyText", "", 0, Array())
When I change that line to
Code: Select all
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
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
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: CopyPasteSort Issues with Macro
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
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
-
- Volunteer
- Posts: 1548
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: CopyPasteSort Issues with Macro
I don't understand. What formula?But Paste Text Only is the requirement needed since using simply Paste will trospose the cell 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
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: CopyPasteSort Issues with Macro
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