Macro to Copy Cell values, formats, and image

[jmid35]

Hi,

I am trying to figure out how to copy a cell from one sheet to another sheet using a macro. The cells contains formatted text and an image anchored to the cell. I am able to copy the cell values and formats using the code below. But it does not copy the image. Any help would be greatly appreciated.

oSheets = ThisComponent.getSheets()
CopyFromSheet = oSheets.getByName("Sheet1")
CopyToSheet = oSheets.getByName("Sheet2")

source = CopyFromSheet.getCellrangeByName("C9").getRangeAddress()
destination =  CopyToSheet.getcellbyposition(8,5).getCellAddress()

CopyFromSheet.copyRange( destination , source )

---

LO 6.2.7 on Win10

[mauricio]

To my surprise, now, the method copyRange, copy image too. Next code work fine for me.

  Sub Main()
	doc = ThisComponent
	target_sheet = doc.Sheets.getByName("Sheet2")
	source = doc.Sheets.getByName("Sheet1").getCellRangeByName("C9")
	cell = target_sheet.getCellByPosition(8, 5)
	target_sheet.copyRange(cell.CellAddress, source.RangeAddress)
  End Sub

[jmid35]

Thank You for the code. I tried it but it does not copy anything. All it does is select the cells.

[mauricio]

In my PC (ArchLinux, Libo 6.2) work fine, I will try in your S.O.

[mauricio]

I try in virtual machine with Win10 and Libo 6.2, work fine for me

[jmid35]

I tried it again and it does copy it but the image does not stay inside the target cell.

[mauricio]

please, try with the new code

[schiavinatto]

@jmid35, You can keep as submacro and use only the Call, so you can use n times using only the Call.

Sub CopyPaste
   Call GoToCellCopyPasteSS "Plan1.A1", "Plan2.A1"
End Sub

Sub GoToCellCopyPasteSS ( X As String, Y As String)
'================================================|
dim copy(0) as new com.sun.star.beans.PropertyValue
copy(0).Name = "ToPoint" : copy(0).Value = X
createUnoService("com.sun.star.frame.DispatchHelper").executeDispatch(ThisComponent.CurrentController.Frame, ".uno:GoToCell", "", 0, copy())
createUnoService("com.sun.star.frame.DispatchHelper").executeDispatch(ThisComponent.CurrentController.Frame, ".uno:Copy", "", 0, Array())
dim paste(0) as new com.sun.star.beans.PropertyValue
paste(0).Name = "ToPoint" : paste(0).Value = Y
createUnoService("com.sun.star.frame.DispatchHelper").executeDispatch(ThisComponent.CurrentController.Frame, ".uno:GoToCell", "", 0, paste())
createUnoService("com.sun.star.frame.DispatchHelper").executeDispatch(ThisComponent.CurrentController.Frame, ".uno:Paste", "", 0, Array())
End Sub

[Lupp]

The graphical objects you want to copy together with cells must be anchored to these cells.
This kind of anchoring is default.
If you paste a shape copied from elsewhere assuming it goes to the selected cell, you may be wrong. If the original was achored ‘To Page’ the pasted copy will also be anchored this way despite the fact that it may get relocated with respect to the target cell. Copying that cell now, you wont get the object along with it.

[jmid35]

That is what I am seeing too. Is it possible to copy the image and move the anchor to the cell it is copied to.

[Lupp]

Select the image and choose the ‘Anchor’ item from its context menu. There you can select ‘To Cell’ (or the rather new option including ‘Resize’).
In Calc you cannot move the anchor on its own by dragging it with the mouse. Dragging the image or changing its position by user code will, on the other hand, make the anchor follow in a reasonable way to keep things consistent.

[jmid35]

Like I said in the original post, the image is anchored to the cell that is being copied. I basically want to create a macro to do a Ctrl C and Ctrl V.

[Lupp]

I simply doubted that because the process works for me as expected using the code from the question as well as using a recorded macro.

[jmid35]

I tried my own code again and it copied it but the image does not stay inside the target cell.

[Lupp]

What do you mean by “stay inside”?
If the area of the source cell was large enough to display the image inside, the area of the target cell may not. What do you expect? What do you think other users might expect depending on the situation? Many different answers thinkable!
If somebody actually often needs a very specific behaviour of objects under some actions, it’s a case for specific user code. You want to change the size of the image to fit into the target cell? Or you want to resize column width and row height to make the cell large enough for it? Just do so.

[jmid35]

By staying inside i mean that when I copy to Cell H5, the image might be positioned over C2. And yes that is with the image anchored to the cell before copying it.

[Lupp]

Well we already agreed the original image was anchored to the source cell. In this case the pasted image will also be anchored this way. I still don’t understand what you mean by “…the image does not stay inside the target cell.” Is this concerning the area of the target cell and the size of the image - or what else?
In my testing the image generally was positioned relative to the LU corner of the target cell as the original was in the source cell. There were lots of buggy actions in addition, probably due to the immature version I was using.
Beyond that, the behaviour of Copy/Paste and copyRange concerning objects was changed recently or was buggy anyway.
Report the bugs and hope they will be fixed.