Using VBA to insert and keep images in file - Exce

问题:

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).

回答1:

In all my uses, Adding a picture with Insert makes a reference to a file on your harddrive, for whatever reason if you want the image to be embedded in the file you have to add a shape and then put the image on the shape using the AddPicture (like you use), I have never had any issues with this.

Also you are giving the picture a height and width of 1 pixel, You will almost never be able to see that true setting that higher as below:

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

I have a feeling it was working all along and you just never saw the picture cause it was too small.

回答2:

Previous answer has been really useful! I just wanted to add the reference to the method parameters (I thought the width and height were in pixels, turns out they're in points):

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

回答3:

I usually run a macro that downloads images from a server into files that are then forwarded to clients who do not have access to that server. My coding is quite basic so I'll just copy the particular line I use to insert the picture:

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 proposed, but try using msoCTrue and msoFalse. I seem to recall that being part of the issues. Let me know if it works and maybe we can try something else. It works for me, so we should be able to get it to work for you.

回答4:

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 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