Ask Your Question
0

loop in macro ?

asked 2013-11-27 14:18:55 +0200

pierre-e-delman gravatar image

updated 2015-07-21 05:05:19 +0200

doug gravatar image

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
edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

answered 2015-07-21 09:08:16 +0200

karolus gravatar image

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
edit flag offensive delete link more

Comments

nice use of DataArray and join().

doug gravatar imagedoug ( 2015-07-21 09:21:57 +0200 )edit
0

answered 2015-07-21 05:52:00 +0200

doug gravatar image

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
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2013-11-27 14:18:55 +0200

Seen: 1,651 times

Last updated: Jul 21 '15