使用 VBA 在文件中插入和保留图像 - Excel 2013
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19908538/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using VBA to insert and keep images in file - Excel 2013
提问by FlyingM
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.
我正在为我的一个朋友开发一个宏，他需要在 excel 文档中导入一组图像，然后在其他计算机上使用该文档。我遇到的问题是，在另一台计算机上打开此文档时，所有图像都消失了，而是出现这些小错误标志，表明找不到图像路径。
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.
我在自己的电脑上开发了这个宏，我有 Excel 2007，对我来说，代码工作得很好。我的朋友使用 Excel 2013，显然，这两个版本处理图像导入和保存的方式似乎存在重大差异。
Overall, I found 2 different ways how to insert images. The first one I tried was something similar to this:
总的来说，我发现了 2 种不同的插入图像的方法。我尝试的第一个类似于以下内容：
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.
在此第二种方法的文档中，据说第三个参数（此处为 True）负责将图片与文档一起保存。
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).
但是，这两种方法在最终结果中看起来或多或少相同：它们对我来说很好用，但如果在我朋友的 pc 上使用 Excel 2013 执行它们就行不通了。所以我需要的是更新的解决方法Excel 版本（我从 Excel 2010 以上的某个地方读到过，这些图像导入方法存在错误或类似问题）。
回答by user2140261
In all my uses, Adding a picture with Insertmakes 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.
在我的所有用途中，添加图片Insert会引用硬盘驱动器上的文件，无论出于何种原因，如果您希望将图像嵌入到文件中，您必须添加一个形状，然后使用AddPicture(就像你使用的一样），我从来没有遇到过任何问题。
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:
此外，您为图片提供了 1 像素的高度和宽度，您几乎永远无法看到更高的真实设置，如下所示：
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.
我有一种感觉它一直在工作，你只是从未看到过这张照片，因为它太小了。
回答by Alexandre Madurell
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
https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.shapes.addpicture.ASPX
回答by Alan Morrison Fell
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.
我知道它在技术上与您提出的代码相同，但请尝试使用 msoCTrue 和 msoFalse。我似乎记得这是问题的一部分。让我知道它是否有效，也许我们可以尝试其他方法。它对我有用，所以我们应该能够让它为你工作。
回答by Андрей Романов
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
