My Automation Nightmare..Please help

[Scoty]

Hey guys,
I hope someone can help me out. I am attmepting to automate a mail merge. First let me descripe my problem. I am working on autoamtically faxing information from an access database. I dont want to print this stuff to letter head so I created a mailmerge document on word which has the letterhead (bitmaps) already attached in the header and footer. So now I fake-automate using send keys. This works fo about 2 seconds as the system times are upredictable at best. So I decide to OLE/COM automate. This is my first time to try this in a office app to office app situation. I have had success in other automation projects. But this one is turning into a real nightmare. I am running office97 on Win NT v4.0 build 6. Here is my code:

Private Sub Command10_Click()
Dim wd As Object
Dim regcnt As Integer
Dim z As Integer
Dim y As Double
Dim ID As Variant
Dim name As Variant
regcnt = Me.List6.ListCount
regcnt = regcnt - 1


For z = 0 To regcnt
    Set wd = CreateObject("Word.Application") 'create the word object
    name = "g:\netdrive\Scottys\faxdocs\temp" & z & ".doc" ' create a name for the new document
        DoCmd.GoToControl "List6"
    List6.Selected(z) = True
        DoCmd.GoToControl "Command10"
    ID = Mid(List6.Value, 16, Len(List6))
    text8 = ID ' the record number used to produce the merge
    wd.Application.Visible = True 'open word
    Application.Documents.Open "g:\netdrive\Scottys\faxdocs\LHFax.doc" ' open letter head fax document
        Do Until y = 15000000: y = y + 2: y = y - 1: Loop: y = 0 'Timer loop
    ActiveDocument.MailMerge.Execute 'make the merge
    ActiveDocument.SaveAs FileName:=name 'save the new doc as the value of name
    Documents("LHFax.doc").Close False 'close orig merge doc with out saving
    wd.Application.Quit ' close word
    Set wd = Nothing 'destroy object
    
Next z

Now the first time through the loop this works great but the next time through the loop I get this error

+-----------------------------------------+
|Run-Time error '-2147023174 (800706ba)': |
|                                         | 
|Automation error                         |
|The RPC server is unavailable            |
|                                         |
|  _____   ________   _______   _______   |
| |Debug| |Continue| |  End  | | Help  |  |
|  -----   --------   -------   -------   |
+-----------------------------------------+

The continue is greyed out. The debug takes me to the line:

ActiveDocument.MailMerge.Execute

I know it is something I am overlooking. Please help if me. I need to make this work soon.

Thanks
Scoty

 

[MichaelRed]

Scotty,

I would be interested in looking at the whole thing. If you would e-mail me enough of the db to execute, I will look at it.

At a glance, the only thingsI see which seems strange a:

1. Ceation / Destruction of the wd APPLICATION on each pass through the loop. I would think that just closing the active document where you set the app to nothing anad moving hte instantation of the app out of the loop would help.

2. The Do Nothing "timer Loop". This - IMHO just wastes CPU cycles. IF a delay is really necessary, a TIMER control would be MUCH more user friendly.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[Scoty]

Michael
Thanks for your reply. I finally got it working. It seems that remote process call will allow 1 upper level broadcast. (thus it works once through the loop) but it will not allow a second. The problem I was having is I was trying an upper level broadcast each time. After seval Cokes and a smoke I found that it is neccessary to define the call to the RPC. So the only thing missing from the above code is a constucted object....DUH!!. new code is:

    wd.ActiveDocument.MailMerge.Execute 'make the merge
    wd.ActiveDocument.SaveAs FileName:=name 'save the new doc as the value of name

btw I did remove the timer loop. It was not necessary. I was trying to allow time for the upper level broadcast through the RPC time to terminate. Since I defined my broadcast it is no longer necessary. Thanks for your help and if you would still like a copy of my code I will send it to you. I think it is sweet
Scoty :-D <<Good code = Happy Boy

 

[MichaelRed]

Yes, Please.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over