[Solved] Copying values & formats from one SS to another SS

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Herb40
Posts: 134
Joined: Thu May 08, 2014 3:35 am

[Solved] Copying values & formats from one SS to another SS

Post by Herb40 »

Using Basic, I need to copy all of the VALUES from a sheet in one SS to another SS, while retaining all of the formatting, column widths, etc. I know that I can manually copy the source sheet contents to the clipboard and then use Paste Special to paste the values and formatting to a sheet in the other document. This works just fine and retains every last bit of formatting.

For use in a Basic macro, I have found the PasteCellRange method that should allow me to paste the clipboard to the other SS. My question is: what do I use to copy the source sheet to the clipboard so I can then paste it?

I know that oRange.DataArray = iRange.DataArray will copy the data, but loses all formatting. I know that using Transferable Content will copy the FORMULAS and that I can then use oRange.DataArray = iRange.DataArray to replace the formulas with the values. This gives me everything I want except retaining the column widths. I could add code to redo the column widths, but I would need special code for each different sheet that was copied. I need a general solution that retains the column widths.

I imagine that this question has been answered before, but I can't find it. Thanks for your help.
Last edited by Herb40 on Sun Apr 16, 2017 3:55 pm, edited 1 time in total.
OpenOffice 4.1.3 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copying values and formats from one SS to another SS

Post by Villeroy »

It is a way easier to exchange data on the same formatted sheet by means of scenarios.

http://forum.openoffice.org/en/forum/do ... hp?id=3004 This example exchanges formulas but it works with constant values in the same way.

Select the exchangable range of cells and call Tools>Scenarios... to create a new scenario.
Give a name to the selected scenario range (just type a name into the name box left of the formula bar).
Organize scenarios in the navigator (hit F5 for the navigator and then its last button)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Herb40
Posts: 134
Joined: Thu May 08, 2014 3:35 am

Re: Copying values and formats from one SS to another SS

Post by Herb40 »

Villeroy wrote:It is a way easier to exchange data on the same formatted sheet by means of scenarios.
"...on the same formatted sheet..." But I need to exchange data to a different sheet in another SS.

"It is way easier... by means of scenarios." But I'm just looking for info about an API method that will copy a range to the clipboard. Why would anyone want to use a scenario for this? KISS is the approach I prefer.

Thanks anyway, Villeroy.
OpenOffice 4.1.3 on Windows 10
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copying values and formats from one SS to another SS

Post by Zizi64 »

You can use the Clipboard (by macro) for copying Cell styles, and other properties together with the cell contents.
You must copy a whole column for copying the Column properties, and a whole row for copying the row properties.

Or you can get the desired property values in the source document by API functions and you can set them in the target document.


See Pitonyak's books, and the API descriptrions.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copying values and formats from one SS to another SS

Post by Villeroy »

If it is easier to do it in the GUI, it is easier in the API as well. And it is far more efficient to save one formatted sheet with varying data sets rather than dozends of equally formatted sheets.

The following macro has been recorded by the MRI object inspector called from my above linked scenario document:

Code: Select all

Sub Snippet
  Dim oCurrentController As Variant
  Dim oActiveSheet As Variant
  Dim oScenarios As Variant
  Dim oObj1 As Variant
  Dim oObj2 As Variant

  oCurrentController = ThisComponent.getCurrentController()
  oActiveSheet = oCurrentController.getActiveSheet()
  oScenarios = oActiveSheet.getScenarios()
  
  oObj1 = oScenarios.getByName("Cosinus")
  oObj1.apply()
  oObj2 = oScenarios.getByName("Tangens")
  
  oObj2.apply()
End Sub
Even without scenario range, it might be easier and more efficient to copy the entire sheet and replace all data.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Herb40
Posts: 134
Joined: Thu May 08, 2014 3:35 am

Re: Copying values and formats from one SS to another SS

Post by Herb40 »

Thanks, guys. I followed the discussion of the dispatcher and data exchange in Andy Pitonyak's OOME and soon achieved a successful copy, except that the optimal column widths were not retained by the dispatcher's Copy action. I had noted that if I did a manual SelectAll, Copy, and PasteSpecial, everything was copied intact. So I used the macro recorder and found that the dispatcher issued a SelectAll before the Copy. This addition is included in the following code which should achieve everything required to produce an exact copy of the values and formatting. In this code, all of the "i..." objects (iDoc, iSheet, etc.) refer to the "input" SS whose iSheet is to be copied. Likewise, all of the "o..." objects refer to the "output" SS whose oSheet is to receive the copy.

Thanks to Andy Pitonyak for presenting the basis for this code. I did not experiment with the six Property Values supplied to the dispatcher, and assume that all six are required.

Code: Select all

	Dim oDispatcher	as Variant

	Dim iDoc		  as Object	' input SS document
	Dim iCC			as Object	' input SS current controller
	Dim iFrame		as Object	
	Dim iSheet		as Object	' input sheet to be copied
	Dim iRange		as Object
	
	Dim oDoc		  as Object	' output document
	Dim oCC			as Object	' output SS current controller
	Dim oFrame		as Object
	Dim oSheet		as Object	' output sheet to receive the copy
	Dim oRange		as Object

	Dim dPV(5) as new com.sun.star.beans.PropertyValue	' dispatcher PVs		
	dPV(0).Name = "Flags"
	dPV(0).Value = "SVDNT"
	dPV(1).Name = "FormulaCommand"
	dPV(1).Value = 0
	dPV(2).Name = "SkipEmptyCells"
	dPV(2).Value = False
	dPV(3).Name = "Transpose"
	dPV(3).Value = False
	dPV(4).Name = "AsLink"
	dPV(4).Value = False
	dPV(5).Name = "MoveMode"
	dPV(5).Value = 4
				
	' Create a DispatchHelper service to do the work.
	oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
		
	' Make sure both CurrentControllers are focused on the desired sheet.
	iCC = iDoc.CurrentController
	iRange = iSheet.getCellRangeByName("A1")
	iCC.Select(iRange)
	
	oCC = oDoc.CurrentController
	oRange = oSheet.getCellRangeByName("A1")
	oCC.Select(oRange)
		
	' Use a dispatch to Select All of the input sheet
	' and copy everything to the clipboard.
	iFrame = iCC.Frame
	oDispatcher.executeDispatch(iFrame, ".uno:SelectAll", "", 0, Array())
	oDispatcher.executeDispatch(iFrame, ".uno:Copy", "", 0, Array())

	' Paste the clipboard contents to the output sheet.				
	oFrame = oCC.Frame
	oDispatcher.executeDispatch(oFrame, ".uno:InsertContents", "", 0, dPV())
	
	' If desired, select cell A1 of the output sheet.								
	oCC.Select(oRange)

OpenOffice 4.1.3 on Windows 10
Post Reply