Error calling Word objects

Ken Puls · Dec 1, 2003

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:

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,

NateO · Dec 1, 2003

Try changing:

Set WordApp = CreateObject("Word.Application")

With

Set WordApp = New Word.Application

You may want to work with a specific obejct versus active document, e.g.,

Dim wrdDoc As Word.Document
Set wrdDoc = objWD.Documents.Add

Then:

With wrdDoc

Ken Puls · Dec 1, 2003

Nate,

Thank you! Always appreciate your help. "New" seems to be working a lot better than "CreateObject". Funny that I never came across that in all my reading on Binds...

On the other point though, I'm not quite following. I reluctantly put in the ActiveDocument because I didn't know how to assign it to a variable, and I'm not quite there yet still.

Where exactly does the "Set wrdDoc" line go? I tried this:

Set wrdDoc = objWD.Documents.Add Template......

It really didn't like it. Can I get that in there and still make it work with the template?

NateO · Dec 1, 2003

Hello again Ken, you are welcome.

Could be barfing on the French spelling of Center, yes-no?

Try the following:

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


I'll let you guess where to stick it.

Ken Puls · Dec 1, 2003

I'm feeling like using another french word right now!

Okay, should have seen the brackets on that, just not quite with it.

It turns out that it's still gagging every now and then though. (The original problem...)

It almost seems to be that if it sits for a while, it loses it's connection to, I assume, the automation server? I just don't understand why this would be intermittent though. Would you be able to shed some light on this by any chance?

Thanks,

NateO · Dec 1, 2003

Hello again,

What is:

.Quit False

? Sacre bleu. Why would quitting the Word App have a False argument to it? This could certainly be a yacking point.

If you have a ton of hidden Word instances going, expect your PC to chunder.

Use the following to see if you have any hidden instances out there causing your computer to ralph:

sub dhfkjhsdfj()
dim y as object
set y = getobject(,"Word.Application")
y.visible = true
set y = nothing
end sub

If there are none, you'll get an error, if there are some, you should notice.

You shouldn't have any active connection, you should be creating one via ole...

Why are you running this from Excel again?

Ken Puls · Dec 1, 2003

Why are you running this from Excel again?

I just like to make life difficult for myself!

Seriously though, I'm trying to automate this for one of our users as they have to do it monthly. It'll save them some time, and doubles as a learning experience for me.

.Quit False is just shorthand for .Quit savechanges:=false Either seems to work, but I was being lazy typing.

Been watching the task manager and I've been killing any Winword apps that stay, although they only stay alive if the code errors out. Also tried your sub, but received an ActiveX error, Unable to Create object. I assume that this is because there are no hidden instances.

One thing I sort of forgot to metion is where it is erroring out.

It's actually dying on the Set wrdDoc line, but again, only somtimes....

It creates the Word object
It sets it visible
It MAY be able to make a New document...

It's just plain weird to me. Code either works or not, never intermittent. Something must be different, I just can't figure out what...

NateO · Dec 1, 2003

I don't think the tesk manager will help you, but the code will, good, you want that error. Maybe if you reboot it will like you better. Your system gets bolloxed up when developing these things incorrectly.

I see what you're saying, I would've killed it all in Word via:

ThisDocument.Close False
Application.Quit

Why run this if you're not going to save it?

Make sure to release your object variables to avoid memory leaks, either from bugs or circular references, e.g.,

set wrdDoc = nothing: set WordApp = Nothing

At the end of your code.

Otherwise, I don't know what the problem is...

Ken Puls · Dec 1, 2003

I see what you're saying, I would've killed it all in Word via:

ThisDocument.Close False
Application.Quit

Really? I've always thought that you were the king of shorthand!

The reason for not saving is that I'm merging it straight to the printer. It's a one shot deal, and not necessary after that. Good point on releasing the variables too, I'll have to modify for that.

I'll give the reboot a shot, and see if it makes a difference. I've been hammering my poor PC with stuff today, so it's quite likely that it's a little tired. It's almost time to head for home anyway!

NateO · Dec 1, 2003

Wait a second, hold the phone. The code I posted is bloody sloppy!

Reference the App when opening the file:

Dim WordApp As Word.Application, wrdDoc As Word.Document
Set wrdDoc = WordApp.Documents.Add(Template:="C:\Program Files\Microsoft Office\Office\Startup\Amex.Dot", _
NewTemplate:=False, DocumentType:=0)

See if this helps, perhaps you made the appropriate adjustment with your with statement. Can't believe I forgot that...

I do love shorthand, not Word though.

Error calling Word objects

Ken Puls

Active Member

Excel Facts

NateO

Legend

Ken Puls

Active Member

NateO

Legend

Ken Puls

Active Member

NateO

Legend

Ken Puls

Active Member

NateO

Legend

Ken Puls

Active Member

NateO

Legend

Similar threads

Forum statistics

Share this page

Error calling Word objects

Active Member

Excel Facts

Legend

Active Member

Legend

Active Member

Legend

Active Member

Legend

Active Member

Legend

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock