excel 外部链接乱码_在Excel文件中查找外部链接

最新推荐文章于 2024-01-22 17:25:22 发布

culiao2169

最新推荐文章于 2024-01-22 17:25:22 发布

阅读量1.6k

点赞数

文章标签： java linux python excel mysql

原文链接：https://contexturesblog.com/archives/2013/11/12/find-external-links-in-an-excel-file/

版权

当打开带有外部链接的Excel文件时，可能会出现安全警告。可以通过手动检查公式、编辑链接、复制粘贴为值等方法来查找并去除这些链接。手动修复包括检查公式中的链接、删除不需要的链接、检查工作簿名称列表、检查分配给按钮的宏等步骤，以确保文件安全无误。

摘要由CSDN通过智能技术生成

excel 外部链接乱码

Last week, I opened an Excel file, and was surprised to see a Security Warning. Apparently there were external links in the file, and I didn’t want to keep them. Here's how you can find external links in an Excel file.

上周，我打开了一个Excel文件，并惊讶地看到一个安全警告。显然，文件中有外部链接，我不想保留它们。这是在Excel文件中查找外部链接的方法。

This screen shot shows the warning that I saw, when I opened the file.

此屏幕快照显示了打开文件时看到的警告。

To look for the links, you can use a free add-in – there is a link at the end of this article. Or, go through the file yourself, and try to fix the links.

要查找链接，可以使用免费的加载项–本文结尾处有一个链接。或者，您自己浏览文件，然后尝试修复链接。

手动修复 (The Manual Fix)

For reasons that I can’t remember I decided to to a manual search for the links. First, I made a backup copy of the file – just in case things went wrong. Not that anything ever goes wrong in Excel!

由于我不记得的原因，我决定手动搜索链接。首先，我制作了文件的备份副本，以防万一出错。在Excel中，没有任何错误！

Then, I opened the file again, clicked the Enable Content button, and started my search for those unwanted links.

然后，我再次打开文件，单击“启用内容”按钮，然后开始搜索那些不需要的链接。

看公式 (Look in the Formulas)

The first place I looked was in the workbook’s formulas. Maybe I had copied a formula from another workbook, and it was still linking back to that file. If that file is referenced in a formula, its name is enclosed in square brackets, so I used the Find command to look for those.

我首先看到的是工作簿的公式。也许我已经从另一个工作簿中复制了一个公式，但它仍旧链接回该文件。如果在公式中引用了该文件，则其名称括在方括号中，因此我使用“查找”命令来查找那些文件。

When I clicked the Find All button, Excel showed me a list of 18 cells that had square brackets. I clicked on the first item, and it had a reference to another file, so I’ll be able to fix that.

当我单击“查找全部”按钮时，Excel向我显示了带有方括号的18个单元格的列表。我单击了第一项，它具有对另一个文件的引用，因此我将能够修复该问题。

There were other cells with square brackets that aren’t external links, so I ignored those. Excel table references use square brackets too, like this formula:

还有其他带有方括号的单元格不是外部链接，因此我将其忽略。 Excel表引用也使用方括号，例如以下公式：

=[@Amt]*0.07

= [@ Amt] * 0.07

It might be easier to search for “.xls” now, if the workbook has formatted tables.

如果工作簿已格式化表格，现在搜索“ .xls”可能会更容易。

修正公式 (Fix the Formulas)

The formula that I want to fix is looking up prices in another workbook’s pricing table.

我要修正的公式是在另一个工作簿的定价表中查找价格。

=VLOOKUP(C2,'D:\BlogCtx\TestFiles\[DateAmts.xlsx]Prices'!$B$2:$C$5,2,FALSE)

= VLOOKUP(C2，'D：\ BlogCtx \ TestFiles \ [DateAmts.xlsx] Prices'！$ B $ 2：$ C $ 5,2，FALSE)

I wanted to get rid of the links, and change those cells to values, so I followed these steps, to break those links:

我想摆脱链接，并将这些单元格更改为值，所以我按照以下步骤操作，以断开那些链接：

On the Ribbon, click the Data tab
在功能区上，单击“数据”选项卡
In the Connections group, click Edit Links
在“连接”组中，单击“编辑链接”。
Select the link that you want to break, and click Break Link.
选择要断开的链接，然后单击“断开链接”。
A warning message will appear, so read it carefully – especially the part about making a backup copy. Then, click Break Links if you’re sure that’s what you want to do.
将出现一条警告消息，因此请仔细阅读-特别是有关制作备份副本的部分。然后，如果确定您要这样做，请单击“断开链接”。

Unfortunately, that didn’t change anything – the links were still in the formula.

不幸的是，那并没有改变任何东西–链接仍然在公式中。

复制并粘贴为值 (Copy and Paste As Values)

The cells were all in one column in a table, so I decided to change them to values by copying them, then pasting as values.

单元格都在表的一列中，因此我决定通过复制它们，然后将其粘贴为值来将其更改为值。

That got rid of the link in those cells – now they just contain numbers, not links.

这样就消除了这些单元格中的链接-现在它们仅包含数字，而不包含链接。

The Edit Links window was still showing a link to that workbook though, so maybe one of the workbook names had been copied too.

但是，“编辑链接”窗口仍显示到该工作簿的链接，因此也许其中一个工作簿名称也已被复制。

检查名称列表 (Check the Names List)

To see then names, click the Formulas tab on the Ribbon, then click Name Manager. In the Refers To column, you might see references to other workbooks. You can delete those names, or change them, so they refer to ranges in the active workbook.

要查看名称，请单击功能区上的“公式”选项卡，然后单击“名称管理器”。在“引用到”列中，您可能会看到对其他工作簿的引用。您可以删除或更改这些名称，以便它们引用活动工作簿中的范围。

I didn’t need the Product name or LocationList (I hope!), so I deleted them. I’ll check on the LocationList name later.

我不需要产品名称或LocationList(我希望！)，所以我删除了它们。稍后我将检查LocationList名称。

When I checked the Edit Links window again, the link to the DateAmts.xlsx file was gone. Hooray!

当我再次检查“编辑链接”窗口时，指向DateAmts.xlsx文件的链接消失了。万岁！

However, there was still a link to another workbook – PartLocDBCombo.xls. I had deleted the LocationList, which referred to that workbook, so where could that link be?

但是，仍然有指向另一个工作簿的链接– PartLocDBCombo.xls。我已经删除了引用该工作簿的LocationList，那么该链接在哪里？

检查按钮 (Check the Buttons)

Another place that links can hide is in the macros that are assigned to buttons. This workbook had 2 buttons, so I checked those next.

链接可以隐藏的另一个地方是分配给按钮的宏。该工作簿有2个按钮，因此我接下来检查了这两个按钮。

I right-clicked on the first button, and clicked Assign Macro, to see the macro that the button runs. In this example, it was still trying to run the macro in the PartLocDBCombo.xls workbook.

我右键单击第一个按钮，然后单击“分配宏”，以查看按钮运行的宏。在此示例中，它仍试图在PartLocDBCombo.xls工作簿中运行宏。

So, I selected a macro in the current workbook, for each button, to get rid of those links.

因此，我在当前工作簿中为每个按钮选择了一个宏，以摆脱那些链接。

关闭并重新打开文件 (Close and Reopen the File)

The Edit Links window still showed a link to PartLocDBCombo.xls, but I didn’t know of any other places to look. So, I saved the workbook, then closed and reopened it, and that got rid of the “ghost” link.

“编辑链接”窗口仍显示指向PartLocDBCombo.xls的链接，但我不知道要查找其他任何地方。因此，我保存了工作簿，然后关闭并重新打开它，从而摆脱了“鬼影”链接。

It can be a slow and frustrating process to remove external links, so be careful what you copy into a workbook!

删除外部链接可能是一个缓慢而令人沮丧的过程，因此请小心复制到工作簿中！

使用查找链接加载项 (Use the Find Link Add-in)

For an automated way of checking for links, you can download Bill Manville's free FINDLINK.XLA program, from his website.

对于自动检查链接的方法，您可以从他的网站下载Bill Manville的免费FINDLINK.XLA程序。

After you install the add-in, it will appear as a menu item on the Ribbon’s Add-Ins tab. Click the Find Links command, to open the Link Finder window.

安装外接程序后，它将作为菜单项显示在功能区的“外接程序”选项卡上。单击“查找链接”命令，以打开“链接查找器”窗口。

Select a file name from the drop down list, then select one of the options for working with those links.

从下拉列表中选择一个文件名，然后选择用于处理这些链接的选项之一。