Excel VBA循环打开多个Word文件
对于这个新手问题,我提前表示歉意——我的大部分 VBA 经验都是在 Excel 或 Word 到 Excel 中使用的。 在本例中,我将从 Excel 转到 Word。 我正在尝试从某些 Word 表单中捕获一些数据并将其存储在 Excel 文件中。
现在,我的代码适用于文件夹中的第一个文档,但之后,它会出现自动化错误“服务器抛出异常”(哇!)
这是我的代码:
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
我注意到如果我退出应用程序并在循环内设置对象=无。 但现在的情况是,它会轰炸“objWordApp.documents.Open strFilePath & strCurFileName”行上文件夹中的第二个文件。
我可以循环打开和关闭 Word 文档,而不必一遍又一遍地创建对象吗? 当我这样做时,速度真的很慢。
谢谢你的帮助——我更喜欢你的方式。 不幸的是,我得到了同样的结果。 该程序通过行上的循环第二次终止:
Set objWordDoc = objWordApp.Documents.Open(objFile.Path)
我得到的错误是:
运行时错误 -2147417851 (80010105) 自动化错误 服务器抛出异常。
我在常规Word文档(不是我正在处理的文档)上尝试了您的代码,并且运行良好。 我正在运行的文档有表单字段和宏——不确定这是否有影响。 我已将 Word 中的宏安全性设置为“低”和“非常高”,以确保其他宏不会干扰。
我只是不明白为什么它适用于第一个文档,然后不适用于下一个文档。 我什至克隆了第一个文档,但这没有什么区别。
但仍然没有运气。 我唯一可以开始工作的是,如果我完全擦除对象并在每次要打开文件时重新创建它们。
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 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!)
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.
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)
The error that I get is:
Run-time Error -2147417851 (80010105)
Automation Error
The server threw an exception.
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?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
我将 Dir 更改为 FileSystemObject(转到 Tools\References 并添加 Microsoft Scripting Runtime),并且能够成功打开多个文件。 如果您遇到问题,请描述您在调试器中看到的错误。 另外,如果您需要递归到子目录,则需要重构它。
编辑:我添加了一些错误处理和一些重构,尽管还可以进行更多的重构。
您打开的文档一定有什么特别之处。 您可以尝试使用不同的参数来打开文档,例如:
您可能需要添加 Microsoft Word 作为参考,如果这样做,则开始使用 Word 常量(wdDoNotSaveChanges 等)。 查看 Documents.Open 的帮助并测试不同的参数。
另外,在调试期间使用上下文菜单中的“设置下一条语句”,可能会跳过第一个文档并直接打开第二个文档并查看是否存在问题。
编辑:如果您收到您所描述的自动化错误,我已更改代码以关闭并重新打开 Word。 您可能需要调整错误编号,或者干脆关闭出现任何错误的 Word(如果 Err.Number <> 0 那么...)。
同样,您的文档必须有一些特殊之处(宏、保护等),因为此代码适用于我尝试过的测试用例。 您是否尝试过按照与脚本相同的顺序在 Word 中手动打开文档,更新与流程脚本类似的信息,然后关闭文档以查看 Word 是否有任何异常?
关闭 Word.Application 不会有任何损害,但显然会明显变慢。
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.
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:
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.
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 ...).
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?
Closing the Word.Application won't hurt anything, but it will obviously significantly slower.