11

xlApp.activesheet.Pictures.Insert(strImagePath) inserts pictures into a spreadsheet as a linked picture. If I send the spreadsheet out of our network the images fail.

How can I place an image as an embedded image?

I am also calling this method from Access.

braX
  • 11,506
  • 5
  • 20
  • 33
DasPete
  • 831
  • 2
  • 18
  • 37

3 Answers3

22

you can use the shapes.addpicture method

activesheet.Shapes.AddPicture Filename:="C:\test\desert.jpg", linktofile:=msoFalse, _
            savewithdocument:=msoCTrue, Left:=0, Top:=0, Width:=100, Height:=100
JosieP
  • 3,360
  • 1
  • 13
  • 16
  • Thanks @JosieP! This works great. Now I am trying to assign it to a Shape object so I can make some adjustments. I did `Dim shpPic as Shape` and `Set shpPic = xlApp.activesheet.Shapes.AddPicture ...` and I get a type mismatch error. Any idea why? – DasPete Jun 14 '13 at 15:30
  • based on the xlApp part I guess you're automating from another app-does that app have Shape objects? if so use `dim shpPic as Excel.Shape` – JosieP Jun 14 '13 at 15:43
  • Hi @JosieP, yes I'm calling from Access. I can't use `Excel.Shape` because I don't want to have a reference to the Excel library because I have users with a large range of systems. I declared my excel instance like this: `Dim xlApp as Object` `Set xlApp = CreateObject("Excel.Application")` So I tried `Dim shpPic = xlApp.Shape` and that gives me "User-defined type not defined" error. Any ideas? – DasPete Jun 14 '13 at 15:50
  • you've gotta declare as Object if you're late binding excel – JosieP Jun 14 '13 at 15:51
  • Oh ok, thanks. I'm not very familiar with late binding. So would I declare like this: `Dim shpPic As Object` `Set shpPic = xlApp.Shape`? I tried that and it said "Object doesn't support this property or method" – DasPete Jun 14 '13 at 15:54
  • 2
    no you `Dim shpPic as Object` then use the `set shpPic = xlapp.activesheet.shapes.addpicture...` – JosieP Jun 14 '13 at 16:08
  • Note that you can set the required Width and Height parameters to -1, which then maintains the height and width of the original image! – Michael Jan 06 '18 at 10:05
1

Note that you can set the required Width and Height parameters to -1, which then maintains the height and width of the original image!

Activesheet.Shapes.AddPicture Filename:="C:\image.jpg", LinkToFile:=msoFalse, _
        SaveWithDocument:=msoTrue, Left:=0, Top:=0, Width:=-1, Height:=-1

http://excelmatters.com/2013/11/25/default-picture-size-with-shapes-addpicture/

(Added as another answer to increase visibility as I've struggled with this problem for ages and haven't found this solution documented anywhere else.)

Michael
  • 4,563
  • 2
  • 11
  • 25
-1
Activesheet.Shapes.AddPicture Filename:="C:\image.jpg", LinkToFile:=msoFalse, _
        SaveWithDocument:=msoTrue, Left:=0, Top:=0, Width:=-1, Height:=-1

this works, maybe the following code can help someone too (it helped me) this is how you select the image you've just added:

ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Select
AG9
  • 1
  • 1