Copy a range of Excel cells to an "image" object by VBA

2

I need to copy a strip from an Excel spreadsheet that contains a graphic layout made with borders around some cells, with some background colors and text, and visualize this layout in a VBA image.

With the code below the cell range that makes up the figure is copied from the current spreadsheet to another one called "Image", and is copied as an image (as needed), but I could not find a way to place it in the " image ", or paste it directly into the" image "object (so the" Image "worksheet would be unnecessary).

Range("D19:H25").Select

Application.CutCopyMode = False

Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap

Sheets("Imagem").Activate

Range("A1").Select

ActiveSheet.Paste

Inanattempttofindasolution,bymanuallyselectingthecellscontainingtheimage,andin"Home", "Copy as image" (appearance: as shown on the screen, and format: Bitmap); when you click on properties of the "image" object in the "Picture" field where "none" is described and paste in this field, the word "Bitmap" appears and the image "image" appears as desired.

I've tried and researched this same procedure in VBA, including doing it through macro and parsing the code, but evidently the part code that the deviation is done for VBA to "paste" the selected track into the VBA property object is not registered in the macro.

As the graphic schema is dynamically changed by the user (via VBA), whenever the user enters a certain form this "image" object needs to display the current image, how often the user wants to refine this image.

How do I resolve this the way I need it?

    
asked by anonymous 01.02.2016 / 20:21

1 answer

3

Just delete the previous image (saving the position before it) and update the new paste with the same name and in the same position. Here is the code:

Set oImage = ActiveSheet.Shapes.Item("Imagem 1")
x = oImage.Left
y = oImage.Top
oImage.Delete

' Seu código
' -------------
Range("e7:E9").Select

Application.CutCopyMode = False

Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap

ActiveSheet.Paste Destination:=ActiveSheet.Range("J19:N25")
' -------------

ActiveSheet.Shapes.Item(ActiveSheet.Shapes.Count).Name = "Imagem 1"
Set oImage = ActiveSheet.Shapes.Item("Imagem 1")
oImage.Left = x
oImage.Top = y
  

IMPORTANT: Note that in this answer I only show how to update   an existing image with a new image generated from the data   of a selected range. In your other question , my answer   demonstrates how to write this image (not the range!) to a file. At the   The final questions are not duplicated but have been   You do not need to have two to solve your problem. If you had   been more organized when asked, would have avoided all small   confusions and had their response more quickly. The tip is pro   future. :)

    
02.02.2016 / 03:16