I have a macro that requires user input to:
dispatcher.executeDispatch(document, “.uno:ToggleMergeCells”, “”, 0, Array())
The pop up shows 3 options. I would like code to provide an answer to the pop up.
https://api.libreoffice.org/docs/idl/ref/interfacecom_1_1sun_1_1star_1_1util_1_1XMergeable.html
Sub toggleMerge(oRange)
bM = oRange.getIsMerged()
oRange.merge(Not bM)
End SubMany thanks for your quick reply – sorry I could not get back to you as quickly.
I’m having trouble with that code; therefore, I must be over looking something.
Here’s an example of what I’m trying to do:
Sub Trial
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService(“com.sun.star.frame.DispatchHelper”)
Doc = ThisComponent
Sheet = Doc.Sheets(1)
args1(0).Name = “ToPoint”
oRange = “$A$1:$D$1”
args1(0).Value = oRange
dispatcher.executeDispatch(document, “.uno:GoToCell”, “”, 0, args1())
Call toggleMerge(oRange)
End Sub
I always get an error with Sub toggleMerge(oRange) highlighted and
BASIC runtime error.
Object variable not set.
So I’m missing something. What is it?
Thanks again,
Nick
PS: I’ve got a work around. If the cell contents are in the first column of the range, dispatcher.executeDispatch(document, “.uno:ToggleMergeCells”, “”, 0, Array())
works without any user input. So I check the position of the cells content, and move them to the first column of the range. It’s messy with a lot of code but it works. I’d rather use toggleMerge(oRange) if I could.
Many thanks for toggleMerge.ods, it was very helpful. FYI, when I used the macro code in My Macros I had to add Dim rg as Object, and Dim bM as Boolean. I could not find where they were specified.
Again, thanks.
Nick
Is there a way to do the merging with “Move the contents of the hidden cells into the first cell”?
The option dialog was added in 2016.
There is no way to pass the move option to XMergeable.
That’s the issue I’m having - merging and preserving the data from both cells. With several thousand lines of “bad” data in several hundred thousand lines of good data, I’m vacillating between brute-forcing the problem, or actually trying to find a solution.
The “bad data” seems to be where people entered illegal characters (tabs, NewLines, multibyte characters in names ? not sure, don’t care ; not my database - not editable by me except by exporting) into what should have been a text field, so now the exported data has extra cells of data, misaligning the subsequent dozens of columns on that row.
You can unmerge all cells by simply selecting a rectangle that includes them all (or entire columns) and then Format>Merge>Unmerge