CODEWITHSUNDEEP
excelvbaimage
Horácio Silva
Horácio Silva

Reputation: 33

Copy image from one workbook to another workbook

I have an image in a sheet1 in a merged cell range S1:V8.

I don't know the name of this picture because we paste different pictures in the area each time we create a new Excel file from a template.

I want to copy the picture in this range in this workbook or another workbook, to a sheet called "Database" into cell A6.

I've found several examples but we must know the picture name.

I want the first picture. I imagine I can check if the range has pictures. Then select the first picture and paste it in the other workbook.

The only thing I can't do is to select the picture inside the range for copying.

Bonus features if possible:
- After that I want to resize it to the cell dimension, but maintaining the aspect ratio
- After I want to compress it to 96 dpi
- This file will have several pictures. Is there a way to resize them all to each cell were they are in case of someone modifies the pictures dimension? (there will be a picture in Cell A6, another in cell A7,..., and someone could change the size of the picture in A20 for example)

This post is also in the MrExcel forum.

enter image description here

Upvotes: 2

Views: 3701

Answers (1)

Tony M
Tony M

Reputation: 1762

It's not clear whether the picture you are referring to is "in the cell" or floating above the cells. As far as I know, the only way to have a picture "in a cell" is to have it within the comment of the cell (actually, intended as a background picture for comment text). In either case, the key to manipulating it using VBA would be to get a reference for it. The following code will identify the presence of either of the above cases if you first select the region of cells, as shown in the animated gif. Unfortunately, as far as I know, all you can do is add background pictures to comments via VBA. enter image description here

Option Explicit
Sub testForPicturesOrComments()
Dim p As Picture, r As Range, hasComment As Boolean
For Each p In ActiveSheet.Pictures
  MsgBox ("There's a picture called " & p.Name)
Next p
For Each r In Selection
  On Error Resume Next
  hasComment = r.Comment.Parent.Address = r.Address
  'Reset Run Time Errors
  On Error GoTo 0
  If hasComment Then
  MsgBox ("There's a comment in " & r.Address _
  & " with a shape.ID = " & r.Comment.Shape.ID)
    hasComment = False
  End If
Next r
End Sub

Upvotes: 1

Related Questions