How to copy ranges with filtered rows? (LibreOffice)

Creating a macro - Writing a Script - Using the API

How to copy ranges with filtered rows? (LibreOffice)

Postby cvogelbusch » Mon Jan 29, 2018 12:12 pm

Hi,

I hopp you guys are ok I ask this question here (since I am not sure if basic 100% OpenOffice = LibreOffice).

I am trying top copy information from a spreadsheet into a text document.
The dispatch way did not work so I tried "getTransferable" wich works fine, BUT when part of the selected range is filtered, it fully fails.

How can I copy an area that includes filtered rows?
I am happy for any hints as I am seriously stuck!

All the best
Christoph
NeoOffice 3.2.1 Mac
cvogelbusch
 
Posts: 3
Joined: Thu Apr 12, 2012 4:06 pm

Re: How to copy ranges with filtered rows? (LibreOffice)

Postby Villeroy » Mon Jan 29, 2018 1:40 pm

Recent versions of OpenOffice and LibreOffice support a normal copy/paste operation on a filtered range if it is really filtered, not with hidden rows.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26248
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to copy ranges with filtered rows? (LibreOffice)

Postby Villeroy » Tue Jan 30, 2018 12:55 pm

In order to get the filtered cells into a Writer document you only need to copy to another sheet and then call your macro on the resulting range. The first step can be automated with a recorded macro.
A database could do that easily without any macro code.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26248
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to copy ranges with filtered rows? (LibreOffice)

Postby cvogelbusch » Tue Jan 30, 2018 1:27 pm

I updated LibreOffice to 5.4.4.2 with the same result:
1. using UNO does nothing (on my Mac) even if the marked area is not filtered I keep the previous clipboard – cmd+c works
Code: Select all   Expand viewCollapse view
   document = ThisComponent.CurrentController.Frame
   dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
   dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

2. using transferable works if NOT filtered but fails if filtered
Code: Select all   Expand viewCollapse view
   copyContent = ThisComponent.CurrentController.getTransferable()
   oClip = CreateUnoService("com.sun.star.datatransfer.clipboard.SystemClipboard")
   oClip.setContents(copyContent,Null)


I will implement a message that copies the full sheet (hopefully this works without formulas) then I will delete all invisible columns and rows and then I will copy this content using transferable :-)

Since some macros to a few dozen copies this will be slow so I will first try if transferable fail and then do the slow process, but better than nothing :-(

Any suggestions? (I am a self taught OO Basic user – worse it's just learning by doing – so I take any advice I can get :-)

All the best
Christoph
NeoOffice 3.2.1 Mac
cvogelbusch
 
Posts: 3
Joined: Thu Apr 12, 2012 4:06 pm

Re: How to copy ranges with filtered rows? (LibreOffice)

Postby Villeroy » Tue Jan 30, 2018 2:07 pm

Ctrl+C (copy filtered area)
Ctrl+N (new document)
Ctrl+Shift+V or Insert key (paste special)
Choose whatever content you want to be pasted.
-- until here you can record some dispatch macro in the global scope (stored under "My Macros"). But keep in mind that the frame changes and with another frame you need another dispatcher. However, this is highly unproductive since you don't do that every minute, possibly not even once a day. Just do it manually with a few keystrokes.
Then call your macro to transfer the current selection to a Writer table.
-------
Again, this is what databases are made for. In this case you may even connect a Base document to your spreadsheet so the spreadsheet is treated like a database. Then do the filtering and sorting in the database and use the filtered and sorted results with reports.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26248
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 3 guests