Programmatically add reference to excel in word

[James]

I would like to take advantage of early binding in a macro I am
building, but I don't know if all the users will have a reference to
Excel in their Word. Can I add the reference programmatically if it is
missing?

 

[Word Heretic]

G'day "James" <[email protected]>,

Make the reference from your VBProject.

Steve Hudson - Word Heretic
Want a hyperlinked index? S/W R&D? See WordHeretic.com

steve from wordheretic.com (Email replies require payment)


James reckoned:

 

[James]

I tried the following w/o success:
Application.ActiveDocument.VBProject.References.AddFromFile "Microsoft
Excel 10.0 Object Library" or also addfromfile("Excel") - this does not
work - get error failed to load DLL. What am I doing wrong?

 

[Jean-Guy Marcil]

Bonjour,

Dans son message, < James > écrivait :
In this message, < James > wrote:

|| I tried the following w/o success:
|| Application.ActiveDocument.VBProject.References.AddFromFile "Microsoft
|| Excel 10.0 Object Library" or also addfromfile("Excel") - this does not
|| work - get error failed to load DLL. What am I doing wrong?

You have to state the actual path and filename of the Excel dll you want to
add a reference to.

But, as Steve pointed out, if you create the reference in your project via
the VBE window (Tools > References...), the reference will stick, i.e you do
not need to code for it.

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[James]

Are you advocating a manual creation of the reference? If not, can you
explain the steps I would need to take to create the reference in the
project via the VBE window programatically?

Thanks

 

[Ed]

Hi, Jean-Guy. When you say:

if you create the reference in your project via
the VBE window (Tools > References...), the reference will stick, i.e you do
not need to code for it.

does the reference stick to the document or to the template? If I create a
doc based on Normal, and set a macro in the doc's VB Project (AutoOpen,
let's say), are the object library references in my doc's VB Project or in
Normal's VB Project? If I want to give that document to someone else so
they can run the macro, will the reference move with the doc? Or would I
need to create a template to hold the references?

Ed

 

[Jean-Guy Marcil]

Bonjour,

Dans son message, < James > écrivait :
In this message, < James > wrote:

|| Are you advocating a manual creation of the reference? If not, can you

Yes.

|| explain the steps I would need to take to create the reference in the
|| project via the VBE window programatically?

For most projects, you do not need to do this programmatically. But since
you seem to be dead set on doing it that way, here is some code that may
bring you some joy:

'_______________________________________
Sub AddNewRef()

'Adding Excel
'
'To get the GUID number,
'and the Long for Major/Minor,
'Add the reference manually,
'Run GetNameofAllRef to get the name
'and then run the RemoveRef Sub

ActiveDocument.VBProject.References _
.AddFromGuid "{00020813-0000-0000-C000-000000000046}", 1, 4

End Sub
'_______________________________________

'_______________________________________
Sub RemoveRef()

Dim i As Long

With ActiveDocument.VBProject.References
For i = 1 To .Count
If .Item(i).Name = "Excel" Then
MsgBox .Item(i).Description
MsgBox .Item(i).GUID
MsgBox .Item(i).Major
MsgBox .Item(i).Minor
.Item(i).Collection.Remove .Item(i)
End If
Next i
End With

End Sub
'_______________________________________

'_______________________________________
Sub GetNameofAllRef()

Dim i As Long

With ActiveDocument.VBProject.References
For i = 1 To .Count
MsgBox .Item(i).Name
Next i
End With

End Sub
'_______________________________________

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Jean-Guy Marcil]

Bonjour,

Dans son message, < Ed > écrivait :
In this message, < Ed > wrote:

|| Hi, Jean-Guy. When you say:
||
||| if you create the reference in your project via
||| the VBE window (Tools > References...), the reference will stick, i.e
you do
||| not need to code for it.
||
|| does the reference stick to the document or to the template? If I create
a
|| doc based on Normal, and set a macro in the doc's VB Project (AutoOpen,
|| let's say), are the object library references in my doc's VB Project or
in
|| Normal's VB Project? If I want to give that document to someone else so
|| they can run the macro, will the reference move with the doc? Or would I
|| need to create a template to hold the references?
||

It is my understanding that they stick to the project. Projects are held by
templates or by documents.
But they stick regardless of the project "container".
Try it, Open several documents, all based on different templates, go in
each document's project and add different reference to a different library.
Close all your documents, and then open them one by one, the references
should still be there, as part of each document's project. But if you switch
between projects, the active references change.

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[James]

merci, Jean-Guy.

 

[Jean-Guy Marcil]

Bonjour,

Dans son message, < James > écrivait :
In this message, < James > wrote:

|| merci, Jean-Guy.

De rien James!

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org