从Excel VBA更新Word文档中的链接字段 [英] Update linked fields in Word document from Excel VBA

问题描述

我试图通过将数据放入电子表格并链接到Word中的各个单元格，自动更新3种不同Word文档中的某些信息（如名称，日期和数字）。电子表格中有一些宏，它内部自动更新电子表格的部分。

除了更新Word文档中的链接之外，一切都正常。
尝试通过右键单击Word中的链接并选择更新链接选项来更新Word中的链接时，会为电子表格打开宏警告对话框，询问是否要激活宏。它不会这样做只是一次，但不断在20年代左右的更新过程（这似乎异常长的我）。所以更新链接是有效的，但只有当你愿意点击几十次的激活宏按钮。

我试图自动更新所有字段来自Word与VBA的文档，但有同样的问题，它也会持续打开宏对话半分钟。
这是我的代码：

  Sub UpdateFields（）
 ActiveDocument.Fields.Update 
 End Sub 
  

我还尝试从电子表格直接更新Word文档，但这不起作用或者，因为当Excel尝试通过VBA打开Word文档时，程序将停止执行并且删除此错误：

Excel正在等待另一个应用程序来完成OLE操作。

单击确定并等待不会有帮助，因为错误消息在几秒钟后重新出现，唯一的方法是手动删除Excel进程。 p>

这是我的Excel宏代码：

  Sub LoopThroughFiles（）
 Path = Application.ActiveWorkbook.Path 
 Dim WordFile As String 
 WordFile = Dir（Path&\ * .doc）
 Do While Len（WordFile）> 0 
运行更新（Path&\& WordFile）
 WordFile = Dir 
循环
 End Sub 
 
函数更新（Filepath As字符串）
 
 Dim WordDoc As Word.Document 
设置WordApplication = CreateObject（Word.Application）
设置WordDoc = WordApplication.Documents.Open（Filepath）'这产生错误
 
 ActiveDocument.Fields.Update 
 
结束功能
  

请注意，文件夹中的唯一文件是3个文档和电子表格，程序确实找到文件没有任何问题。

我已经搜索解决方案在线但是我没有找到任何东西，我觉得很奇怪，因为它似乎是一个很常见的事情，有人会做VBA。
那么再一次，我对VBA有很少的经验，所以我可能完全不知道这一点，还有一个超简单的解决方案，我只是不知道。

解决方案

我想我看到错误，这是一个沉默的失败，因为文档包含链接，有一个打开的对话框等待你说是或否来更新链接。

我们可以通过禁用自动链接更新来禁止此对话框（ WordApplication.Options.UpdateLinksAtOpen = False ）。 / p>

 函数更新（文件路径为字符串）
 Dim WordApplication作为Word.Application 
 Dim WordDoc As Word.Document 
 Dim updateLinks As Boolean 
 
设置WordApplication = CreateObject（Word.Application）
 updateLinks = WordApplication.Options.UpdateLinksAtOpen'捕获原始值
 WordApplication.Options .UpdateLinksAtOpen = False'暂时禁用
 
设置WordDoc = WordApplication.Documents.Open（Filepath）
 WordDoc.Fields.Update 
'MsgBox更新在& WordDoc.Name 
'##保存并关闭文档
 WordDoc.Save 
 WordDoc.Close 
 
'##重置上一个值并退出Word应用程序
 WordApplication.Options.UpdateLinksAtOpen = updateLinks'
 WordApplication.Quit 
 
结束功能
  

此外，请记住保存并关闭文档，并退出函数中的单词应用程序。

我进行了另外一个修改：

在您的函数中， ActiveDocument 不是Excel中的对象，因此您需要对其进行限定，否则该行也将抛出错误而不是引用 WordApplication.ActiveDocument ，我只是简单地引用您已经分配的 WordDoc 。

I am trying to automatically update certain information (such as names, dates and numbers) across 3 different Word documents by putting the data into a spreadsheet and linking to the respective cells from Word. The spreadsheet has some Macros in it which auto-update parts of the spreadsheet internally.

Everything is working fine, except for updating the links in the Word documents. When trying to update a link in Word by right-clicking on it and selecting the "update link" option it brings up the Macro warning dialog for the spreadsheet, asking whether I want to activate Macros or not. It doesn't do this just once but constantly during the 20s or so the update process takes (which seems unusually long to me). So updating the link works, but only if you're willing to click the "activate Macros" button of a few dozen times.

I tried to automate updating all fields in a document from Word with VBA, but that has the same problem, it also brings up the Macros dialog constantly for half a minute. Here's my code for that:

Sub UpdateFields()
    ActiveDocument.Fields.Update
End Sub

I also tried to update the Word documents directly from the spreadsheet, but that does not work either, because when Excel tries to open a Word document via VBA the program stops executing and trows this error:

"Excel is waiting for another application to complete an OLE action."

Clicking ok and waiting does not help because the error message reappears after a few seconds, and the only way to stop it is to manually kill the Excel process.

Here's my Excel Macro code:

Sub LoopThroughFiles()
    Path = Application.ActiveWorkbook.Path
    Dim WordFile As String
    WordFile = Dir(Path & "\*.doc")
    Do While Len(WordFile) > 0
        Run Update(Path & "\" & WordFile)
        WordFile = Dir
    Loop
End Sub

Function Update(Filepath As String)

    Dim WordDoc As Word.Document
    Set WordApplication = CreateObject("Word.Application")
    Set WordDoc = WordApplication.Documents.Open(Filepath) 'This produces the error

    ActiveDocument.Fields.Update

End Function

Note that the only files in the folder are the 3 documents and the spreadsheet, and the program does find the files without any problems.

I have searched for solutions online but I did not really find anything, which I found odd, since it seems like a pretty common thing that someone would do with VBA. Then again, I have very little experience with VBA, so I might be completely missing the point and there is a super simple solution I am just not aware of.

解决方案

I think I see the error, which is a silent failure, becuase the document contains links, there is an open dialog waiting for you to say "yes" or "no" to update the links.

We can suppress this dialog by disabling the automatic link updates (WordApplication.Options.UpdateLinksAtOpen = False).

Function Update(Filepath As String)
    Dim WordApplication As Word.Application
    Dim WordDoc As Word.Document
    Dim updateLinks As Boolean

    Set WordApplication = CreateObject("Word.Application")
        updateLinks = WordApplication.Options.UpdateLinksAtOpen 'capture the original value
        WordApplication.Options.UpdateLinksAtOpen = False      'temporarily disable

    Set WordDoc = WordApplication.Documents.Open(Filepath)
        WordDoc.Fields.Update
        'MsgBox "Links updated in " & WordDoc.Name
        '## Save and Close the Document
        WordDoc.Save
        WordDoc.Close

    '## reset the previous value and Quit the Word Application
    WordApplication.Options.UpdateLinksAtOpen = updateLinks             '
    WordApplication.Quit

End Function

Also, remember to Save and Close the document, and Quit the word application inside the function.

I made this other modification:

In your function, ActiveDocument is not an object in Excel, so you would need to qualify it, otherwise that line will also throw an error. Rather than refer to WordApplication.ActiveDocument, I just simply refer to the WordDoc which you have already assigned.

这篇关于从Excel VBA更新Word文档中的链接字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！