problem with reference being replicated

[JB]

Hi Folks,
I have a template set which I use to attatch addins to a new/existing
document. My users are spread all over the place and do not have the
same environment/shares so this means I have multiple "copies" of teh
addin stored on admin shares.

What I do at the moment is determine (via registry settings) what they
have mapped to my addin location and add a reference to the addin using

MyProject.ThisDocument.VBProject.References.AddFromFile
AddinLocationandName which works fine and every new document based on
the template has the addin available to them.

Problem is that these docuemnts may be passed around environments which
makes the reference no longer valid as it's not on the same share. I
have a utility that allows them to open the doc which removes attached
template and adds the new addin location using

ActiveDocument.AttachedTemplate = "" and

addNo = AddIns.Count
For i = addNo To 1 Step -1
temp = AddIns.Item(i)
If temp = AddinName Then
AddIns.Item(i).Delete
End If
Next i

addtemp:
With ActiveDocument
.UpdateStylesOnOpen = True
.AttachedTemplate = Template(0)
End With
AddIns.Add FileName:=Template(1), Install:=True

This kinda works but I find myself with 2 addins installed if I look in
Tools > Templates and Addins, and I think it's something to do with not
removing the previous reference which I can't seem to do.

Any pointers???

Cheers

J

[Tom Winter]

OK, let's take a look at this.

I assume when you are talking about add-ins, you mean traditional .DOT
add-ins (global templates), not COM Add-Ins. My first question is, why do
you need to attach an add-in template to a document? Add-in templates are
meant primarily to provide macros, autotext, and menu changes to the entire
application. If you need to access these type of things in an add-in
template, you shouldn't have to attach it to a document. If you are using
the add-in template more like a traditional template (so that your document
can use the template's styles for example), then it should be a traditional
template, in the templates folder and it should not be an add-in template.

I assume it's not possible to have these add-ins/templates installed on the
local machines, correct?

To access "macros" in an add-in template, you do not need to add a reference
to it for the current document/project. The following are equivalent:

### Example 1 ###

' MyProject.ThisDocument has had a reference added to the template
Macros.dot. In this case, Macros.dot does not need to be loaded as an add-in
(global) template.

Sub Test

MacrosProjectName.ModuleName.PublicRoutineName x, y, z ' You don't
really need to include .ModuleName, but you can if you want.

End Sub

### Example 2 ###

' Macros.dot is loaded as an add-in (global) template, either by the user
from TOOLS | TEMPLATES AND ADD-INS, by code, or by being in the STARTUP
folder.

Sub Test

Application.Run "PublicRoutineName", x, y, z ' Application.Run in
Word 2000 or later will return the return value from the routine.

End Sub

### Example 3 ###

' The document has it's attached template set to Macros.dot

Sub Test

MacrosProjectName.ModuleName.PublicRoutineName x, y, z ' You don't
really need to include .ModuleName, but you can if you want.

End Sub

####

So if we're just talking about a macro here, you can either go the Reference
route or the Add-In route or the attached template route, but you don't have
to do all three, which it kind of looks like you are trying to do.

I'll be honest, I haven't seen people adding references to .dot files very
often. Setting the attached template or using an add-in is the usual route
here. I think you need to look at WHY you need this add-in (macros only or
other reasons) and then look at the best SINGLE way to reference it. Once
you know that, you can probably use the Document_Open() event to reset the
reference, add-in or attached template to an available file.

Let me know what's in these templates/add-ins and we can think this through.
--
Tom Winter

Post by JB
Hi Folks,
I have a template set which I use to attatch addins to a new/existing
document. My users are spread all over the place and do not have the same
environment/shares so this means I have multiple "copies" of teh addin
stored on admin shares.
What I do at the moment is determine (via registry settings) what they
have mapped to my addin location and add a reference to the addin using
MyProject.ThisDocument.VBProject.References.AddFromFile
AddinLocationandName which works fine and every new document based on the
template has the addin available to them.
Problem is that these docuemnts may be passed around environments which
makes the reference no longer valid as it's not on the same share. I have
a utility that allows them to open the doc which removes attached template
and adds the new addin location using
ActiveDocument.AttachedTemplate = "" and
addNo = AddIns.Count
For i = addNo To 1 Step -1
temp = AddIns.Item(i)
If temp = AddinName Then
AddIns.Item(i).Delete
End If
Next i
With ActiveDocument
.UpdateStylesOnOpen = True
.AttachedTemplate = Template(0)
End With
AddIns.Add FileName:=Template(1), Install:=True
This kinda works but I find myself with 2 addins installed if I look in
Tools > Templates and Addins, and I think it's something to do with not
removing the previous reference which I can't seem to do.
Any pointers???
Cheers
J

[JB]

Post by Tom Winter
OK, let's take a look at this.
I assume when you are talking about add-ins, you mean traditional .DOT
add-ins (global templates), not COM Add-Ins. My first question is, why do
you need to attach an add-in template to a document? Add-in templates are
meant primarily to provide macros, autotext, and menu changes to the entire
application. If you need to access these type of things in an add-in
template, you shouldn't have to attach it to a document. If you are using
the add-in template more like a traditional template (so that your document
can use the template's styles for example), then it should be a traditional
template, in the templates folder and it should not be an add-in template.
I assume it's not possible to have these add-ins/templates installed on the
local machines, correct?
To access "macros" in an add-in template, you do not need to add a reference
### Example 1 ###
' MyProject.ThisDocument has had a reference added to the template
Macros.dot. In this case, Macros.dot does not need to be loaded as an add-in
(global) template.
Sub Test
MacrosProjectName.ModuleName.PublicRoutineName x, y, z ' You don't
really need to include .ModuleName, but you can if you want.
End Sub
### Example 2 ###
' Macros.dot is loaded as an add-in (global) template, either by the user
from TOOLS | TEMPLATES AND ADD-INS, by code, or by being in the STARTUP
folder.
Sub Test
Application.Run "PublicRoutineName", x, y, z ' Application.Run in
Word 2000 or later will return the return value from the routine.
End Sub
### Example 3 ###
' The document has it's attached template set to Macros.dot
Sub Test
MacrosProjectName.ModuleName.PublicRoutineName x, y, z ' You don't
really need to include .ModuleName, but you can if you want.
End Sub
####
So if we're just talking about a macro here, you can either go the Reference
route or the Add-In route or the attached template route, but you don't have
to do all three, which it kind of looks like you are trying to do.
I'll be honest, I haven't seen people adding references to .dot files very
often. Setting the attached template or using an add-in is the usual route
here. I think you need to look at WHY you need this add-in (macros only or
other reasons) and then look at the best SINGLE way to reference it. Once
you know that, you can probably use the Document_Open() event to reset the
reference, add-in or attached template to an available file.
Let me know what's in these templates/add-ins and we can think this through.

Hi Tom,
Thanks for replying.

I am indeed using a traditional .dot addin. I use this to store all my
subs/functions and forms which are used across many different templates.
The templates attached are used to give formatting and toolbars to the
document being opened/created.

The templates themselves have a single common module which
connects/references the addin (all are in a shared location, it's not
not possible to have these on local machines as I need to be able to
update them easily and we work in a very mixed and complex environment
for various reasons) so I can have a common code set.

I have 10 document templates for styles/toolbars and 1 addin holding
code and forms etc. which is updated with new/better functionallity as
it becomes necessary/available.

Your first example pretty much sums up what I do to call the functions I
need in the initial setup using
MyProject.ThisDocument.VBProject.References.AddFromFile "Z:\addin.dot"

Additional to this I must allow users to switch environment and open the
document from somewhere which may not have the share set up as
Z:\addin.dot, so the need arises to set a new reference or amend the
reference.

Hope this is a bit clearer

Cheers

J

[Tom Winter]

Well, first let's look at the add-ins. Here's some code that might help you
(it's VB6 code, but should work in VBA).

I know recall way back when I had problems reloading traditional add-ins,
getting multiple entries, as you talked about. The ReInstallWordAddIn()
should do what you need. It will remove all instances of an add-in with a
particular file name, no matter where it's located. It will then load only
the one you specify. As long as your add-in file is uniquely named, this
should keep you from having multiple references to the add-in.

Public Function GetWordAddIn(oWord As Word.Application, sName As String) As
Word.AddIn

' sName is just the file name of the add-in, without path. For example,
"add-in.dot".

On Error GoTo ErrorHandler

Dim oCurrAddIn As Word.AddIn

For Each oCurrAddIn In oWord.AddIns

If StrComp(oCurrAddIn.Name, sName, vbTextCompare) = 0 Then

Set GetWordAddIn = oCurrAddIn

Exit Function

End If

Next

Set GetWordAddIn = Nothing

Exit Function

ErrorHandler:

Set GetWordAddIn = Nothing

End Function

Public Sub RemoveWordAddIn(oWord As Word.Application, sName As String)

' sName is just the file name of the add-in, without path. For example,
"add-in.dot".

On Error GoTo ErrorHandler

Dim oWordAddIn As Word.AddIn

Set oWordAddIn = GetWordAddIn(oWord, sName)

If Not oWordAddIn Is Nothing Then

' The 'Delete' method doesn't actually delete the add-in file,
it just
' turns it off. It removes the add-in from the add-ins list.
It's like
' clicking the Remove button the Templates and Add-Ins dialog.

oWordAddIn.Delete

End If

Exit Sub

ErrorHandler:

Exit Sub

End Sub

Public Sub ReInstallWordAddIn(oWord As Word.Application, sPathAndName As
String)

' sPathAndName is the full path and name of the add-in file. For
example, "Z:\Application\addin.dot".

' This routine will remove ALL instances of an add-in with the name
"addin.dot" (for example),
' no matter where that add-in is located. It will then load the specific
add-in you're passed in.
' You'll then know that there is only one copy of "addin.dot" loaded.

On Error GoTo ErrorHandler

RemoveWordAddIn oWord, GetFilePart(sPathAndName)

oWord.AddIns.Add sPathAndName, True

Exit Sub

ErrorHandler:

Exit Sub ' You may want to do something more drastic here...

End Sub

[Tom Winter]

Oops! Looking over my code, I realized I lied to you. That code doesn't
remove ALL the add-ins with the same name, only the first. Here's some
better code:

Public Sub ReInstallWordAddIn(oWord As Word.Application, sPathAndName As
String)

' sPathAndName is the full path and name of the add-in file. For
example, "Z:\Application\addin.dot".

' This routine will remove ALL instances of an add-in with the name
"addin.dot" (for example),
' no matter where that add-in is located. It will then load the specific
add-in you're passed in.
' You'll then know that there is only one copy of "addin.dot" loaded.

On Error GoTo ErrorHandler

RemoveWordAddIns oWord, GetFilePart(sPathAndName)

oWord.Addins.Add sPathAndName, True

Exit Sub

ErrorHandler:

Exit Sub ' You may want to do something more drastic here...

End Sub

Public Function GetWordAddIn(oWord As Word.Application, sName As String) As
Word.AddIn

' sName is just the file name of the add-in, without path. For example,
"add-in.dot".

On Error GoTo ErrorHandler

Dim oCurrAddIn As Word.AddIn

For Each oCurrAddIn In oWord.Addins

If StrComp(oCurrAddIn.Name, sName, vbTextCompare) = 0 Then

Set GetWordAddIn = oCurrAddIn

Exit Function

End If

Next

Set GetWordAddIn = Nothing

Exit Function

ErrorHandler:

Set GetWordAddIn = Nothing

End Function

Public Function RemoveWordAddIn(oWord As Word.Application, sName As String)
As Boolean

' sName is just the file name of the add-in, without path. For example,
"add-in.dot".

On Error GoTo ErrorHandler

Dim oWordAddIn As Word.AddIn

Set oWordAddIn = GetWordAddIn(oWord, sName)

If Not oWordAddIn Is Nothing Then

' The 'Delete' method doesn't actually delete the add-in file,
It just
' turns it off. It removes the add-in from the add-ins list. It
's like
' clicking the Remove button the Templates and Add-Ins dialog.

oWordAddIn.Delete

RemoveWordAddIn = True

Else

RemoveWordAddIn = False

End If

Exit Function

ErrorHandler:

RemoveWordAddIn = False

End Function

Public Sub RemoveWordAddIns(oWord As Word.Application, sName As String)

' sName is just the file name of the add-in, without path. For example,
"add-in.dot".

On Error GoTo ErrorHandler

Dim bContinue As Boolean

Do

bContinue = RemoveWordAddIn(oWord, sName)

Loop While bContinue

Exit Sub

ErrorHandler:

Exit Sub

End Sub

Public Function GetFilePart(sPath As String) As String

On Error GoTo ErrorHandler

GetFilePart = Right$(sPath, Len(sPath) - InStrRev(sPath, "\", ,
vbBinaryCompare))

Exit Function

ErrorHandler:

GetFilePart = ""

End Function
--
Tom Winter

Post by Tom Winter
Well, first let's look at the add-ins. Here's some code that might help
you (it's VB6 code, but should work in VBA).
I know recall way back when I had problems reloading traditional add-ins,
getting multiple entries, as you talked about. The ReInstallWordAddIn()
should do what you need. It will remove all instances of an add-in with a
particular file name, no matter where it's located. It will then load only
the one you specify. As long as your add-in file is uniquely named, this
should keep you from having multiple references to the add-in.
Public Function GetWordAddIn(oWord As Word.Application, sName As String)
As Word.AddIn
' sName is just the file name of the add-in, without path. For example,
"add-in.dot".
On Error GoTo ErrorHandler
Dim oCurrAddIn As Word.AddIn
For Each oCurrAddIn In oWord.AddIns
If StrComp(oCurrAddIn.Name, sName, vbTextCompare) = 0 Then
Set GetWordAddIn = oCurrAddIn
Exit Function
End If
Next
Set GetWordAddIn = Nothing
Exit Function
Set GetWordAddIn = Nothing
End Function
Public Sub RemoveWordAddIn(oWord As Word.Application, sName As String)
' sName is just the file name of the add-in, without path. For example,
"add-in.dot".
On Error GoTo ErrorHandler
Dim oWordAddIn As Word.AddIn
Set oWordAddIn = GetWordAddIn(oWord, sName)
If Not oWordAddIn Is Nothing Then
' The 'Delete' method doesn't actually delete the add-in file,
it just
' turns it off. It removes the add-in from the add-ins list.
It's like
' clicking the Remove button the Templates and Add-Ins dialog.
oWordAddIn.Delete
End If
Exit Sub
Exit Sub
End Sub
Public Sub ReInstallWordAddIn(oWord As Word.Application, sPathAndName As
String)
' sPathAndName is the full path and name of the add-in file. For
example, "Z:\Application\addin.dot".
' This routine will remove ALL instances of an add-in with the name
"addin.dot" (for example),
' no matter where that add-in is located. It will then load the
specific add-in you're passed in.
' You'll then know that there is only one copy of "addin.dot" loaded.
On Error GoTo ErrorHandler
RemoveWordAddIn oWord, GetFilePart(sPathAndName)
oWord.AddIns.Add sPathAndName, True
Exit Sub
Exit Sub ' You may want to do something more drastic here...
End Sub
--
Tom Winter

[Tom Winter]

OK, here's some similar code for References

Public Sub ReInstallVBReference(oVBProject As VBIDE.VBProject, sPathAndName
As String)

' sPathAndName is the full path and name of the document or template.
For example, "Z:\Application\addin.dot".

On Error GoTo ErrorHandler

RemoveVBReferences oVBProject, GetFilePart(sPathAndName)

oVBProject.References.AddFromFile sPathAndName

Exit Sub

ErrorHandler:

Exit Sub ' You may want to do something more drastic here...

End Sub

Public Function GetVBReference(oVBProject As VBIDE.VBProject, sName As
String) As VBIDE.Reference

' sName is the name of the document or template, without path. For
example, "addin.dot".

On Error GoTo ErrorHandler

Dim oCurrReference As VBIDE.Reference

For Each oCurrReference In oVBProject.References

If StrComp(GetFilePart(oCurrReference.FullPath), sName,
vbTextCompare) = 0 Then

Set GetVBReference = oCurrReference

Exit Function

End If

Next

Set GetVBReference = Nothing

Exit Function

ErrorHandler:

Set GetVBReference = Nothing

End Function

Public Function RemoveVBReference(oVBProject As VBIDE.VBProject, sName As
String) As Boolean

' sName is the name of the document or template, without path. For
example, "addin.dot".

On Error GoTo ErrorHandler

Dim oReference As Word.AddIn

Set oReference = GetVBReference(oVBProject, sName)

If Not oReference Is Nothing Then

oReference.Delete

RemoveVBReference = True

Else

RemoveVBReference = False

End If

Exit Function

ErrorHandler:

RemoveVBReference = False

End Function

Public Sub RemoveVBReferences(oVBProject As VBIDE.VBProject, sName As
String)

' sName is the name of the document or template, without path. For
example, "addin.dot".

On Error GoTo ErrorHandler

Dim bContinue As Boolean

Do

bContinue = RemoveVBReference(oVBProject, sName)

Loop While bContinue

Exit Sub

ErrorHandler:

Exit Sub

End Sub

Public Function GetFilePart(sPath As String) As String

On Error GoTo ErrorHandler

GetFilePart = Right$(sPath, Len(sPath) - InStrRev(sPath, "\", ,
vbBinaryCompare))

Exit Function

ErrorHandler:

GetFilePart = ""

End Function

[JB]

Post by Tom Winter
OK, here's some similar code for References
Public Sub ReInstallVBReference(oVBProject As VBIDE.VBProject, sPathAndName
As String)
' sPathAndName is the full path and name of the document or template.
For example, "Z:\Application\addin.dot".
On Error GoTo ErrorHandler
RemoveVBReferences oVBProject, GetFilePart(sPathAndName)
oVBProject.References.AddFromFile sPathAndName
Exit Sub
Exit Sub ' You may want to do something more drastic here...
End Sub
Public Function GetVBReference(oVBProject As VBIDE.VBProject, sName As
String) As VBIDE.Reference
' sName is the name of the document or template, without path. For
example, "addin.dot".
On Error GoTo ErrorHandler
Dim oCurrReference As VBIDE.Reference
For Each oCurrReference In oVBProject.References
If StrComp(GetFilePart(oCurrReference.FullPath), sName,
vbTextCompare) = 0 Then
Set GetVBReference = oCurrReference
Exit Function
End If
Next
Set GetVBReference = Nothing
Exit Function
Set GetVBReference = Nothing
End Function
Public Function RemoveVBReference(oVBProject As VBIDE.VBProject, sName As
String) As Boolean
' sName is the name of the document or template, without path. For
example, "addin.dot".
On Error GoTo ErrorHandler
Dim oReference As Word.AddIn
Set oReference = GetVBReference(oVBProject, sName)
If Not oReference Is Nothing Then
oReference.Delete
RemoveVBReference = True
Else
RemoveVBReference = False
End If
Exit Function
RemoveVBReference = False
End Function
Public Sub RemoveVBReferences(oVBProject As VBIDE.VBProject, sName As
String)
' sName is the name of the document or template, without path. For
example, "addin.dot".
On Error GoTo ErrorHandler
Dim bContinue As Boolean
Do
bContinue = RemoveVBReference(oVBProject, sName)
Loop While bContinue
Exit Sub
Exit Sub
End Sub
Public Function GetFilePart(sPath As String) As String
On Error GoTo ErrorHandler
GetFilePart = Right$(sPath, Len(sPath) - InStrRev(sPath, "\", ,
vbBinaryCompare))
Exit Function
GetFilePart = ""
End Function

Hi Tom,
Thanks for the examples! I'll give them a try and let you know how I
get on.

Cheers

J

[JB]

Post by Tom Winter
OK, here's some similar code for References
Public Sub ReInstallVBReference(oVBProject As VBIDE.VBProject, sPathAndName
As String)
' sPathAndName is the full path and name of the document or template.
For example, "Z:\Application\addin.dot".
On Error GoTo ErrorHandler
RemoveVBReferences oVBProject, GetFilePart(sPathAndName)
oVBProject.References.AddFromFile sPathAndName
Exit Sub
Exit Sub ' You may want to do something more drastic here...
End Sub
Public Function GetVBReference(oVBProject As VBIDE.VBProject, sName As
String) As VBIDE.Reference
' sName is the name of the document or template, without path. For
example, "addin.dot".
On Error GoTo ErrorHandler
Dim oCurrReference As VBIDE.Reference
For Each oCurrReference In oVBProject.References
If StrComp(GetFilePart(oCurrReference.FullPath), sName,
vbTextCompare) = 0 Then
Set GetVBReference = oCurrReference
Exit Function
End If
Next
Set GetVBReference = Nothing
Exit Function
Set GetVBReference = Nothing
End Function
Public Function RemoveVBReference(oVBProject As VBIDE.VBProject, sName As
String) As Boolean
' sName is the name of the document or template, without path. For
example, "addin.dot".
On Error GoTo ErrorHandler
Dim oReference As Word.AddIn
Set oReference = GetVBReference(oVBProject, sName)
If Not oReference Is Nothing Then
oReference.Delete
RemoveVBReference = True
Else
RemoveVBReference = False
End If
Exit Function
RemoveVBReference = False
End Function
Public Sub RemoveVBReferences(oVBProject As VBIDE.VBProject, sName As
String)
' sName is the name of the document or template, without path. For
example, "addin.dot".
On Error GoTo ErrorHandler
Dim bContinue As Boolean
Do
bContinue = RemoveVBReference(oVBProject, sName)
Loop While bContinue
Exit Sub
Exit Sub
End Sub
Public Function GetFilePart(sPath As String) As String
On Error GoTo ErrorHandler
GetFilePart = Right$(sPath, Len(sPath) - InStrRev(sPath, "\", ,
vbBinaryCompare))
Exit Function
GetFilePart = ""
End Function

Hi Tom,
I couldn't even get this code initialized as it came up with error
message "User Defined Type not Defined" highlighting the line "Public
Sub ReInstallVBReference(oVBProject As VBIDE.VBProject, sPathAndName As
String"
I don't really understand what the VBIDE.VBProject part is can you
explain please?

Cheers

J

[Tom Winter]

Post by JB

Post by Tom Winter
OK, here's some similar code for References
Public Sub ReInstallVBReference(oVBProject As VBIDE.VBProject,
sPathAndName As String)
' sPathAndName is the full path and name of the document or template.
For example, "Z:\Application\addin.dot".
On Error GoTo ErrorHandler
RemoveVBReferences oVBProject, GetFilePart(sPathAndName)
oVBProject.References.AddFromFile sPathAndName
Exit Sub
Exit Sub ' You may want to do something more drastic here...
End Sub
Public Function GetVBReference(oVBProject As VBIDE.VBProject, sName As
String) As VBIDE.Reference
' sName is the name of the document or template, without path. For
example, "addin.dot".
On Error GoTo ErrorHandler
Dim oCurrReference As VBIDE.Reference
For Each oCurrReference In oVBProject.References
If StrComp(GetFilePart(oCurrReference.FullPath), sName,
vbTextCompare) = 0 Then
Set GetVBReference = oCurrReference
Exit Function
End If
Next
Set GetVBReference = Nothing
Exit Function
Set GetVBReference = Nothing
End Function
Public Function RemoveVBReference(oVBProject As VBIDE.VBProject, sName As
String) As Boolean
' sName is the name of the document or template, without path. For
example, "addin.dot".
On Error GoTo ErrorHandler
Dim oReference As Word.AddIn
Set oReference = GetVBReference(oVBProject, sName)
If Not oReference Is Nothing Then
oReference.Delete
RemoveVBReference = True
Else
RemoveVBReference = False
End If
Exit Function
RemoveVBReference = False
End Function
Public Sub RemoveVBReferences(oVBProject As VBIDE.VBProject, sName As
String)
' sName is the name of the document or template, without path. For
example, "addin.dot".
On Error GoTo ErrorHandler
Dim bContinue As Boolean
Do
bContinue = RemoveVBReference(oVBProject, sName)
Loop While bContinue
Exit Sub
Exit Sub
End Sub
Public Function GetFilePart(sPath As String) As String
On Error GoTo ErrorHandler
GetFilePart = Right$(sPath, Len(sPath) - InStrRev(sPath, "\", ,
vbBinaryCompare))
Exit Function
GetFilePart = ""
End Function

Hi Tom,
I couldn't even get this code initialized as it came up with error message
"User Defined Type not Defined" highlighting the line "Public Sub
ReInstallVBReference(oVBProject As VBIDE.VBProject, sPathAndName As
String"
I don't really understand what the VBIDE.VBProject part is can you explain
please?
Cheers
J

VBIDE is the "project" (type library really) that defines the objects
VBProject, Reference, etc. You'll need to add a reference to it in the
projects that use my code. In VBA, go to TOOLS | REFERENCES and look for
"Microsoft Visual Basic for Application Extensibility x.x" (It will usually
end with 5.3). Check that and hit OK and you should be all set.