Excel VBA在循环中打开多个Word文件 [英] Excel VBA to Open Multiple Word files in a loop

问题描述

对于新手问题，我事先表示歉意-我的大多数VBA经验都来自Excel或Word to Excel.在这种情况下，我将从Excel转到Word.我正在尝试从某些Word表单中捕获一些数据并将其存储在Excel文件中.

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.

现在，我的代码适用于该文件夹中的第一个文档，但是此后，它出现了自动化错误服务器抛出异常"(提示！)

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!)

这是我的代码:

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

我注意到，如果我退出应用程序并在循环内将object设置为none，则代码可以正常工作.但是，按照现在的方式，它会轰炸"objWordApp.documents.Open strFilePath& strCurFileName"行上的文件夹中的第二个文件.

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.

是否可以循环打开和关闭Word文档，而不必一遍又一遍地创建对象?这样的话真的很慢.

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.

感谢您的帮助-我非常喜欢您的方式.不幸的是，我得到了相同的结果.该程序第二次死于循环，该循环的内容为:

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)

我得到的错误是:

运行时错误-2147417851(80010105)自动化错误服务器引发了异常.

我在常规word文档(而不是我正在处理的文档)上尝试了您的代码，并且效果很好.我正在运行的文档具有表单字段和宏-不确定是否有所不同.我已将Word中的宏安全性设置为低"和非常高"，以确保其他宏不会干扰.

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.

我无法弄清楚为什么它适用于第一个文档，而不适用于下一个文档.我什至克隆了第一个文档，但没什么区别.

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?

推荐答案

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

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.

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

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

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)

您可能需要添加Microsoft Word作为参考，如果这样做，则开始使用Word常量(wdDoNotSaveChanges等).查看有关文档的帮助.打开并测试不同的参数.

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.

此外，在调试过程中使用上下文菜单中的设置下一条语句"，并且可以跳过第一个文档并直接打开第二个文档，看看是否有问题.

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.

如果您遇到了所描述的自动化错误，我已经更改了代码以关闭并重新打开Word.您可能必须调整错误编号，或仅对任何错误关闭Word(如果Err.Number<> 0然后...).

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是否有任何奇怪之处?

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.应用程序不会造成任何伤害，但显然会明显减慢速度.

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

这篇关于Excel VBA在循环中打开多个Word文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！