Inserting an image to Excel with VBA is actually one line:
ThisWorkbook.Worksheets(1).Pictures.Insert ("C:\Users\myPic.png")
The interesting part happens, when it should be correctly positioned. In order to position it the way we want, we need to give 3 parameters – the image top position, the image left position and the width (or the height) of the image. If the width is given, then the height is calculated, thus keeping the image scale:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
Sub Main() KillAllShapes Worksheets(1) ThisWorkbook.Worksheets(1).Pictures.Insert ("C:\Users\gropc\Desktop\aa\myPic.png") Dim myShape As Shape Set myShape = Worksheets(1).Shapes(1) With Worksheets(1) myShape.Top = .Range("B5").Top myShape.Left = .Range("B5").Left myShape.Width = .Range("B5:D5").Width 'myShape.Height = .Range("B5:B7").Height myShape.IncrementRotation 360 'or 90 End With End Sub Sub KillAllShapes(wks As Worksheet) Dim sh As Shape For Each sh In wks.Shapes sh.Delete Next End Sub |
The KillAllShapes part of the code deletes all shapes from the worksheet, given as parameter. Then, using .Top , .Left and .Width the image is positioned correctly, as per the location of the mentioned Ranges. The myShape.IncrementRotation 360 rotates the image into 360 degrees, thus keeping it as it was. Anyway, it could be changed to 90, 180 or 270, depending on the requirements:
That’s all, folks!