Using VBA to insert and keep images in file - Excel 2013

Question

I'm working on a macro for a friend of mine who needs to import a set of images in an excel document and later use this document on other computers. The problem I encountered is that when opening this document on a different computer, all the images are gone and instead you get these little error signs, indicating that the image path could not be found.

I have developed the macro on my own computer where I have Excel 2007 and for me, the code works perfectly fine. My friend uses Excel 2013 and apparently, there seems to be a major difference on how those 2 versions deal with the image importing and saving.

Overall, I found 2 different ways how to insert images. The first one I tried was something similar to this:

Set pic = ActiveSheet.Pictures.Insert("C:\documents\somepicture.jpg")

The second way of doing this looked like this:

Set pic = Application.ActiveSheet.Shapes.AddPicture("C:\documents\somepicture.jpg", False, True, 1, 1, 1, 1)

In the documentation for this 2nd approach it is said that the 3rd paramenter (which is True here) is responsible for saving the picture with the document.

However, both these approaches look more or less the same in the end result: They work fine for me but won't work if they are executed on my friends pc with Excel 2013. So what I need is a work-around for the newer Excel versions (I read somewhere that from Excel 2010 upwards, there is a bug or something like that with these image import methods).

Answer 1

The first snippet works just fine, but it does not allow picture positioning (i.e. if you need a pic placed at some certain range), so I made something that works nicely with positioning available, based on the second solution, such as it is shown below.

Dim r As Range
Dim pic As Shape
Set r = ActiveSheet.Range("A34:Q58")
Set pic = ActiveSheet.Shapes.AddPicture(ThisWorkbook.Path & "\FracAnalysis.png", _
linktofile:=msoFalse, savewithdocument:=msoCTrue, Left:=0, Top:=0, Width:=-1, Height:=-1)
With pic
   .LockAspectRatio = msoFalse
   .Top = r.Top
   .Left = r.Left
   .Width = r.Width
   .Height = r.Height
End with