| View previous topic :: View next topic |
| Author |
Message |
svender
General User
Joined: 27 Jan 2010
Posts: 34
|
 Posted: Mon Feb 14, 2011 10:35 pm Post subject: ooo.calc macro copy paste a range of cells to another |
 |
|
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 |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
|
| Back to top |
|
|
svender
General User
Joined: 27 Jan 2010
Posts: 34
|
| Posted: Tue Feb 15, 2011 12:03 pm Post subject: |
|
|
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 |
|
|
svender
General User
Joined: 27 Jan 2010
Posts: 34
|
| Posted: Tue Feb 15, 2011 5:01 pm Post subject: |
|
|
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 |
|
|
svender
General User
Joined: 27 Jan 2010
Posts: 34
|
| Posted: Tue Feb 15, 2011 8:48 pm Post subject: |
|
|
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 |
|
|
pitonyak
Administrator
Joined: 09 Mar 2004
Posts: 3618
Location: Columbus, Ohio, USA
|
| Posted: Mon Jun 13, 2011 8:22 pm Post subject: |
|
|
| 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 |
|
|
Eduardoj
General User
Joined: 12 Sep 2004
Posts: 21
Location: Colombia
|
| Posted: Sun Apr 29, 2012 9:47 am Post subject: |
|
|
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 |
|
|
pitonyak
Administrator
Joined: 09 Mar 2004
Posts: 3618
Location: Columbus, Ohio, USA
|
| Posted: Sun Apr 29, 2012 3:22 pm Post subject: |
|
|
| 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 |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Sun Apr 29, 2012 3:53 pm Post subject: |
|
|
| 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 http://forum.openoffice.org |
|
| Back to top |
|
|
fotoman
General User
Joined: 21 Feb 2010
Posts: 6
|
| Posted: Mon Aug 20, 2012 3:09 pm Post subject: |
|
|
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 |
|
|
|
