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

Creating a macro - Writing a Script - Using the API

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

Postby Herb40 » Sat Apr 15, 2017 5:27 pm

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
Herb40
 
Posts: 134
Joined: Thu May 08, 2014 3:35 am

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

Postby Villeroy » Sat Apr 15, 2017 6:07 pm

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

download/file.php?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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 23972
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby Herb40 » Sat Apr 15, 2017 7:10 pm

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
Herb40
 
Posts: 134
Joined: Thu May 08, 2014 3:35 am

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

Postby Zizi64 » Sat Apr 15, 2017 8:27 pm

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; LibreOffice4.4.7 on Win7x64Prof.
And the portable versions: LO3.3.0-LO5.3.4 and AOO4.1.3
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 5444
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby Villeroy » Sat Apr 15, 2017 9:21 pm

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   Expand viewCollapse view
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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 23972
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby Herb40 » Sun Apr 16, 2017 3:54 pm

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   Expand viewCollapse view
   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
Herb40
 
Posts: 134
Joined: Thu May 08, 2014 3:35 am


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 11 guests