views:

5648

answers:

3

Hello,

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!

A: 

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

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.

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.

Ryan
A: 

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.

It seems like Word is displaying a dialog box that won't let any macros continue processing. Are you using the VBA Debugger to step through the code? If you can switch to the Word application after the first document supposedly has closed, see if there is anything strange about Word or if there is a dialog box. The code also needs to have some error handling when things fail.
Ryan
Thanks. Word looks/functions as normal after the first document is closed. I went through each step and never saw any dialog boxes in Word. It appears that the second form DOES open when the error message appears. Even after clicking OK to the message, Word is locked up -- I have to terminate the program from the task manager. Macro security is set to 'very high'.I'm not too experienced with error handling routines. I can study-up if you think that will solve the problem.
I tried the new code -- it runs without the error, but only processes the first form. I deleted all of my test forms and just made 10 copies of the first form that worked, but same error with the old code, and same result with the new code. When I open the forms by themselves (not in VBA) nothing seems unusual with them. I really appreciate the help. I'm just going to go with wiping out the object in the loop -- at least it works. At this point, I'm willing to sacrifice performance time for a solution. Thanks again!!
Sorry, I'm a new user, so I can't vote-up your response. I have to have a reputation of 15 -- crap! Sorry. Thanks again.
A: 

Thanks for the help. 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?

Please see me edits in my response. Also, you may want to read the FAQ about editing: http://stackoverflow.com/questions/18557/how-does-stackoverflow-work-the-official-faq. It isn't real specific in the FAQ, but it isn't recommended to create a new response for each update to the original question, but rather edit the question by providing additional information (unless you are answering your own question). Lastly, if you are able to up-vote my answer for the time spent, I certainly wouldn't mind. Thanks. :-D
Ryan