Using VBA to insert and save images in a file - Excel 2013

Question

I am working on a macro for my friend who needs to import a set of images into an excel document and then use this document on other computers. The problem that I encountered is that when I open this document on another computer, all the images disappear, and instead you get these small error signs, indicating that the path to the image was not found.

I developed a macro on my own computer, where I have Excel 2007, and for me the code works fine. My friend uses Excel 2013 and it seems that there is a big difference in how the two versions relate to import and save images.

In general, I found two different ways to insert images. The first one I tried was something like this:

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

The second way to do this is as follows:

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

The documentation for this second approach says that the 3rd paragraph (which is True here) is responsible for saving the image with the document.

However, both of these approaches look more or less the same in the end result: they work fine for me, but will not work if they run on my friends computer from Excel 2013. So I need to work for new versions of Excel (I’m somewhere read that from Excel 2010 up, there is an error or something similar with these image import methods).

Answer 1

In all my applications, Adding an image with Insert makes a link to a file on your hard drive, for any reason, if you want the image to be embedded in the file, you need to add a shape and then put the image on the shape using AddPicture (like you use), I have never had any problems with this.

Also, you give the image a height and width of 1 pixel, you will almost never be able to see this true setting higher than shown below:

 Application.ActiveSheet.Shapes.AddPicture "C:\documents\somepicture.jpg", False, True, 1, 1, 100, 100 

I have a feeling that it worked all the time, and you just never saw that the image was too small.

Answer 2

The previous answer was really helpful! I just wanted to add a link to the method parameters (I thought that the width and height were in pixels, it turns out they are in points):

https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.shapes.addpicture.ASPX

Answer 3

Usually I run a macro that downloads images from the server to files, which are then sent to clients who do not have access to this server. My coding is pretty simple, so I just copy the specific line that I use to paste the image:

 Set pic = ActiveSheet.Shapes.AddPicture(Filename:="C:\documents\somepicture.jpg", _ linktofile:=msoFalse, savewithdocument:=msoCTrue, Left:=0, Top:=0, Width:=-1, Height:=-1) 

I know its technically the same code as the one you suggested, but try using msoCTrue and msoFalse. I seem to recall that this is part of the questions. Let me know if this works, and maybe we can try something else. This works for me, so we should be able to get it to work for you.

Answer 4

The first fragment works fine, but does not allow the image to be positioned (i.e. if you need the image to be placed in a certain range), so I did something that works well with available positioning, based on a second solution, such as shown below.

 Dim r As Range Dim pic As Range 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) pic.Select Selection.ShapeRange.LockAspectRatio = msoFalse Selection.Top = r.Top Selection.Left = r.Left Selection.Width = r.Width Selection.Height = r.Height 

Answer 5

I work with a form in which the user can select any picture to add to a specific cell through the dialog box (Application.FileDialog (msoFileDialogFilePicker)), but I also have a problem opening files on different computers.

Can this be fixed and continue to use the dialog box?

The code is shown below:

 Sub InsertImage() With Application.FileDialog(msoFileDialogFilePicker) .AllowMultiSelect = False .ButtonName = "Submit" .Title = "Select an image file" .Filters.Clear .Filters.Add "JPG", "*.JPG" .Filters.Add "JPEG File Interchange Format", "*.JPEG" .Filters.Add "Graphics Interchange Format", "*.GIF" .Filters.Add "Portable Network Graphics", "*.PNG" .Filters.Add "Tag Image File Format", "*.TIFF" .Filters.Add "All Pictures", "*.*" If .Show = -1 Then Dim img As Object Set img = ActiveSheet.Pictures.Insert(.SelectedItems(1)) End If End With End Sub