OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Write a macro to quickly copy and paste cells

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
sortega0847
General User
General User


Joined: 12 May 2009
Posts: 7
Location: Yakima Washington USA

PostPosted: Mon May 18, 2009 4:33 pm    Post subject: Write a macro to quickly copy and paste cells Reply with quote

I just want to write a macro that copies and pastes cell from one sheet to another. WHen I record a macro however, there is a lot of reduncacy: for example:

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

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "Nr"
args3(0).Value = 3

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args3())

rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ToPoint"
args4(0).Value = "b30000"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())

rem ----------------------------------------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "ToPoint"
args5(0).Value = "$B$30000"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args5())

rem ----------------------------------------------------------------------
dim args6(5) as new com.sun.star.beans.PropertyValue
args6(0).Name = "Flags"
args6(0).Value = "SVD"
args6(1).Name = "FormulaCommand"
args6(1).Value = 0
args6(2).Name = "SkipEmptyCells"
args6(2).Value = false
args6(3).Name = "Transpose"
args6(3).Value = false
args6(4).Name = "AsLink"
args6(4).Value = false
args6(5).Name = "MoveMode"
args6(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args6())

rem ----------------------------------------------------------------------
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = "ToPoint"
args7(0).Value = "$C$30000"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args7())

rem ----------------------------------------------------------------------
dim args8(0) as new com.sun.star.beans.PropertyValue
args8(0).Name = "Nr"
args8(0).Value = 2

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args8())

rem ----------------------------------------------------------------------
dim args9(0) as new com.sun.star.beans.PropertyValue
args9(0).Name = "ToPoint"
args9(0).Value = "$C$7"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args9())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args11(0) as new com.sun.star.beans.PropertyValue
args11(0).Name = "Nr"
args11(0).Value = 3

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args11())
rem ----------------------------------------------------------------------
dim args12(0) as new com.sun.star.beans.PropertyValue
args12(0).Name = "ToPoint"
args12(0).Value = "$C$30000"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args12())

rem ----------------------------------------------------------------------
dim args13(5) as new com.sun.star.beans.PropertyValue
args13(0).Name = "Flags"
args13(0).Value = "D"
args13(1).Name = "FormulaCommand"
args13(1).Value = 0
args13(2).Name = "SkipEmptyCells"
args13(2).Value = false
args13(3).Name = "Transpose"
args13(3).Value = false
args13(4).Name = "AsLink"
args13(4).Value = false
args13(5).Name = "MoveMode"
args13(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args13())

rem ----------------------------------------------------------------------
dim args14(0) as new com.sun.star.beans.PropertyValue
args14(0).Name = "ToPoint"
args14(0).Value = "$D$30000"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args14())

rem ----------------------------------------------------------------------
dim args15(0) as new com.sun.star.beans.PropertyValue
args15(0).Name = "Nr"
args15(0).Value = 2

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args15())

rem ----------------------------------------------------------------------
dim args16(0) as new com.sun.star.beans.PropertyValue
args16(0).Name = "ToPoint"
args16(0).Value = "$C$9"

Could you please show an easier, tidier way to achieve the same thing? Such as:

args1(0).Name = "ToPoint"
args1(0).Value = "$C$5; $C$7; $A$4; $B$9; $E$6

In order to copy several cells at once rather then one by one?

Please, thank you for your input/
Back to top
View user's profile Send private message
DiGro
Super User
Super User


Joined: 02 Jun 2004
Posts: 1415
Location: Hoorn NH, The Netherlands

PostPosted: Thu May 21, 2009 6:18 am    Post subject: Reply with quote

From the document that covers most of the macro-principles
Document written by the master of macro, mr. Andrew Pitonyak:
"Useful Macro Information For OpenOffice"

Code:
The following macro copies the contents of a given sheet into a newly created of a second document.
'Author: Stephan Wunderlich [stephan.wunderlich@sun.com]
Sub CopySpreadsheet
  Dim doc1
  Dim doc2
  doc1 = ThisComponent
  selectSheetByName(doc1, "Sheet2")
  dispatchURL(doc1,".uno:SelectAll")
  dispatchURL(doc1,".uno:Copy")
  doc2 = StarDesktop.loadComponentFromUrl("private:factory/scalc" , _
                      "_blank",0,dimArray())
  doc2.getSheets().insertNewByName("inserted",0)
  selectSheetByName(doc2, "inserted")
  dispatchURL(doc2,".uno:Paste")
End Sub

Sub selectSheetByName(oDoc, sheetName)
  oDoc.getCurrentController.select(oDoc.getSheets().getByName(sheetName))
End Sub

Sub dispatchURL(oDoc, aURL)
  Dim noProps()
  Dim URL As New com.sun.star.util.URL

  frame = oDoc.getCurrentController().getFrame()
  URL.Complete = aURL
  transf = createUnoService("com.sun.star.util.URLTransformer")
 transf.parseStrict(URL)

  disp = frame.queryDispatch(URL, "", _
            com.sun.star.frame.FrameSearchFlag.SELF _
         OR com.sun.star.frame.FrameSearchFlag.CHILDREN)
  disp.dispatch(URL, noProps())
End Sub


See if you can make it work Smile
_________________
DiGro

Windows 7 Home Premium and AOO 4.0.1 NL (Dutch)
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group