# Calc - reference cells that have been merged in basic macro

Hi, I want a macro that merges manually selected cells, then applies a style, then writes text in the merged block, and then centers the text. I found a way to perform all the individual tasks, but the complete macro fails (without errors) after merging and applying the style. I think this is because after merging the cells, I have no longer a valid reference to the merged cells. If I manually select the merged cells and then run the macro a second time, then the rest of the macro executes as expected and the text is written to the merged block.

So the question is how I can reference the newly merged block from within the macro.

sub applyStyle(strStyle, strText, blnCentered)

theSelection=ThisComponent.CurrentSelection

//'split the selection just in case it already contains merged cells
theSelection.merge(False)
//'merge the cells
theSelection.merge(True)

//' apply the style
dim document   as object
dim dispatcher as object

document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

dim args1(1) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Template"
args1(0).Value = strStyle
args1(1).Name = "Family"
args1(1).Value = 2

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

//'enter the text   <== fails in the first run
theSelection.setString(strText)
// 'center the text
if blnCentered = true then
theSelection.HoriJustify=com.sun.star.table.CellHoriJustify.CENTER
theSelection.VertJustify=com.sun.star.table.CellVertJustify.CENTER
endif
end sub


thanks

edit retag close merge delete

Sort by » oldest newest most voted

Hi,

you just have to get the cell at position (0,0) of the seleciton and write to it instead to the selection itself.

Minimal example:

sub mergeAndSet
theSelection=ThisComponent.CurrentSelection
theSelection.merge(False)
theSelection.merge(True)
mergedCell = theSelection.getCellByPosition(0,0)
mergedCell.String = "newContent"
end sub


Hope that helps.

more

1

Worked! Thanks! Not sure what to do now, I never had multiple working answers before, and the answers are basically the same (mergedCell = theSelection.getCellByPosition(0,0)). How does this work? Do I accept both, do I have to choose one over the other?

( 2020-09-28 14:21:11 +0100 )edit
1

That is kind of up to you. In general i would say upvote all helpful answers and accept the one you think helped you out the most.

( 2020-09-28 14:45:26 +0100 )edit

You need study the API function if you want to write your macros instead of recording them. The macro recorder has a very limited capability. And install and use one of the object inspection tools (XrayTool, MRI).

Sub Merge_and_style

oSelection = ThisComponent.CurrentSelection
if oSelection.ImplementationName = "ScCellRangeObj" then
if oSelection.GetIsMerged then
oSelection.merge(False)
end if
oCell = oSelection.getCellByPosition(0,0)
oSelection.merge(True)
oCell.CellStyle = "MyMergedCells"
end if
End Sub


The Cell style named MyMergedCells (with adjusted vertical and horizontal alignment properties) must be created before you launch the macro.

more

Yeah, I hear MRI is really helpful, but I haven't figured out yet how it works and how to use it. But the code works, thanks for your input!

( 2020-09-28 14:22:47 +0100 )edit

I am using the Xray install it with the Extension manager, and then put this commant into your macro code:

oSelection = ThisComponent.CurrentSelection
xray oSelection


You must load the Xray Library before you launch the macro. You can load the xray by a macro code:

If (Not GlobalScope.BasicLibraries.isLibraryLoaded("XrayTool")) Then
End If


...or it is enough to click on the name of the Xray in the Library list in the Basic IDE.

See the enlargened panel of the working Xray at my answer.

( 2020-09-28 17:32:09 +0100 )edit

Thanks a lot for that, Zizi, I have it working...

( 2020-09-29 05:56:38 +0100 )edit

If theSelection no longer holds a valid reference after merging, I guess an error should be thrown. Anyway, you could try to refresh theSelection from ThisComponent.CurrentSelection (which should now be the merged block) before setting content.

On my phone now, so I can't test this myself.

more

Hey, tnx for replying! Not sure why, but this approach didn't work for me.

( 2020-09-28 14:16:39 +0100 )edit

## Stats

Seen: 104 times

Last updated: Sep 28 '20