[Solved] The problem of copy filtered data and paste

Creating a macro - Writing a Script - Using the API

[Solved] The problem of copy filtered data and paste

Postby 026538 » Wed Jan 16, 2013 5:31 pm

i now use the following codes to copy the filtered data to another place,

Code: Select all   Expand viewCollapse view
   copyrange = oExportsh.getcellrangebyposition(0,0,16,lastrow+1)
            sourcedataarray = copyrange.getdataarray
            
            'define the paste area of destination
            newblankdoc = StarDesktop.loadComponentFromURL("private:factory/scalc","_Blank",0,Array())
            newblankdocsh = newblankdoc.sheets(0)
            newblankdocshrng = newblankdocsh.getcellrangebyposition(0,0,16,lastrow+1)
            
            'paste
            newblankdocshrng.setdataarray(sourcedataarray)
            


now the problem is that: after execution, i found actually the hidden rows are all copied,

when i press ctrl + a to select all, and then ctrl + c to copy, then ctrl + v to paste to another new sheet, it will only copy the filtered data(visible), how to do exactly like this in macro ?
Last edited by 026538 on Fri Jan 18, 2013 10:44 am, edited 1 time in total.
OpenOffice 3.1 on Windows Vista / NeoOffice 2.2.3 with MacOS 10.4 / OpenOffice 2.4 on Ubuntu 9.04
026538
 
Posts: 52
Joined: Wed Dec 12, 2012 5:59 am

Re: the problem of copy filtered data and paste

Postby Villeroy » Wed Jan 16, 2013 5:42 pm

1) Record a macro for one document and see how you apply the recorded dispatches to your scenario with 2 documents.
2) Use a copying filter and export the resulting sheet to another document.
3) What you obviously try to do could be done in a database without writing a single line of silly Basic code.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27207
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: The problem of copy filtered data and paste

Postby 026538 » Thu Jan 17, 2013 3:25 am

Thanks,

1, i completed the following codes, it is ok,

Code: Select all   Expand viewCollapse view
Sub DataFromAnotherFile

dim doc as object
dim docnew as object
dim dispatcher as object

doc = thiscomponent.currentcontroller.frame

dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

dispatcher.executeDispatch(doc, ".uno:SelectAll", "", 0, Array())

dispatcher.executeDispatch(doc, ".uno:Copy", "", 0, Array())

OldDoc = StarDesktop.loadComponentFromURL("private:factory/scalc","_Blank",0,Array()) 'open it

docnew = OldDoc.currentcontroller.frame

dispatcher.executeDispatch(docnew, ".uno:Paste", "", 0, Array())

End Sub


For this method, i have a question, if i want to paste to sheets(1), not default to sheets(0), how to change the code ?

2, could you help to explain it a little more ? how to copy filtering and export the result sheet to other document ?

i try the output function in filter, and can not assign the destination in other document,
OpenOffice 3.1 on Windows Vista / NeoOffice 2.2.3 with MacOS 10.4 / OpenOffice 2.4 on Ubuntu 9.04
026538
 
Posts: 52
Joined: Wed Dec 12, 2012 5:59 am

Re: The problem of copy filtered data and paste

Postby Villeroy » Thu Jan 17, 2013 4:43 pm

You already know how to make a filter descriptor with CopyOutput option. You copy the filter output to another sheet, get the used area's DataArray, create a new document and dump the DataArray.

Getting used to the used range:
viewtopic.php?f=45&t=58516&p=258433&hilit=getusedrange#p258433
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27207
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: The problem of copy filtered data and paste

Postby 026538 » Fri Jan 18, 2013 10:11 am

thanks a lot,

i do not used usedrange before, then it is very slow for function setdataarray for large files,

after i change to usedrange, it is ok now,
OpenOffice 3.1 on Windows Vista / NeoOffice 2.2.3 with MacOS 10.4 / OpenOffice 2.4 on Ubuntu 9.04
026538
 
Posts: 52
Joined: Wed Dec 12, 2012 5:59 am


Return to Macros and UNO API

Who is online

Users browsing this forum: RPG and 9 guests