Page 1 of 1
[Solved] Copying values & formats from one SS to another SS
Posted: Sat Apr 15, 2017 5:27 pm
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.
Re: Copying values and formats from one SS to another SS
Posted: Sat Apr 15, 2017 6:07 pm
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)
Re: Copying values and formats from one SS to another SS
Posted: Sat Apr 15, 2017 7:10 pm
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.
Re: Copying values and formats from one SS to another SS
Posted: Sat Apr 15, 2017 8:27 pm
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.
Re: Copying values and formats from one SS to another SS
Posted: Sat Apr 15, 2017 9:21 pm
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.
Re: Copying values and formats from one SS to another SS
Posted: Sun Apr 16, 2017 3:54 pm
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)