Loop in macro?

[pierre-e-delman]

Hello,
I recorded a simple macro to fuse 3 sells together. It works only once.
I’d like it go all the way down the list of field adresses that i need to put tgether. It is possible ?
Thanks for any help, I’m a newby as far as macros.

 REM  *****  BASIC  *****

sub FusionAdresses
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 = "By"
args1(0).Value = 1

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

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "By"
args2(0).Value = 1

dispatcher.executeDispatch(document, ".uno:GoRightSel", "", 0, args2())

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

rem ----------------------------------------------------------------------
dim args4(1) as new com.sun.star.beans.PropertyValue
args4(0).Name = "By"
args4(0).Value = 1
args4(1).Name = "Sel"
args4(1).Value = false

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


end sub

[karolus]

More generalized, works on any Selection:

Select the Range in Question and run:

Sub merge_concat_selectet_rows
    doc = thisComponent
    sel = doc.CurrentSelection
    data = sel.DataArray
    n = 0
    for each row in data
        sel.getCellRangeByPosition( 0, n, ubound( row ), n).merge( True )
        sel.getCellByPosition( 0, n ).setString( join( row, "_")
        n = n+1
    next
End Sub

[doug]

nice use of DataArray and join().

[doug]

Here is a more comprehensible syntax for the operation. The below effectuates the solution by copying the desiredOutput into one column. For demonstration, it also merges the columns but the operation results in data loss rather than concatenation, so that operation alone does not achieve the desired result. The key operation is simply the reading and copying of Values within a Loop.

Sub CopyThreeColsToFirstCol
spreadDoc = ThisComponent.CurrentController
sheet = spreadDoc.getActiveSheet
spread = sheet.Spreadsheet

desiredOutput = ""
i = 0

Do Until desiredOutput = "  "
  cola = spread.getCellByPosition(0,i)
  colb = spread.getCellByPosition(1,i)
  colc = spread.getCellByPosition(2,i)

  desiredOutput = cola.string & " " & colb.string & " " & colc.string 

  mergeRange = spread.getCellRangeByPosition(0,i,2,i)
  mergeRange.merge(True)
  cola = spread.getCellByPosition(0,i)
  cola.string = desiredOutput

  i= i + 1

Loop

End Sub