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

ooo.calc macro copy paste a range of cells to another

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
svender
General User
General User


Joined: 27 Jan 2010
Posts: 34

PostPosted: Mon Feb 14, 2011 10:35 pm    Post subject: ooo.calc macro copy paste a range of cells to another Reply with quote

Would like to program a button that copies a range of cells on my spreadsheet to another range of cells but excludes validity options in the copy.

i.e.

copy range A1:B2 to range C1:D2

Cells in range A1:B2 all have validity input help alerts as does range C1:D2.

When copying over, can the input help alert part of the cell be left alone?

I guess what I want to do is copy and paste cell strings only?

Thanks in advance!
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Feb 15, 2011 4:10 am    Post subject: Reply with quote

Has been answered dozends of times in the MACRO FORUM.
The solution is too coplex anyway as your stated here: http://user.services.openoffice.org/en/forum/viewtopic.php?f=9&t=38387
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
svender
General User
General User


Joined: 27 Jan 2010
Posts: 34

PostPosted: Tue Feb 15, 2011 12:03 pm    Post subject: Reply with quote

Looks like I posted this in the wrong forum.

The search function here is completely useless.

When I type in a search in the macro api forum 'copy cell range' i get 100s of non related results.

I've been better off using a Google search to find answers here. Unfortunately, haven't found anything that fits my case.

Since you have posted 1000s of times with100s of solutions I'm assuming you know the answer but don't feel like sharing or linking me directly to a 'been answered dozens of times' solution.

Bye the way, I'm not dummydecoy.

Someone else?
Back to top
View user's profile Send private message
svender
General User
General User


Joined: 27 Jan 2010
Posts: 34

PostPosted: Tue Feb 15, 2011 5:01 pm    Post subject: Reply with quote

After searching and trying to find possible code snippets for my button, I came up with the following code...

Code:
Sub CopyPasteRangeOfCells
   Dim document As Object, dispatcher As Object, args3(5) As New com.sun.star.beans.PropertyValue
   dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
   oDesktop = createUnoService("com.sun.star.frame.Desktop")
   oDocument = ThisComponent
   oSheet1 = oDocument.Sheets.getByIndex(0)
   'oSheet2 = oDocument.Sheets.getByIndex(1)
   oFromRange = oSheet1.getCellRangeByPosition(1,7,4,11)
   oToCell = oSheet1.getCellRangeByPosition(12,7,15,11)
   'oToCell = oSheet2.getCellByPosition(2,5)

   oDocument.CurrentController.Select(oFromRange)
   dispatcher.executeDispatch(oDocument, ".uno:Copy", "", 0, Array())
   args3(0).Name = "Flags"
   args3(0).Value = "S"          'Flags - (A - all, S - string, V - value, D - date, time, F - formulas, N - notes, T - formats) - SVDFNT 
   args3(1).Name = "FormulaCommand"
   args3(1).Value = 0
   args3(2).Name = "SkipEmptyCells"
   args3(2).Value = false
   args3(3).Name = "Transpose"
   args3(3).Value = false
   args3(4).Name = "AsLink"
   args3(4).Value = false
   args3(5).Name = "MoveMode"
   args3(5).Value = 4                'Modes - (1 - no specialities, 2 - inserts rows then paste data, 3 - inserts columns then paste data)
   oDocument.CurrentController.Select(oToCell)

   dispatcher.executeDispatch(oDocument, ".uno:InsertContents", "", 0, args3())
End Sub


But I get the following error message...

Type: com.sun.star.lang.IllegalArgumentException
Message: cannot coerce argument type during corereflection call!

at...

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


Am I getting this error message because I have merged cells within the range?
Back to top
View user's profile Send private message
svender
General User
General User


Joined: 27 Jan 2010
Posts: 34

PostPosted: Tue Feb 15, 2011 8:48 pm    Post subject: Reply with quote

Wow!

Did a Google search 'openoffice calc copy range of cells macro' and this thread is already 5th on the list.

Still working on this will post a solution when I figure it out.
Back to top
View user's profile Send private message
pitonyak
Administrator
Administrator


Joined: 09 Mar 2004
Posts: 3655
Location: Columbus, Ohio, USA

PostPosted: Mon Jun 13, 2011 8:22 pm    Post subject: Reply with quote

Code:
Sub CopySpreadsheetRange
  REM Get sheet 1, the original, and 2, which will contain the copy.
  oSheet1 = ThisComponent.Sheets.getByIndex(0)
  oSheet2 = ThisComponent.Sheets.getByIndex(1)

  REM Get the range to copy and the rang to copy to.
  oRangeOrg = oSheet1.getCellRangeByName("A1:C10").RangeAddress
  oRangeCpy = oSheet2.getCellRangeByName("A1:C10").RangeAddress

  REM The insert position
  oCellCpy = oSheet2.getCellByPosition(oRangeCpy.StartColumn,_
    oRangeCpy.StartRow).CellAddress

  REM Do the copy
  oSheet1.CopyRange(oCellCpy, oRangeOrg)
End Sub

_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php
Back to top
View user's profile Send private message Send e-mail Visit poster's website AIM Address
Eduardoj
General User
General User


Joined: 12 Sep 2004
Posts: 21
Location: Colombia

PostPosted: Sun Apr 29, 2012 9:47 am    Post subject: Reply with quote

The problem is with
Quote:
oDocument.CurrentController.Select(oFromRange)


It should be
Code:
oDocument.Controller.Select(oFromRange)


because the property is not CurrentController but Controller
_________________
"One thing only I know, and that is that I know nothing"
Socrates
Back to top
View user's profile Send private message
pitonyak
Administrator
Administrator


Joined: 09 Mar 2004
Posts: 3655
Location: Columbus, Ohio, USA

PostPosted: Sun Apr 29, 2012 3:22 pm    Post subject: Reply with quote

Eduardoj wrote:
because the property is not CurrentController but Controller


Historically, it has been currentcontroller, what version of OOo are you using and what document type?
_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php
Back to top
View user's profile Send private message Send e-mail Visit poster's website AIM Address
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Apr 29, 2012 3:53 pm    Post subject: Reply with quote

pitonyak wrote:
Eduardoj wrote:
because the property is not CurrentController but Controller


Historically, it has been currentcontroller, what version of OOo are you using and what document type?

The broken macro recorder declares the frame as oDocument.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
fotoman
General User
General User


Joined: 21 Feb 2010
Posts: 6

PostPosted: Mon Aug 20, 2012 3:09 pm    Post subject: Reply with quote

Andrew-

I'm using your CopyRange code, but is there a way to use that same code but have it only copy the values and not formulas?

thanks
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 Calc 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