vba Excel VBA循环打开多个Word文件

Excel VBA to Open Multiple Word files in a loop

提问by Ryan

I apologize in advance for the newbie question -- most of my VBA experience is in Excel, or Word to Excel. In this case, I am going from Excel to Word. I am trying to capture some data off of some Word forms and store it in an Excel file.

我提前为新手问题道歉——我的大部分 VBA 经验都是在 Excel 中，或 Word 到 Excel。在这种情况下，我将从 Excel 转到 Word。我正在尝试从某些 Word 表单中捕获一些数据并将其存储在 Excel 文件中。

Right now, my code works for the first document in the folder, but after that, it hoses up with an automation error "the server threw an exception" (goo!)

现在，我的代码适用于文件夹中的第一个文档，但在那之后，它出现了一个自动化错误“服务器抛出异常”（goo！）

Here is my code:

这是我的代码：

Dim objWordApp As Object

strCurFileName = Dir(strFilePath)

Set objWordApp = CreateObject("word.application")
objWordApp.Visible = True

Do While strCurFileName <> ""

    objWordApp.documents.Open strFilePath & strCurFileName
    objWordApp.activedocument.Unprotect password:="testcode"

    {EXCEL PROCESSING HERE}

    strCurFileName = Dir
    objWordApp.activedocument.Close 0

Loop

objWordApp.Quit
Set objWordApp = Nothing

I notice that the code works fine if I quit the app and set the object = nothing within the loop. But the way it is now, it bombs-out on the second file in the folder on the "objWordApp.documents.Open strFilePath & strCurFileName" line.

我注意到如果我退出应用程序并在循环中设置 object = nothing ，代码工作正常。但现在的方式是，它在“objWordApp.documents.Open strFilePath & strCurFileName”行上的文件夹中的第二个文件上爆炸。

Can I open and close Word documents in a loop without having to create the object over and over? It's really slow when I do it that way.

我可以循环打开和关闭 Word 文档而不必一遍又一遍地创建对象吗？当我这样做时，它真的很慢。

---

---

Thanks for the help -- I like your way much better. Unfortunately, I get the same result. The program dies the second time through the loop on the line that reads:

谢谢你的帮助——我更喜欢你的方式。不幸的是，我得到了相同的结果。该程序第二次通过循环中的以下内容终止：

Set objWordDoc = objWordApp.Documents.Open(objFile.Path)

The error that I get is:

我得到的错误是：

Run-time Error -2147417851 (80010105) Automation Error The server threw an exception.

运行时错误 -2147417851 (80010105) 自动化错误 服务器抛出异常。

I tried your code on regular word docs (not the ones I'm processing) and it worked fine. The docs I'm running have form fields and macros -- not sure if that makes a difference. I have set the macro security in Word to both "low" and "very high" to make sure the other macros don't interfere.

我在常规 word 文档（不是我正在处理的文档）上尝试了您的代码，并且效果很好。我正在运行的文档有表单域和宏——不确定这是否有区别。我已将 Word 中的宏安全性设置为“低”和“非常高”，以确保其他宏不会干扰。

I just can't figure it out why it works for the first doc and then not the next. I even cloned the first doc but it made no difference.

我只是不明白为什么它适用于第一个文档，然后不适用于下一个。我什至克隆了第一个文档，但没有任何区别。

---

---

Still no luck, though. The only thing I can get to work is if I completely wipe the objects and re-create them every time I want to open a file.

尽管如此，仍然没有运气。我唯一可以开始工作的是，如果我每次想打开文件时完全擦除对象并重新创建它们。

Set objFolder = FSO.GetFolder(strFilePath)

For Each objFile In objFolder.Files

    Set objWordApp = CreateObject("word.application")
    objWordApp.Visible = True

    If Right(objFile.Name, 4) = ".doc" Then
        Set objWordDoc = objWordApp.documents.Open(Filename:=objFile.Path, ConfirmConversions:=False, _
            ReadOnly:=True, AddToRecentFiles:=False, PasswordDocument:="", _
            PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
            WritePasswordTemplate:="", Format:=wdOpenFormatAuto)

        [Process DOC]

        objWordDoc.Close 0, 1
    End If

    Set objWordDoc = Nothing
    objWordApp.Quit
    Set objWordApp = Nothing

Next

I'm not sure why that works and why it won't work the other way. If I have to go this route, I can -- it just seems really slow and inefficient. Is this a bad idea?

我不确定为什么这样做以及为什么它不会以其他方式起作用。如果我必须走这条路，我可以——它看起来真的很慢而且效率很低。这是一个坏主意吗？

回答by Ryan

I changed the Dir to a FileSystemObject (go to Tools\References and add Microsoft Scripting Runtime) and I was able to successfully open multiple files. If you are having problems, please describe the error you see in the debugger. Also, if you need to recurse into subdirectories, you will need to refactor this.

我将 Dir 更改为 FileSystemObject（转到 Tools\References 并添加 Microsoft Scripting Runtime）并且我能够成功打开多个文件。如果您遇到问题，请描述您在调试器中看到的错误。此外，如果您需要递归到子目录中，则需要重构它。

Private mobjWordApp As Word.Application

Sub Test()
  ProcessDirectory "PathName"
End Sub

Property Get WordApp() As Word.Application
  If mobjWordApp Is Nothing Then
    Set mobjWordApp = CreateObject("Word.Application")
    mobjWordApp.Visible = True
  End If
  Set WordApp = mobjWordApp
End Property

Sub CloseWordApp()
  If Not (mobjWordApp Is Nothing) Then
    On Error Resume Next
    mobjWordApp.Quit
    Set mobjWordApp = Nothing
  End If
End Sub

Function GetWordDocument(FileName As String) As Word.Document
    On Error Resume Next
    Set GetWordDocument = WordApp.Documents.Open(FileName)
    If Err.Number = &H80010105 Then
      CloseWordApp
      On Error GoTo 0
      Set GetWordDocument = WordApp.Documents.Open(FileName)
    End If
End Function

Sub ProcessDirectory(PathName As String)
  Dim fso As New FileSystemObject
  Dim objFile As File
  Dim objFolder As Folder
  Dim objWordDoc As Object

  On Error Goto Err_Handler

  Set objFolder = fso.GetFolder(PathName)
  For Each objFile In objFolder.Files
    If StrComp(Right(objFile.Name, 4), ".doc", vbTextCompare) = 0 Then
      Set objWordDoc = GetWordDocument(objFile.Path)
      ' objWordDoc.Unprotect Password:="testcode" ' Need to check if it has Password?
      ProcessDocument objWordDoc
      objWordDoc.Close 0, 1
      Set objWordDoc = Nothing
    End If
  Next

Exit_Handler:
  CloseWordApp
  Exit Sub

Err_Handler:
  MsgBox "Error " & Err.Number & ": " & Err.Description
  Resume Exit_Handler
  'Resume Next ' or as above
End Sub

Sub ProcessDocument(objWordDoc As Document)
  '{EXCEL PROCESSING HERE}'
End Sub

EDIT: I've added some error handling and a little refactoring although there is quite a bit more refactoring that could be done.

编辑：我添加了一些错误处理和一些重构，尽管还有很多可以完成的重构。

There must be something special about the documents you are opening. You might try using different parameters for opening the documents, such as:

您打开的文件一定有什么特别之处。您可以尝试使用不同的参数打开文档，例如：

Set objWordDoc = objWordApp.Documents.Open( _
  FileName:=objFile.Path, ReadOnly:=True)

You may need to add Microsoft Word as a Reference, and if you do that then start using the Word constants (wdDoNotSaveChanges, etc.). Check out the help on Documents.Open and test different parameters.

您可能需要添加 Microsoft Word 作为参考，如果这样做，则开始使用 Word 常量（wdDoNotSaveChanges 等）。查看 Documents.Open 上的帮助并测试不同的参数。

Also, use the "Set Next Statement" from the Context Menu during debugging and maybe skip the first document and open the second document directly and see if there are issues.

另外，在调试期间使用上下文菜单中的“设置下一个语句”，可能会跳过第一个文档并直接打开第二个文档，看看是否有问题。

EDIT: I've changed the code to close and reopen Word if you get the automation error you described. You may have to adjust the error numbers, or simply close Word on any error (If Err.Number <> 0 Then ...).

编辑：如果您遇到您描述的自动化错误，我已更改代码以关闭并重新打开 Word。您可能需要调整错误编号，或者在出现任何错误时关闭 Word（如果 Err.Number <> 0 Then ...）。

Again, something must be special about your documents (macros, protection, etc.) because this code works on the test cases I have tried. Have you tried manually opening the documents in Word in the same order as the script, updating information similar to your process script, and then closing the documents to see if Word does anything strange?

同样，您的文档（宏、保护等）必须有一些特别之处，因为此代码适用于我尝试过的测试用例。您是否尝试过按照与脚本相同的顺序在 Word 中手动打开文档，更新类似于您的流程脚本的信息，然后关闭文档以查看 Word 是否有任何异常？

Closing the Word.Application won't hurt anything, but it will obviously significantly slower.

关闭 Word.Application 不会有任何伤害，但显然会明显变慢。