copy only cell format (no value)

Question

I'd like copy only format (no value) from cell range (L3:L10) to cell range (H10:H11).

With Excel is easy:

Sheets(sheet1).Range("L3:L10").Select 
Selection.Copy 
Sheets(sheet1).Range("H10:H11").Select 
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

But with LibreOffice?

Can you help me?

What have you tried thus far? You might want to have a look at this if you're not sure where to start: http://www.openoffice.org/documentation/manuals/userguide3/0312CG3-CalcMacros.pdf — Ralph, Mar 22 '16 at 16:15

score 0 · Answer 1 · edited May 23 '17 at 12:31

I ran the macro recorder, and it generated this:

Sub PasteFormatting
    dim document   as object
    dim dispatcher as object
    document = ThisComponent.CurrentController.Frame
    dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

    dim args1(0) as new com.sun.star.beans.PropertyValue
    args1(0).Name = "ToPoint"
    args1(0).Value = "$L$3:$L$10"
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
    dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

    dim args3(0) as new com.sun.star.beans.PropertyValue
    args3(0).Name = "ToPoint"
    args3(0).Value = "$H$10:$H$11"
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())

    dim args4(5) as new com.sun.star.beans.PropertyValue
    args4(0).Name = "Flags"
    args4(0).Value = "T"
    args4(1).Name = "FormulaCommand"
    args4(1).Value = 0
    args4(2).Name = "SkipEmptyCells"
    args4(2).Value = false
    args4(3).Name = "Transpose"
    args4(3).Value = false
    args4(4).Name = "AsLink"
    args4(4).Value = false
    args4(5).Name = "MoveMode"
    args4(5).Value = 6
    dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args4())
End Sub

It works, although the code is ugly, as is to be expected of dispatcher code. The ranges you asked for are different sizes, so it generates a warning. It would be easy to fix this by simply using "$L$3:$L$4" as the source range.

API code would be much shorter and cleaner. For an example using XTransferableSupplier, see openoffice: duplicating rows of a table in writer. However, it may not be possible to paste only formatting with XTransferable.

copy only cell format (no value)

1 Answers1