Ask Your Question
0

BASIC Calc : How to copy a cell range from a row and paste to multiple rows ?

asked 2020-06-27 07:42:42 +0200

lonk gravatar image
Option Explicit

Sub CopyA1D1AndPasteToA6D6
    CopyFromRangeA1D1AndPasteToRangeA6D6ByLooping(5)
End Sub

Sub CopyFromRangeA1D1AndPasteToRangeA6D6ByLooping(pRows&)
    Dim oDoc                        As Object : oDoc    = ThisComponent
    Dim oSheet                      As Object : oSheet  = oDoc.getCurrentController.ActiveSheet
    Dim SourceRange, TargetCell     As Object
'   Dim TargetRange                 As Object
    Dim i                           As Long
    SourceRange = oSheet.getCellRangeByPosition(0, 0, 3, 0)
        For i = 1 To pRows
            TargetCell  = oSheet.getCellByPosition(0, 0 + i)
'           TargetRange = oSheet.getCellRangeByPosition(0, 0 + 1, 3, 0 + 1 + pRows - 1)
            oSheet.copyRange(TargetCell.CellAddress, SourceRange.RangeAddress)
        Next
End Sub

How can we avoid looping using i and use TargetRange for multiple rows instead?

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

answered 2020-06-28 13:34:02 +0200

Lupp gravatar image

updated 2020-06-28 13:37:56 +0200

You can use the fillAuto() method with FillDirection = 0 (TO_BOTTOM) and nSoureCount = 1: https://api.libreoffice.org/docs/idl/....
(In a case where your source range has more than one row, the automatisms coming with fillAuto() may not meet your needs. If your source contains "incrementable constants" the result may also not be what you want. Look for fillSeries() then - or get back to copyRange(). )

edit flag offensive delete link more

Comments

1

If you worry about efficiency in cases of very large target ranges: You can try a strategy of double-the-size-per-step.
I didn't test the idea really thoroughly, however.
The consept is exemplified here: https://forum.openoffice.org/en/forum...

Lupp gravatar imageLupp ( 2020-06-28 13:41:05 +0200 )edit
0

answered 2020-06-28 11:54:48 +0200

newbie-02 gravatar image

updated 2020-06-28 11:59:46 +0200

the fools way: record a macro ...


sub test21

rem ----------------------------------------------------------------------

rem define variables

dim document as object

dim dispatcher as object

rem ----------------------------------------------------------------------

rem get access to the document

document = ThisComponent.CurrentController.Frame

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

rem ----------------------------------------------------------------------

dim args1(0) as new com.sun.star.beans.PropertyValue

args1(0).Name = "ToPoint"

args1(0).Value = "$A$1:$D$1"

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

rem ----------------------------------------------------------------------

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

rem ----------------------------------------------------------------------

dim args3(0) as new com.sun.star.beans.PropertyValue

args3(0).Name = "ToPoint"

args3(0).Value = "$A$2:$D$6"

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

rem ----------------------------------------------------------------------

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

end sub 'test21


i know! that the code is 'cruel' at least, i know! it's not very flexible, and i know! that it doesn't fulfill all your requiremens / wishes ...

but it proves a way to copy into a range, and might be something you can build up upon,

and ... quite sure ... our pro's will throw a bunch of better solutions :-)

reg.

b.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-06-27 07:42:42 +0200

Seen: 36 times

Last updated: Jun 28