在Excel中更改图片链接的路径 [英] Change Path to Picture Links in Excel
问题描述
我使用插入图片->选择图片位置->链接到文件,将大量链接的图片手动粘贴到2010 Excel电子表格中.这些图片是报告的一部分.我每季度使用R更新图片,并且我的报告会自动更新.完美.
I have manually pasted a large number of linked pictures into a 2010 Excel spreadsheet using insert picture -> select picture location --> link to file. These pictures are part of a report. I update the pictures using R each quarter, and my report automatically updates. Perfect.
我现在需要更改保存图表的目录,并需要更新链接.由于其中大约有200个(这是一个大报告),我想在VBA中做到这一点.虽然我可以正常浏览图片(ActiveSheet.Pictures),但似乎找不到链接/地址.知道如何查看基础文件位置,以便我可以更改它-引用必须存储在某个位置(请注意-这些似乎不存储为超链接).
I now need to change the directory where the plots are kept, and need to update the links. As there are around 200 of them (its a big report), I want to do this in VBA. Whilst I can loop through the pictures ok (ActiveSheet.Pictures), I can't seem to find the links/address. Any idea how I can see the underlying file location so I might change it - the reference has to be stored somewhere (note - these don't seem to be stored as hyperlinks).
推荐答案
任何想法我都可以看到底层文件的位置,以便我可以更改它-引用必须存储在某个地方
Any idea how I can see the underlying file location so I might change it - the reference has to be stored somewhere
- 创建一个新文件夹
- 粘贴.xlsx或.xlsm excel文件的副本
- 使用zip工具解压缩文件(我使用的是7-Zip)
- 删除.xlsx或.xlsm文件(可选)
- 现在我们拥有原始文件的所有组成部分,即纯文本xml文件和文件夹
- 在文件夹xl \ drawings \ _rels中,有一些文件名为drawing2.xml.rels,drawing3.xml.rels,...
似乎每个文件都对应一张纸,并以这种格式存储图像的路径:
It seems that each file corresponds to a sheet and stores the paths to images in this format:
Target ="file:///C:\ Users \ myusername \ Documents \ MyImageFolder \ My%20Image%20Name.png"
Target="file:///C:\Users\myusername\Documents\MyImageFolder\My%20Image%20Name.png"
- 使用文本编辑器更改路径
- 将文件夹的所有内容压缩为.zip
- 将扩展名更改为原始.xlsx或.xlsm
这些步骤可以通过VBA,AutoIt等自动执行,此处提供一些参考资料:
These steps could be automated with VBA, AutoIt, etc., here some references:
- AutoIt和7-zip 的示例
- http://www.jkp-ads.com/Articles/Excel2007FileFormat.asp
- http://www.jkp-ads.com/Articles/Excel2007FileFormat02.asp
- Ron de Bruin带有VBA的zip示例
- 在Excel中读取和更改多个XML文件( 2007)VBA
- An example with AutoIt and 7-zip
- http://www.jkp-ads.com/Articles/Excel2007FileFormat.asp
- http://www.jkp-ads.com/Articles/Excel2007FileFormat02.asp
- Ron de Bruin zip examples with VBA
- Read and change multiple XML files in Excel (2007) VBA
这篇关于在Excel中更改图片链接的路径的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!