Copy-Past format, macro, basic, calc

English
, ,
#1

Hello,
The code shown below copies the range of cells and pastes them, but only the format, not the values. The problem is that the code sometimes works and sometimes doesn’t. Is there a way to rewrite the code differently so that it always works?
Thanks in advance.

Function kopiPastForma(brojArtikala)
dim row as integer
row = 23 +  brojArtikala - 11
rem msgbox "Koristi se! red je: " + row + ",  broj artikala: " + brojArtikala
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 = "ToPoint"
args1(0).Value = "$S$14:$AB$14"

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

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$S$24:$S" & row 
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())

rem ----------------------------------------------------------------------
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 = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args4())

rem ----------------------------------------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "ToPoint"
args5(0).Value = "$AC$25"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args5())


end Function
#2

It’s much better if you create a cell style and apply it whenever you want.

cell_range.CellStyle = "my_style"
1 Like
#4

Any example? Maybe like this:


Sub ExampleSetValue
  Dim oDoc As Object, oSheet As Object, oCell As Object
  oDoc=ThisComponent
  oSheet=oDoc.Sheets.getByName("RAČUNI")
  oCell=oSheet.getCellRangeByName("S25") 'A1
  'oCell.setValue(23658)
  oCell.CharHeight="8"
  'oCell.NumberFormat=2   '23658.00
  'oCell.SetString("oops")
  'oCell.setFormula("=FUNCTION()")
  'oCell.IsCellBackgroundTransparent = TRUE
  'oCell.CellBackColor = RGB(255,141,56)
End Sub
#5

You can see the chapter 15.5.5 in document OpenOfficeorg Macros Explained.odt V4.1 www.pitonyak.org/oo.php

#6

AS far as I can see from the book:

  • copyRange(CellAddress, CellRangeAddress) - “The net effect of the copyRange() method is the same as copying a range of cells to the clipboard, positioning the cursor at the specified cell, and then pasting the cells into place.” I nee to copy only format.

  • getData() and setData() - “The data functions are fast and easy, but they only copy data, not formatting.”

  • Use dispatch commands - “Although the clipboard is not inherently safe, Paste Special provides numerous options when pasting data not available using other methods.”
    Because I already use code with dispatch commands, and sometimes it works and sometimes not, it seems that there is not available a different method for copy-pasting formats. That means, I must write code for that.

#7

My experience is, the uno command .uno:InsertContents is functional in macros. But it need set the parameters correctly. If you use Paste Special with one settings and after it with other settings, then you must always set correct parameters for .uno:InsertContents.
Can you upload the example ODS? With copied and expected part.

#8

Thanks, KamilLanda. Maybe next time. I solved that problem in another way.

#9

I didn’t realize that there are many dispatch commands in the code, so I would still like to ask you, KamilLanda, to take a look at the file I attached in the message and try using .uno:InsertContents to provide an example of how it would work. In the file, once you open it, you will find brief instructions. Grateful in advance.
copyPast Special.ods (53.7 KB)

#10

To copy only cell formats, use Paste Special (.uno:InsertContents) with the appropriate parameters. At the current time there is no UNO alternative.
Please upload an example for which pasting formats does not work.

#11 
Sub CopyPaste
dim document, dispatcher as object
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 = "ToPoint"
args1(0).Value = "$RAČUNI.$C$14:$L$14"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$RAČUNI.$C$24:$C$60"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())
dim args5(5) as new com.sun.star.beans.PropertyValue
args5(0).Name = "Flags" : args5(0).Value = "T"
args5(1).Name = "FormulaCommand" : args5(1).Value = 0
args5(2).Name = "SkipEmptyCells" : args5(2).Value = false
args5(3).Name = "Transpose" : args5(3).Value = false
args5(4).Name = "AsLink" : args5(4).Value = false
args5(5).Name = "MoveMode" : args5(5).Value = 4
dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args5())

rem ----------------------------------------------------------------------
dim args6(0) as new com.sun.star.beans.PropertyValue
args6(0).Name = "ToPoint"
args6(0).Value = "$RAČUNI.$N$1:$Z$29"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args6())
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
rem ----------------------------------------------------------------------

dim args11(0) as new com.sun.star.beans.PropertyValue
args11(0).Name = "ToPoint"
args11(0).Value = "$C$62"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args11())
dim args12(5) as new com.sun.star.beans.PropertyValue
args12(0).Name = "Flags" : args12(0).Value = "SVDT"
args12(1).Name = "FormulaCommand" : args12(1).Value = 0
args12(2).Name = "SkipEmptyCells" : args12(2).Value = false
args12(3).Name = "Transpose" : args12(3).Value = false
args12(4).Name = "AsLink" : args12(4).Value = false
args12(5).Name = "MoveMode" : args12(5).Value = 4
dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args12())

rem ----------------------------------------------------------------------
dim args13(0) as new com.sun.star.beans.PropertyValue
args13(0).Name = "ToPoint"
args13(0).Value = "$RAČUNI.$A$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args13())
End Sub
#12

sokol92:

Thanks for that information.

#13

Thanks schiavinatto for code.

#14

Thanks, Schiavinatto. I already have a similar solution (look at my first post) in this topic.

#15

Wait:
format_brush

Or did I miss some requirements?

#16

We are discussing the possibilities of macros. I am not aware of the corresponding UNO methods.

#17

Karolus, that is possible to do manually. We are talking here about a macro solution.

#18

It seems the Copy/Paste for range N1:Z29 is like standard Ctrl+C/V, so you can use .copyRange or .getTransferable() instead of .uno:InsertContents.

Sub CopyPaste 'with method copyRange
	dim oDoc as object, oSheet as object, oRange as object, oCell as object, copy as object
	oDoc=ThisComponent
	oSheet=oDoc.Sheets.getByName("RAČUNI")
	oRange=oSheet.getCellRangeByName("N1:Z29") 'source
	oCell=oSheet.getCellRangeByName("A62") 'destination
	oSheet.copyRange(oCell.CellAddress, oRange.RangeAddress)
End Sub

Next way I know is only the detection of necessary properties in single cells and set these properties to other cells. Or set Styles for the ranges - for example the columns. Unfortunately there isn’t easier possibility to “bypass” the .uno:InsertContents.

#19

Thanks KamilLanda

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.