How to copy ranges with filtered rows? (LibreOffice)

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
cvogelbusch
Posts: 3
Joined: Thu Apr 12, 2012 4:06 pm

How to copy ranges with filtered rows? (LibreOffice)

Post by cvogelbusch »

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
cvogelbusch
Posts: 3
Joined: Thu Apr 12, 2012 4:06 pm

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

Post by cvogelbusch »

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

	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

	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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply