I use the below code to check for an AddIn, the problem I have is the line
marked * sometimes causes an if the file is missing, but sometime the error
code is ZERO, even though the file is not installed.

I have not quite worked out what is going on, but this line seems to be
inconsistant in its result.


Dim WBName As String
On Error Resume Next ' turn off error checking
* WBName = Workbooks(DCMaster2).Name
LastError = Err
On Error GoTo 0 ' restore error checking
If LastError < 0 Then
' the add-in workbook isn't currently open. Manually open it.
On Error Resume Next
Application.DisplayAlerts = False
With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2)
.Installed = False
End With
With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2)
.Installed = True
End With
Application.DisplayAlerts = True
LastError = Err
On Error GoTo 0 ' restore error checking
If LastError < 0 Then
' The workbook was not found in the correct location
AddinPresent = False
Else
' The workbook was found and installed
AddinPresent = True
End If
Else
' No error so the workbook is already loaded
AddinPresent = True
End If
End Function



--
Trefor

is DCMaster of the form Myaddin.xla or just MyAddin

If the latter, then always use the extension.

--
Regards,
Tom Ogilvy


"Trefor" wrote:

I use the below code to check for an AddIn, the problem I have is the line
marked * sometimes causes an if the file is missing, but sometime the error
code is ZERO, even though the file is not installed.

I have not quite worked out what is going on, but this line seems to be
inconsistant in its result.


Dim WBName As String
On Error Resume Next ' turn off error checking
* WBName = Workbooks(DCMaster2).Name
LastError = Err
On Error GoTo 0 ' restore error checking
If LastError < 0 Then
' the add-in workbook isn't currently open. Manually open it.
On Error Resume Next
Application.DisplayAlerts = False
With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2)
.Installed = False
End With
With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2)
.Installed = True
End With
Application.DisplayAlerts = True
LastError = Err
On Error GoTo 0 ' restore error checking
If LastError < 0 Then
' The workbook was not found in the correct location
AddinPresent = False
Else
' The workbook was found and installed
AddinPresent = True
End If
Else
' No error so the workbook is already loaded
AddinPresent = True
End If
End Function



--
Trefor

Tom,

Thanks for the reply.

Public Const DCMaster = "'Customer Data Collect Master.xla'"

Dim DCMaster2 As String
DCMaster2 = Replace(DCMaster, "'", "")

--
Trefor


"Tom Ogilvy" wrote:

is DCMaster of the form Myaddin.xla or just MyAddin

If the latter, then always use the extension.

--
Regards,
Tom Ogilvy


"Trefor" wrote:

I use the below code to check for an AddIn, the problem I have is the line
marked * sometimes causes an if the file is missing, but sometime the error
code is ZERO, even though the file is not installed.

I have not quite worked out what is going on, but this line seems to be
inconsistant in its result.


Dim WBName As String
On Error Resume Next ' turn off error checking
* WBName = Workbooks(DCMaster2).Name
LastError = Err
On Error GoTo 0 ' restore error checking
If LastError < 0 Then
' the add-in workbook isn't currently open. Manually open it.
On Error Resume Next
Application.DisplayAlerts = False
With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2)
.Installed = False
End With
With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2)
.Installed = True
End With
Application.DisplayAlerts = True
LastError = Err
On Error GoTo 0 ' restore error checking
If LastError < 0 Then
' The workbook was not found in the correct location
AddinPresent = False
Else
' The workbook was found and installed
AddinPresent = True
End If
Else
' No error so the workbook is already loaded
AddinPresent = True
End If
End Function



--
Trefor

This might be useful to you.

A means to check if a reference exists from a template and a method to
remove the reference later so others can read the Workbook you created
from the Template and the AddIn.


Put these Subs into the Template:

Private Sub Workbook_Open()
If ReferenceExists("MyAddInName") Then
' call StartUpSub in the NameOfAddInModule
NameOfAddInModule.StartUpSub Me
End If
End Sub

Private Function ReferenceExists(reference As String) As Boolean
Dim result As Boolean
result = False

On Error Resume Next
result = Not Me.VBProject.References(reference) Is Nothing

ReferenceExists = result
End Function


Then in the Template call this:


Public Sub RemoveReferences(book As Workbook)
book.VBProject.References.Remove
book.VBProject.References("MyAddInName")
End Sub

Before you try to install it, you can always check for its existence

If dir(Application.UserLibraryPath & DCMaster2) = "" then
'Not in correct location

Also,

With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2)
.Installed = False
End With
With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2)
.Installed = True
End With

Seems flawed. Why install it twice.

Perhaps
With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2)
.Installed = False
.Installed = True
End with

although I don't think the installed = False is necessary if it wasn't in
the menu to begin with.

Nonetheless, I doubt that would account for your anomally. Checking if the
file exists with Dir would seem the most straightforward.

--
Regards,
Tom Ogilvy



"Trefor" wrote:

Tom,

Thanks for the reply.

Public Const DCMaster = "'Customer Data Collect Master.xla'"

Dim DCMaster2 As String
DCMaster2 = Replace(DCMaster, "'", "")

--
Trefor


"Tom Ogilvy" wrote:

is DCMaster of the form Myaddin.xla or just MyAddin

If the latter, then always use the extension.

--
Regards,
Tom Ogilvy


"Trefor" wrote:

I use the below code to check for an AddIn, the problem I have is the line
marked * sometimes causes an if the file is missing, but sometime the error
code is ZERO, even though the file is not installed.

I have not quite worked out what is going on, but this line seems to be
inconsistant in its result.


Dim WBName As String
On Error Resume Next ' turn off error checking
* WBName = Workbooks(DCMaster2).Name
LastError = Err
On Error GoTo 0 ' restore error checking
If LastError < 0 Then
' the add-in workbook isn't currently open. Manually open it.
On Error Resume Next
Application.DisplayAlerts = False
With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2)
.Installed = False
End With
With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2)
.Installed = True
End With
Application.DisplayAlerts = True
LastError = Err
On Error GoTo 0 ' restore error checking
If LastError < 0 Then
' The workbook was not found in the correct location
AddinPresent = False
Else
' The workbook was found and installed
AddinPresent = True
End If
Else
' No error so the workbook is already loaded
AddinPresent = True
End If
End Function



--
Trefor

Tom,


Perhaps I should explain what I am trying to achieve:

I am distributing and Addin as a means of patching or fixing up code. So
this is what I do:


Check to see if the file in a directory is different to the one in
€œApplication.UserLibraryPath€�:

If Not FileMatch(DCMaster2, AppPath, Application.UserLibraryPath) Then


I then remove the Addin from Excel as I found I was getting Error = 70 on
the copy:

With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2)
.Installed = False
End With


Then I copy the file from the directory to the Library:

FileCopy AppPath & "\" & DCMaster2, Application.UserLibraryPath & DCMaster2


Then whether the above runs or not, I check to see if the addin is loaded or
not:


WBName = Workbooks(DCMaster2).Name -Do I need to specify a path here??
LastError = Err
On Error GoTo 0 ' restore error checking

If the Err =0 then I had presumed that the Addin had already loaded.
Otherwise it would need loading:

With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2)
.Installed = False  I added this line because if there had been
a previous .Installed=True on the same file, but I had removed it for a
filecopy and I simply did a .Installed = True it would not load the file
because there was already a reference/link.
.Installed = True
End With
--
Trefor


"Tom Ogilvy" wrote:

Before you try to install it, you can always check for its existence

If dir(Application.UserLibraryPath & DCMaster2) = "" then
'Not in correct location

Also,

With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2)
.Installed = False
End With
With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2)
.Installed = True
End With

Seems flawed. Why install it twice.

Perhaps
With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2)
.Installed = False
.Installed = True
End with

although I don't think the installed = False is necessary if it wasn't in
the menu to begin with.

Nonetheless, I doubt that would account for your anomally. Checking if the
file exists with Dir would seem the most straightforward.

--
Regards,
Tom Ogilvy



"Trefor" wrote:

Tom,

Thanks for the reply.

Public Const DCMaster = "'Customer Data Collect Master.xla'"

Dim DCMaster2 As String
DCMaster2 = Replace(DCMaster, "'", "")

--
Trefor


"Tom Ogilvy" wrote:

is DCMaster of the form Myaddin.xla or just MyAddin

If the latter, then always use the extension.

--
Regards,
Tom Ogilvy


"Trefor" wrote:

I use the below code to check for an AddIn, the problem I have is the line
marked * sometimes causes an if the file is missing, but sometime the error
code is ZERO, even though the file is not installed.

I have not quite worked out what is going on, but this line seems to be
inconsistant in its result.


Dim WBName As String
On Error Resume Next ' turn off error checking
* WBName = Workbooks(DCMaster2).Name
LastError = Err
On Error GoTo 0 ' restore error checking
If LastError < 0 Then
' the add-in workbook isn't currently open. Manually open it.
On Error Resume Next
Application.DisplayAlerts = False
With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2)
.Installed = False
End With
With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2)
.Installed = True
End With
Application.DisplayAlerts = True
LastError = Err
On Error GoTo 0 ' restore error checking
If LastError < 0 Then
' The workbook was not found in the correct location
AddinPresent = False
Else
' The workbook was found and installed
AddinPresent = True
End If
Else
' No error so the workbook is already loaded
AddinPresent = True
End If
End Function



--
Trefor

gimme_this_gimme_that,

Thankyou for your reply. Please see my reply to Tom re what I am trying to
do here. I need to think about what you have sent me to see if it helps, so
thankyou for now.
--
Trefor


" wrote:

This might be useful to you.

A means to check if a reference exists from a template and a method to
remove the reference later so others can read the Workbook you created
from the Template and the AddIn.


Put these Subs into the Template:

Private Sub Workbook_Open()
If ReferenceExists("MyAddInName") Then
' call StartUpSub in the NameOfAddInModule
NameOfAddInModule.StartUpSub Me
End If
End Sub

Private Function ReferenceExists(reference As String) As Boolean
Dim result As Boolean
result = False

On Error Resume Next
result = Not Me.VBProject.References(reference) Is Nothing

ReferenceExists = result
End Function


Then in the Template call this:


Public Sub RemoveReferences(book As Workbook)
book.VBProject.References.Remove
book.VBProject.References("MyAddInName")
End Sub

Hi Trefor,

I haven't followed all this thread but have you tried -

Dim wbAddin As AddIn, wb as Workbook
'sTitle the workbook.title of your addin that you se in file properties

on error resume next
Set wbAddin = Application.AddIns(sTitle)

If not wbAddin is Nothing then

sPath = wbAddin.path
bInstalled = wbAddin.Installed ' ticked in addin manager

If it's not installed and not in one of the default addin paths it will not
be visible in Tools addins but a reference will exist in the registry.

Else
' is it loaded from file but not in the addin manager
set wb = application.workbooks("myAddin.xla")
sPath = wb.path

If the old addin is not in UserLibraryPath or LibraryPath, and it's in the
Addin's collection (whether or not installed) suggest install the updated
addin in the old path after uninstalling (if necessary) and removing the old
file. Otherwise the old addin's details will remain in the registry.

Regards,
Peter T


"Trefor" wrote in message
...
Tom,


Perhaps I should explain what I am trying to achieve:

I am distributing and Addin as a means of patching or fixing up code. So
this is what I do:


Check to see if the file in a directory is different to the one in
"Application.UserLibraryPath":

If Not FileMatch(DCMaster2, AppPath, Application.UserLibraryPath) Then


I then remove the Addin from Excel as I found I was getting Error = 70 on
the copy:

With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2)
.Installed = False
End With


Then I copy the file from the directory to the Library:

FileCopy AppPath & "\" & DCMaster2, Application.UserLibraryPath &
DCMaster2


Then whether the above runs or not, I check to see if the addin is loaded
or
not:


WBName = Workbooks(DCMaster2).Name ?-Do I need to specify a path
here??
LastError = Err
On Error GoTo 0 ' restore error checking

If the Err =0 then I had presumed that the Addin had already loaded.
Otherwise it would need loading:

With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2)
.Installed = False ? I added this line because if there had
been
a previous .Installed=True on the same file, but I had removed it for a
filecopy and I simply did a .Installed = True it would not load the file
because there was already a reference/link.
.Installed = True
End With
--
Trefor


"Tom Ogilvy" wrote:

Before you try to install it, you can always check for its existence

If dir(Application.UserLibraryPath & DCMaster2) = "" then
'Not in correct location

Also,

With AddIns.Add(FileName:=Application.UserLibraryPath &
DCMaster2)
.Installed = False
End With
With AddIns.Add(FileName:=Application.UserLibraryPath &
DCMaster2)
.Installed = True
End With

Seems flawed. Why install it twice.

Perhaps
With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2)
.Installed = False
.Installed = True
End with

although I don't think the installed = False is necessary if it wasn't
in
the menu to begin with.

Nonetheless, I doubt that would account for your anomally. Checking if
the
file exists with Dir would seem the most straightforward.

--
Regards,
Tom Ogilvy



"Trefor" wrote:

Tom,

Thanks for the reply.

Public Const DCMaster = "'Customer Data Collect Master.xla'"

Dim DCMaster2 As String
DCMaster2 = Replace(DCMaster, "'", "")

--
Trefor


"Tom Ogilvy" wrote:

is DCMaster of the form Myaddin.xla or just MyAddin

If the latter, then always use the extension.

--
Regards,
Tom Ogilvy


"Trefor" wrote:

I use the below code to check for an AddIn, the problem I have is
the line
marked * sometimes causes an if the file is missing, but sometime
the error
code is ZERO, even though the file is not installed.

I have not quite worked out what is going on, but this line seems
to be
inconsistant in its result.


Dim WBName As String
On Error Resume Next ' turn off error checking
* WBName = Workbooks(DCMaster2).Name
LastError = Err
On Error GoTo 0 ' restore error checking
If LastError < 0 Then
' the add-in workbook isn't currently open. Manually open it.
On Error Resume Next
Application.DisplayAlerts = False
With AddIns.Add(FileName:=Application.UserLibraryPath &
DCMaster2)
.Installed = False
End With
With AddIns.Add(FileName:=Application.UserLibraryPath &
DCMaster2)
.Installed = True
End With
Application.DisplayAlerts = True
LastError = Err
On Error GoTo 0 ' restore error checking
If LastError < 0 Then
' The workbook was not found in the correct location
AddinPresent = False
Else
' The workbook was found and installed
AddinPresent = True
End If
Else
' No error so the workbook is already loaded
AddinPresent = True
End If
End Function



--
Trefor

gimme_this_gimme_that,

I get an Error, "Invalid use of Me Keyword"

--
Trefor


" wrote:

This might be useful to you.

A means to check if a reference exists from a template and a method to
remove the reference later so others can read the Workbook you created
from the Template and the AddIn.


Put these Subs into the Template:

Private Sub Workbook_Open()
If ReferenceExists("MyAddInName") Then
' call StartUpSub in the NameOfAddInModule
NameOfAddInModule.StartUpSub Me
End If
End Sub

Private Function ReferenceExists(reference As String) As Boolean
Dim result As Boolean
result = False

On Error Resume Next
result = Not Me.VBProject.References(reference) Is Nothing

ReferenceExists = result
End Function


Then in the Template call this:


Public Sub RemoveReferences(book As Workbook)
book.VBProject.References.Remove
book.VBProject.References("MyAddInName")
End Sub

Looks like that code is checking for a reference to the addin - not sure
what it has to do with the question you asked.

--
Regards,
Tom Ogilvy


"Trefor" wrote in message
...
gimme_this_gimme_that,

I get an Error, "Invalid use of Me Keyword"

--
Trefor


" wrote:

This might be useful to you.

A means to check if a reference exists from a template and a method to
remove the reference later so others can read the Workbook you created
from the Template and the AddIn.


Put these Subs into the Template:

Private Sub Workbook_Open()
If ReferenceExists("MyAddInName") Then
' call StartUpSub in the NameOfAddInModule
NameOfAddInModule.StartUpSub Me
End If
End Sub

Private Function ReferenceExists(reference As String) As Boolean
Dim result As Boolean
result = False

On Error Resume Next
result = Not Me.VBProject.References(reference) Is Nothing

ReferenceExists = result
End Function


Then in the Template call this:


Public Sub RemoveReferences(book As Workbook)
book.VBProject.References.Remove
book.VBProject.References("MyAddInName")
End Sub

Tom,

My original problem was not knowing if an addin was currently loaded.
Perhaps it was because it was the way I asked the question, but most of the
answers I was getting were related to have a ticked reference in the Addin
manager. In fact all this tells me is that it had been loaded at some point
in time, NOT that it was currently actually loaded.

For some reason in the past someone sugested that I use this code:

Dim WBName As String
On Error Resume Next ' turn off error checking
WBName = Workbooks(DCMaster2).Name
LastError = Err
On Error GoTo 0 ' restore error checking
If LastError < 0 Then

Where the error condition would give me the answer I was looking for, but
this did not work correctly.

With all my digging around, the answer (that seems to work) was right under
my nose, and so I have re-written the function:

Function AddinPresent(DCMaster2) As Boolean
Dim AddInInstalled As Boolean
With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2)
' Is Addin Installed?
AddInInstalled = .Installed
End With
If AddInInstalled Then
' Addin is installed, exit function
AddinPresent = True
Exit Function
Else
' Addin is NOT install, attempt to load it
With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2)
.Installed = True
End With
' Now check that the Addin has loaded
With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2)
AddInInstalled = .Installed
End With
If AddInInstalled Then
' Addin is installed, exit function
AddinPresent = True
Exit Function
Else
' Addin has failed to install
AddinPresent = False
Exit Function
End If
End If
End Function

Many thanks for you help and eveyone elses, hopefully this is it.

--
Trefor


"Tom Ogilvy" wrote:

Looks like that code is checking for a reference to the addin - not sure
what it has to do with the question you asked.

--
Regards,
Tom Ogilvy


"Trefor" wrote in message
...
gimme_this_gimme_that,

I get an Error, "Invalid use of Me Keyword"

--
Trefor


" wrote:

This might be useful to you.

A means to check if a reference exists from a template and a method to
remove the reference later so others can read the Workbook you created
from the Template and the AddIn.


Put these Subs into the Template:

Private Sub Workbook_Open()
If ReferenceExists("MyAddInName") Then
' call StartUpSub in the NameOfAddInModule
NameOfAddInModule.StartUpSub Me
End If
End Sub

Private Function ReferenceExists(reference As String) As Boolean
Dim result As Boolean
result = False

On Error Resume Next
result = Not Me.VBProject.References(reference) Is Nothing

ReferenceExists = result
End Function


Then in the Template call this:


Public Sub RemoveReferences(book As Workbook)
book.VBProject.References.Remove
book.VBProject.References("MyAddInName")
End Sub