Error calling Word objects

[Ken Puls]

Hello,

I've been having a little fun trying to build a macro to print mailing labels from Excel via a Microsoft Word template. My code works sometimes, which is a little frustrating as I'd really prefer it to work all the time! I've think I've figured out the Early vs Late bind on this, and have opted for an early bind. (A reference is set to the Microsoft Word 11.0 Object Library as I am using Office 2003.)

Here's the code:

Sub testwordcall()
Dim WordApp As Word.Application
Set WordApp = CreateObject("Word.Application")
With WordApp
    .Visible = True
    .Documents.Add Template:="F:\templates\FW Centre\MemberRenewalLabels.dot", _
        NewTemplate:=False, DocumentType:=0
    
    With ActiveDocument.MailMerge
        .OpenDataSource Name:="J:\Members Addresses.xls", _
            ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
            AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
            WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
            Format:=wdOpenFormatAuto, Connection:= _
            "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=J:\Members Addresses.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=35" _
            , SQLStatement:="SELECT * FROM `MailMergeData$`", SQLStatement1:="", _
            SubType:=wdMergeSubTypeAccess
        .Destination = wdSendToPrinter
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
    
    .Quit False
End With
End Sub

(I hope it doesn't blow apart text wrapping as I ran into issues trying to wrap the OpenDataSource part so just set it back to what works.)

So here's the issue:

The first time I run the macro, I get a "Runtime error '462' The remote server machine does not exist or is unavailable"

What's weird is that if I run it again immediately, it works beautifully. Can anyone tell me what is going wrong here?

Thanks,

 

[Ken Puls]

I don't think anyone loves Word :wink:

Just made the change and it seems to be working better. I had it in the With WordApp section before though, so I sort of thought it was implied. Should've probably known better.

I had actually closed down Excel before I saw your reply though, and received notifcation that my file was now available for editing. Earlier in the day, I had adjusted the word template and run into some issues, but could have sworn that I'd closed Excel completely since then. Of course, if I'd had a hidden instance of Word open...

Since re-opening, everything seems fine, no matter how many times I run the macro. I'll rerun it tomorrow just to make sure, but I think you might have nailed it!

Thanks,

 

[Ken Puls]

Now every time I close Excel, I'm notified that my file is now available for editing!

Is there an "I don't care" flag somewhere that I missed?

 

[NateO]

Which file? Your data source?

Try closing the document explicitely before closing the app as I did in my previous post, see if that releases the file.

I'm surprised you don't close this bugger (the connection) somehow other than closing the Word Doc., it's not mentioned here:

http://msdn.microsoft.com/library/d...ry/en-us/vbawd10/html/womthOpenDataSource.asp

Another thought, have you tried: .OpenDataSource Name:=vbnullstring?

Could be chundering on a double-call, once in the name, and again in the connection string.

 

[Ken Puls]

Hi Nate,

Things just get stranger and stranger... Solve one issue and another pops up!

Sorry, yes it was my data source that released only upon closing Excel. I tried explicitly closing the file, and that still didn't work. Actually, interestingly enough, I still have to close the WordApp with a .quit savechanges:=false since Word automatically creates a Document1 (or higher) when opening a template.

For your reference, the vbnullstring gagged with a "Runtime Error '4198' Command Failed" error.

I was thinking about this last night, and to me it seems almost like a record locking issue, so I looked really carefully at the link you posted. There is actually a setting in the OpenDataSource method for an "OpenExclusive" option. Setting that to False seems to have cured that issue. Your point about closing the connection prompted me to look for a CloseDataSource method, which doesn't exist, but you can use .Close on a Mailmerge.Datasource object. I've added that to my code as well. (Figured it's best to clean up as you go along.)

So now when I close Excel, I don't seem to be getting that error any more, but something else is happening... After opening Excel, running the mailmerge and closing Excel, I checked my task manager to make sure I had no hidden instances of Word running, which I don't. I do end up with a hidden instance of Excel though! I canned it, and tried opening and closing Excel, which releases the Excel.exe program without a problem. Running that mailmerge macro though, it always leaves a hidden instance running.

Have you ever heard of anything like that?

I've posted my modified code, just in case there's anything in there that obviously seems like it would cause this... Just so you know (before you ask) the commented line re wdmailinglabels will be made live when this works properly. I'm just trying to save my paper right now!

Sub MailMerge_Execute()
'Macro written 12/01/2003 by Ken Puls
'Macro purpose:  To open a word template and create mailing labels from an Excel DB

Dim WordApp As Word.Application
Dim wrdDoc As Word.Document
Set WordApp = New Word.Application

WordApp.Visible = False
Set wrdDoc = WordApp.Documents.Add(Template:="F:\templates\FW Centre\MemberRenewalLabels.dot", _
    NewTemplate:=False, DocumentType:=0)

With WordApp
    With wrdDoc.Mailmerge
        .OpenDataSource Name:="J:\Members Addresses.xls", _
            ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
            AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
            WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
            Format:=wdOpenFormatAuto, Connection:= _
            "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=J:\Members Addresses.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=35" _
            , SQLStatement:="SELECT * FROM `MailMergeData$`", SQLStatement1:="", _
            SubType:=wdMergeSubTypeAccess, OpenExclusive:=False
'        .Destination = wdSendToPrinter
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
        .DataSource.Close
    End With
End With

'Close the mailmerged document and extra file created by Word, and quit Word
wrdDoc.Close savechanges:=False
WordApp.Quit savechanges:=False

'Release variables
Set wrdDoc = Nothing
Set WordApp = Nothing

End Sub

Thanks,