Ask Your Question

Macro to Copy Cell values, formats, and image

asked 2019-10-24 15:32:31 +0200

jmid35 gravatar image


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

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2019-10-29 01:09:05 +0200

@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
copy(0).Name = "ToPoint" : copy(0).Value = X
createUnoService("").executeDispatch(ThisComponent.CurrentController.Frame, ".uno:GoToCell", "", 0, copy())
createUnoService("").executeDispatch(ThisComponent.CurrentController.Frame, ".uno:Copy", "", 0, Array())
dim paste(0) as new
paste(0).Name = "ToPoint" : paste(0).Value = Y
createUnoService("").executeDispatch(ThisComponent.CurrentController.Frame, ".uno:GoToCell", "", 0, paste())
createUnoService("").executeDispatch(ThisComponent.CurrentController.Frame, ".uno:Paste", "", 0, Array())
End Sub
edit flag offensive delete link more

answered 2019-10-24 19:11:44 +0200

updated 2019-10-25 01:56:07 +0200

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
edit flag offensive delete link more


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

jmid35 gravatar imagejmid35 ( 2019-10-24 20:08:03 +0200 )edit

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

mauricio gravatar imagemauricio ( 2019-10-24 21:37:53 +0200 )edit

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

mauricio gravatar imagemauricio ( 2019-10-24 21:42:19 +0200 )edit

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

jmid35 gravatar imagejmid35 ( 2019-10-24 22:26:34 +0200 )edit

please, try with the new code

mauricio gravatar imagemauricio ( 2019-10-25 01:56:34 +0200 )edit

answered 2019-10-24 15:59:16 +0200

Lupp gravatar image

updated 2019-10-24 16:00:09 +0200

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.

edit flag offensive delete link more


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.

jmid35 gravatar imagejmid35 ( 2019-10-24 16:02:30 +0200 )edit

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.

Lupp gravatar imageLupp ( 2019-10-24 16:29:16 +0200 )edit

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.

jmid35 gravatar imagejmid35 ( 2019-10-24 20:09:56 +0200 )edit

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.

Lupp gravatar imageLupp ( 2019-10-24 21:38:49 +0200 )edit

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

jmid35 gravatar imagejmid35 ( 2019-10-24 22:27:34 +0200 )edit

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.

Lupp gravatar imageLupp ( 2019-10-25 15:07:04 +0200 )edit

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.

jmid35 gravatar imagejmid35 ( 2019-10-25 16:44:04 +0200 )edit

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.

Lupp gravatar imageLupp ( 2019-10-25 19:13:38 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-10-24 15:32:31 +0200

Seen: 1,307 times

Last updated: Oct 29 '19