Re: Changing reference to Word in Excel project

From: Tom Ogilvy (twogilvy_at_msn.com)
Date: 05/03/04 

Date: Mon, 3 May 2004 13:52:28 -0400

I suspect there is more to your code than what you show. If you reference
an object in Word and it is not fully qualified, then you can create a
non-releasable reference to word. Thus, when you code ends, even though you
might have issued all the commands to close word, it may still remain in
memory. The code you show appears to be designed to execute multiple times
in a session, so you could be filling memory with copies of word and causing
the intermittent problems you describe.

So the code you show does appear to use late binding - thus you don't need a
reference to the word object library (use the value of any word constants
rather than using defined constant name.)

Make sure you issue the proper commands to close word

Fully qualify all object refernces to objects in word and release them at
the end. 

-- 
Regards,
Tom Ogilvy
"Ed" <Ed_Millis@NOSPAM.Hotmail.com> wrote in message
news:ec4FWOSMEHA.3216@TK2MSFTNGP12.phx.gbl...
> Bob and Tom:
>
> Thank you for your responses.  But I thought I *was* using late binding.
> Sometimes, though, it will error on the Open command - Object required.
> Other times it will lock up and not proceed; when I close through Task
> Manager, I have a "ghost" doc created.  My code is below - if you can help
> me understand what I'm missing, I will be most grateful.
>
> Ed
>
> Dim WD As Object
> Dim doc As String
> Dim Fname As String
> Dim Fpath As String
>
>     ' Get file path
>     Fpath = ThisWorkbook.Path
>
>     ' Check if ActiveCell is in Col C
>     Sheets("Sheet1").Activate
>     If ActiveCell.Column = 3 Then
>
>         ' Get TIR number from list page
>         Fname = ActiveCell.Text
>
>         ' Open TIR
>         doc = Fpath & "\" & Fname & ".doc"
>         Set WD = CreateObject("Word.Application")
>         WD.Documents.Open doc
>         WD.Visible = True
>
>     Else
>     MsgBox "Please select a TIR number in Column C using a single mouse
> click."
>
> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> news:eHYeoyRMEHA.3596@tk2msftngp13.phx.gbl...
> > tools=>Macros=>Security, select trust access to Visual Basic Project
> >
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;282830
> > PRB: Programmatic Access to Office XP VBA Project Is Denied
> >
> > However, your whole idea sounds flawed.  The library doesn't actually
> > contain the code that will be executed I don't believe, so you would be
> > using a roadmap of Europe to find you way around the US as an analogy.
> >
> > The usual solution to this problem is to use late binding for the
release
> > version of the application.
> >
> >
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;244167
> > INFO: Writing Automation Clients for Multiple Office Versions
> >
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;245115
> > INFO: Using Early Binding and Late Binding in Automation
> >
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;247579
> > INFO: Use DISPID Binding to Automate Office Applications Whenever
Possible
> >
> > -- 
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "Ed" <Ed_Millis@NOSPAM.Hotmail.com> wrote in message
> > news:OziJghRMEHA.3012@tk2msftngp13.phx.gbl...
> > > I have an Excel project which calls Word (hence posting to both NG).
> It's
> > > created on a system running Windows/Office XP.  I've been having
> > difficulty
> > > running code which calls Word on Windows/Office 2000 machines.  I
> > discovered
> > > the reference to the Word library was for Word10 - XP - which does not
> > exist
> > > on a 2000 machine.  I got the bright idea of copying the Word9
reference
> > > from a 2000 machine onto my XP machine, and then setting the reference
> in
> > > the code which creates this workbook.  With the following code, I have
> two
> > > problems:
> > >
> > > Sub Change_Refs()
> > >
> > > ' Set reference to Word9 vs. Word10
> > > refWord9 = "C:\Program Files\Microsoft Office\Office10\MSWORD9.olb"
> > > refWord10 = "C:\Program Files\Microsoft Office\Office10\MSWORD.olb"
> > >
> > > ThisWorkbook.VBProject.References.Remove refWord10
> > > ThisWorkbook.VBProject.References.AddFromFile refWord9
> > >
> > > End Sub
> > >
> > > Problem 1:  The Remove line generates an error - "Object required"
> > > Problem 2:  I commented out the Remove line just to see what the code
> > would
> > > do on the Add line.  Another error - I'm not trusted to
programmatically
> > > change the VB project.
> > >
> > > Any suggestions?
> > >
> > > Ed
> > >
> > >
> >
> >
>
>