Calc - reference cells that have been merged in basic macro

[stabiloboss]

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

[igorlius]

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.

[stabiloboss]

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?

[igorlius]

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.

[Zizi64]

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.

[stabiloboss]

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!

[Zizi64]

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
	GlobalScope.BasicLibraries.LoadLibrary("XrayTool")
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.

[stabiloboss]

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

[keme]

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.

[stabiloboss]

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