vba 如何将多个excel文件链接到一个主文件，以便如果我更新单个主文件，所有链接的文件也会更新？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6043671/
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
How to link many excel files to one master file, so that if I update the single master file, all linked files also update?
提问by ade123
I'm wondering if anybody can help?
我想知道是否有人可以帮忙？
I have a large number of excel files. These are commission statements and some are converted from USD to UK Sterling, from UK sterling to Euros etc etc.
我有大量的excel文件。这些是佣金报表，有些是从美元转换为英镑，从英镑转换为欧元等。
They are quarterly statemnets and at present I have to go through each file one by one and enter in the specific currency conversion rates for that month.
它们是季度报表，目前我必须逐个查看每个文件并输入当月的特定货币兑换率。
I was thinking it would be great if I could link all these statements to a single excel file which contained the conversion rates. I could then update this single currency conversion file and all the other files would update.
我在想，如果我可以将所有这些语句链接到一个包含转换率的单个 Excel 文件，那就太好了。然后我可以更新这个单一的货币转换文件，所有其他文件都会更新。
I think I could manage this, but there is a little problem I have ran into.
我想我可以解决这个问题，但是我遇到了一个小问题。
If I then send the statement files out to individuals, the local link between the two files would be lost and therefore, surly so would the conversion figures. The only fix I can see would be to send the file containing the conversion rates along with the statement, but if possible I would prefer not to do this.
如果我随后将报表文件发送给个人，这两个文件之间的本地链接将丢失，因此转换数字也将丢失。我能看到的唯一解决方法是将包含转换率的文件与语句一起发送，但如果可能的话，我不想这样做。
I hope you can understand my problem and it would be great if anybody could suggest any fixes to this!
我希望您能理解我的问题，如果有人能对此提出任何修复建议，那就太好了！
Thanks in advance :o)
提前致谢:o)
I have had some advice here about including a hidden rates sheet in all excel files:
我在这里有一些关于在所有 excel 文件中包含隐藏费率表的建议：
Linking cells from 2 different excel files, but then keeping figures if emailing only one file?
链接来自 2 个不同 excel 文件的单元格，但如果仅通过电子邮件发送一个文件，则保留数字？
The rates within the visible sheets could then be linked to the cells in the hidden rates sheet and then the hidden rates sheet could be linked to a seperate single master rates file, which I would alter to update rates on all linked excel files.
然后可以将可见表中的费率链接到隐藏费率表中的单元格，然后隐藏费率表可以链接到单独的单个主费率文件，我将更改该文件以更新所有链接的 excel 文件的费率。
I' still a bit unsure on how this would work if I was to send a single excel file to people in an email, because the local link to the master rates file would be lost. Is there a work arround?
如果我要通过电子邮件向人们发送单个 Excel 文件，我仍然有点不确定这将如何工作，因为主汇率文件的本地链接将丢失。附近有工作吗？
Any help would be really really appreciated!
任何帮助将非常感激！
回答by Robert Mearns
One approach is to put your master conversion rates workbook on the web
一种方法是将您的主要转化率工作簿放在网络上
Your statement workbooks can the reference master workbook on the web via formulas. For example:
您的报表工作簿可以通过公式引用网络上的主工作簿。例如：
='http://www.your-site.com/test/[Workbook1.xls]Sheet1'!A1
Whilst this approach is fun, I would recommend using a master Excel conversion rates workbook on your network.
虽然这种方法很有趣，但我建议在您的网络上使用主 Excel 转换率工作簿。
The statement workbooks would link to this master conversion rates workbook to get the rates.
报表工作簿将链接到此主兑换率工作簿以获取汇率。
Before sending the statements out, I would run some VBA code to loop through the workbooks.
在发送语句之前，我会运行一些 VBA 代码来循环遍历工作簿。
The code would open the workbook, update the link and then break it.
该代码将打开工作簿，更新链接，然后断开它。
The value that would be left in the linked cells is the latest updated value.
留在链接单元格中的值是最新更新的值。
The code below updates and breaks all links in a workbook.
下面的代码更新并断开工作簿中的所有链接。
Sub Update_And_BreakLinks()
Dim vLinks As Variant
Dim lLinks As Long
vLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
'Get a list of all the links in the workbook
If IsEmpty(vLinks) Then Exit Sub
'If no links then exit
For lLink = LBound(vLinks) To UBound(vLinks)
ActiveWorkbook.UpdateLink Name:=vLinks(lLink), Type:=xlLinkTypeExcelLinks
Next lLink
'Update links
For lLink = LBound(vLinks) To UBound(vLinks)
ActiveWorkbook.BreakLink Name:=vLinks(lLink), Type:=xlLinkTypeExcelLinks
Next lLink
'Break the links. The link is replaced with the value from the last update
End Sub
Look at this question on how to loop through workbooks in a folder
看看这个关于如何遍历文件夹中的工作簿的问题
