以编程方式更新 MS Word 中 Excel 对象的链接命名范围 (2007) [英] Programmatically Update Linked Named Range of excel object in MS Word (2007)

问题描述

第一个问题，如果已经解决了，请见谅，但我已经彻底搜索并没有找到答案:

First question, excuse me if this has already been solved, but I've searched thoroughly and cannot find an answer:

我已将多个命名范围链接到一个 Word 文档中.这个word doc(以及相关的具有命名范围的excel工作簿)是一个模板:它是为一个同事准备的，他将制作这些模板的许多副本(word doc和excel工作簿).

I have linked several named ranges into a word document. This word doc (and the related excel workbook with named ranges) is a template: it's for a coworker who will make many copies of these templates (of both the word doc and the excel workbook).

我想在 word doc 中包含一个命令按钮，单击该按钮时，将更新链接命名范围的源.具体来说，我希望它将与 worddoc 同名的工作簿设置为源.

I would like to include a command button in the word doc that, when clicked, will update the sources for the linked named ranges. Specifically, I want it to set the workbook with the same name as the worddoc, as the source.

问题是它不喜欢我输入的命名范围.我明白了:

The issue is that it does not like the named range I have entered. I get the:

运行时错误6083":本文档中的对象包含指向无法找到的文件的链接.链接信息不会更新.`

Run-time error '6083': Objects in this document contain links to files that cannot be found. The linked information will not be updated.`

但是，我已经对我的 excel 文档进行了四次检查，命名范围存在.而且，当我在 word 中按 Alt+F9 时，我清楚地看到链接包含命名范围！

However, I have quadrupled-checked my excel doc, the named range exists. AND, when I hit Alt+F9 in word, I clearly see the link contains the named range!

{LINK Excel.Sheet.8 C:\Users\Marc\Documents\WIP_SSS.xlsm CED \a \p}

{LINK Excel.Sheet.8 C:\Users\Marc\Documents\WIP_SSS.xlsm CED \a \p}

这是我的代码:

Public Sub ChangeSource()
Dim filename As Variant
Dim fieldcount As Integer
Dim x As Integer

filename = Left(Application.ActiveDocument.Name, Len(Application.ActiveDocument.Name) - 4) & "xlsm"
fieldcount = ActiveDocument.Fields.Count

For x = 1 To fieldcount
'Debug.Print ActiveDocument.Fields(x).Type
If ActiveDocument.Fields(x).Type = 56 Then
    ActiveDocument.Fields(x).LinkFormat.SourceFullName = ActiveDocument.Path & "\" & _
    filename & "!CED"
End If
Next x

End Sub

如果我根本不输入命名范围，宏就可以工作，但它嵌入了整个 excel 工作表(我不希望它这样做).关于如何/为什么不喜欢命名范围的任何想法?

If I don't enter the named range at all, the macro works, but it embeds the entire excel worksheet (which I do not want it to do). Any ideas on how/ why it is not liking the named range?

谢谢，马克

更新:在 Bibadia 的帮助下，我找到了解决方案；此外，我想记录 Word VBA 表现出的一些奇怪行为:

UPDATE: With help from Bibadia, I found a solution; in addition, I want to document some strange behavior exhibited by Word VBA:

首先，解决方案代码:

Public Sub ChangeSource()
Dim filename As Variant
Dim fieldcount As Integer
Dim x As Integer

filename = ThisDocument.Path & "\" & Left(Application.ActiveDocument.Name, Len(Application.ActiveDocument.Name) - 4) & "xlsm"
fieldcount = ActiveDocument.Fields.Count
For x = 1 To fieldcount
On Error Resume Next
    If ActiveDocument.Fields(x).Type = 56 Then
        ActiveDocument.Fields(x).Delete
    End If
Next x

ActiveDocument.Bookmarks("R1").Range.InlineShapes.AddOLEObject filename:=filename & "!Range1", LinkToFile:=True
End Sub

我首先删除了所有类型为 56 的字段(链接对象，或者更专业地说，wdfieldlinked").然后，我在预设的书签位置添加了 OLEObjects.有趣的是，正如 Bibadia 指出的，关键是输入 LinkToFile:=True 代码.如果对象被嵌入，Word 似乎不会接受该对象:如果我删除该行，我会收到错误 Word 无法获取 C:\...\document!NamedRange 链接的数据.

I first deleted all type 56 fields (linked object, or more technically, "wdfieldlinked"). Then, I added OLEObjects at pre-set bookmark locations. Interestingly, just as Bibadia noted, the key was to input the LinkToFile:=True code. It seems Word will not accept the object if it is embedded: if I remove that line, I get the error Word Cannot obtain the data for the C:\...\document!NamedRange link.

最后，我发现了另一种奇怪的行为:尝试简单地替换链接时，使用此代码，

Finally, I found one other odd behavior: When trying to simply replace the link, using this code,

ActiveDocument.Fields(1).LinkFormat.SourceFullName = filepath+name & _
"!CED" 'that is the named range

当我更改 word 文档和 Excel 工作簿的文件名时，它会起作用一次(有关上下文，请参阅原始消息).因此，当新文件路径+名称与现有文件路径+名称不匹配时，Word VBA 接受了更改.但是，一旦最初更新，如果我再次尝试运行宏，我会得到:

it would work once, when I changed both the word document's and the excel workbook's filenames (see original message for context). So, when the new filepath+name DID NOT match the existing filepath+name, Word VBA accepted the change. However, once initially updated, if I tried to run the macro again, I would get:

运行时错误6083":本文档中的对象包含指向无法找到的文件的链接.链接信息不会更新.

run-time error '6083': Objects in this document contain links to files that cannot be found. The linked information will not be updated.

即使我将命名范围更改为同一工作表(显然是同一工作簿)中的另一个命名范围，我也会收到此错误.因此，当文件路径+名称未更改时，Word VBA 似乎不喜欢更新"文件路径+名称.

I would get this error even if I changed the named range to another named range in the same worksheet (and obviously same workbook). So it appears that Word VBA does not like "updating" filepath+name when the filepath+name does not change.

所以任何不知道的人(比如我)现在都知道了.抱歉，更新太长，我只是想彻底.

Just so anyone who didn't know (like me) now knows. Sorry for the long update, I just wanted to be thorough.

推荐答案

我对此并不完全确定，但评论太长了.

I am not completely sure of this, but it is a little too long for a comment.

据我所知，您只能将 LinkFormat.FullSourceName 设置为文件名，而不是全名 + 子集名称，这是您在附加！CED"时尝试执行的操作.虽然您可以从 OleFormat.Label 读取子集名称 (CED)，但您无法修改它，因为它是只读属性.

As far as I know, you can only set LinkFormat.FullSourceName to the name of a file, not a fullname + subset name, which is what you are trying to do when appending the "!CED". Although you can read the subset name (CED) from OleFormat.Label, you can't modify it as it's a read-only property.

因此，如果您确实需要修改子集名称 (CED)，AFAICS 唯一的方法就是删除并重新插入 LINK 字段.如果使用 Fields.Add 重新插入，则只需指定字段的文本，这样就可以正确获取文件名和子集名称.有点令人困惑的是，如果您使用 InlineShapes.AddOleObject 插入 LINK，您可以完全按照您在代码中尝试执行的方式指定全名+子集名称.

So if you actually need to modify the subset name (CED), AFAICS the only way to do it is to delete and reinsert the LINK field. If you reinsert using Fields.Add, you just specify the text of the field, so you can get the file name and Subset name right. What is slightly confusing is that if you insert a LINK using InlineShapes.AddOleObject, you can specify fullname+subset name in exactly the way that you are trying to do in your code.

但是，我不认为您正在试图修改子集名称.因此，让我们假设您已经有一个

However, I do not think you are trying to modify the Subset name. So let's assume that you already have a LINK field along the lines of

{ LINK Excel.SheetMacroEnabled.12 "the full pathname of a .xlsm file" CED \a f 0 \p }

Word 仅在路径+文件名有效时才能够更新该链接(即该位置有一个 .xlsm，工作簿有一个名为 CED 的范围名称，范围名称在第一个工作表中.否则，您还必须指定工作表名称，例如

Word will only be able to update that link if the path+filename is valid (i.e. there's a .xlsm at that location, the workbook has a Range Name called CED, and the Range Name is in the first Sheet. Otherwise, you have to specify a Sheet name as well, e.g.

{ LINK Excel.SheetMacroEnabled.12 "the full pathname of a .xlsm file" Sheet2!CED \a f 0 \p }

这只是一个猜测，但如果您的代码尝试连接到 CED 定义的范围不是在第一张工作表中的工作簿，您会看到您描述的错误.

It's just a guess, but if your code is trying to connect to a Workbook where the range defined by CED is not in the first sheet, you would see the error you describe.

此外，CED 范围名称的范围必须是工作簿"或第一张工作表的名称.否则，如果范围是第一个工作表但范围实际上在另一个工作表中，反之亦然，我认为 Word 无法建立连接，无论您提供什么子集名称(我的猜测是 Word 在 Excel 之后从未真正赶上 Excel引入了多页工作簿).

Further, the scope of the CED Range Name has to be either "workbook" or the name of the first sheet. Otherwise, if the scope is the first sheet but the range is actually in another sheet, or vice versa, I do not think Word can make the connection whatever subset name you provide (my guess is that Word never really caught up with Excel after Excel introduced multi-sheet workbooks).

如果 CED 可以引用除第一个以外的工作表，我认为您可能必须使用 Excel 对象模型来发现其范围所在的工作表，构造适当的子集名称，然后删除/重新插入 LINK 字段.

If CED can reference sheets other than the first one, I think you will probably have to use the Excel object model to discover which sheet its Range is in, construct the appropriate Subset name, and delete/re-insert the LINK field.

这篇关于以编程方式更新 MS Word 中 Excel 对象的链接命名范围 (2007)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！