Changing reference to Word in Excel project

[Ed]

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

 

[Bob Phillips]

Use late binding.

Instead of declaring your Word object as Word,Application, declare it as a
generic object

Dim wordApp as object

and then don't New, createobject

Set wordApp = CreateObject(,"Word.Application")

then use the object as before.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

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

 

[Ed]

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]

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.

 

[Ed]

Thanks for getting back with me, Tom. I read through all your references -
thank you! But I didn't see anything that turned a light on.

I suspect there is more to your code than what you show.

**No - except for "Sub" and "End Sub", this is it.

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.

**No, it's only executing once - or I should say for the first time!
Although I do call the entire Sub multiple times, it always runs through to
End Sub. And I had no problems with Word hanging on in the 2000
environment.

So the code you show does appear to use late binding - thus you don't need a
reference to the word object library

**So I did something right for a change!

(use the value of any word constants
rather than using defined constant name.)

**This went over my head.

Make sure you issue the proper commands to close word

**But if I close Word, won't I also close the document I just opened to
view? Again, I had no issues in the 2000 world. That's not to say
everything was coded kosher - just that it worked.

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

**So I need to "Set WD = Nothing" at the end? Okay - but I'm not getting
there at this time.

If you have the patience, any further help is greatly appreciated.
Ed