Problems using Add-in

I am using an .XLA in a common directory so multiple people on multiple
machines can all share the same VBA.

Assuming at this point Tools > Add-Ins does not have my Add-in listed. To
make the adding of this automated for my users I have added the following
with help from this discussion group:

DCMaster2 = "'Customer Data Collect Master v6.38.xla'"
MainPath = "M:\Work Flow"
..
..
..
Function AddinPresent(MainPath, DCMaster2) As Boolean
Dim WBName As String, lastError
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
With AddIns.Add(Filename:=MainPath & "\" & DCMaster2) <---Point 1
.Installed = True
End With
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
AddinPresent = True
End If
End Function

At Point 1 above I get a message: "Copy 'Customer Data Collect Master
v6.38.xla' to the Addins folder for <user name>"

If I answer NO, it continues to work fine running from the macros in the
common location. BUT it asks this question everytime you open the spreadsheet
that contains the above code.

If I answer YES, it copies the .XLA to "C:\Documents and Settings\<user
name>\Application Data\Microsoft\AddIns". The problem now is if I want to
change/update the .XLA in the common location the end user will not get the
updated file even though the above code looks for a particular, instead it
will continue to use the "C:\Documents and Settings\<user name>\Application
Data\Microsoft\AddIns". I can manually go to Tools > Add-Ins and deselect the
..XLA and then re-run the macro and it copies accross the updated .XLA, but
this if harldy automated!

Any ideas?

--
Trefor

try

Application.DisplayAlerts = False
With AddIns.Add(Filename:=MainPath & "\" & DCMaster2) <---Point 1
.Installed = True
End With
Application.DisplayAlerts = True

I could reproduce your problem on my machine but DisplayAlerts may be what
you need.

HTH

Barry


"Trefor" wrote:

> I am using an .XLA in a common directory so multiple people on multiple
> machines can all share the same VBA.
>
> Assuming at this point Tools > Add-Ins does not have my Add-in listed. To
> make the adding of this automated for my users I have added the following
> with help from this discussion group:
>
> DCMaster2 = "'Customer Data Collect Master v6.38.xla'"
> MainPath = "M:\Work Flow"
> .
> .
> .
> Function AddinPresent(MainPath, DCMaster2) As Boolean
> Dim WBName As String, lastError
> 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
> With AddIns.Add(Filename:=MainPath & "\" & DCMaster2) <---Point 1
> .Installed = True
> End With
> 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
> AddinPresent = True
> End If
> End Function
>
> At Point 1 above I get a message: "Copy 'Customer Data Collect Master
> v6.38.xla' to the Addins folder for <user name>"
>
> If I answer NO, it continues to work fine running from the macros in the
> common location. BUT it asks this question everytime you open the spreadsheet
> that contains the above code.
>
> If I answer YES, it copies the .XLA to "C:\Documents and Settings\<user
> name>\Application Data\Microsoft\AddIns". The problem now is if I want to
> change/update the .XLA in the common location the end user will not get the
> updated file even though the above code looks for a particular, instead it
> will continue to use the "C:\Documents and Settings\<user name>\Application
> Data\Microsoft\AddIns". I can manually go to Tools > Add-Ins and deselect the
> .XLA and then re-run the macro and it copies accross the updated .XLA, but
> this if harldy automated!
>
> Any ideas?
>
> --
> Trefor

Barry,

Thankyou for the reply, but this does not fix my problem.

MAIN.XLS - Has the code listed below, it checks to see if MACRO.XLA exists,
if it does not, it copies MACRO.XLA to the Users Addin directory (not sure
how excel determines where to copy). By adding the two lines you suggest,
sure its stops the message and the file gets copied. BUT here is my problem:

If I change / update MACRO.XLA in the common location, when MAIN.XLS starts
(for the second or more times) it simply checks that MACRO.XLA exists as an
add-in (which it does) and continues to use the OLD add-in in the users
add-in directory.

Is there a way to force a copy of the XLA from the common location to the
users add-in directory ONLY if there is a newer XLA? OR is there a way to
stop Excel copying the file from the common location in the first place,
thereby always using the common XLA file?


--
Trefor


"Barry" wrote:

> try
>
> Application.DisplayAlerts = False
> With AddIns.Add(Filename:=MainPath & "\" & DCMaster2) <---Point 1
> .Installed = True
> End With
> Application.DisplayAlerts = True
>
> I could reproduce your problem on my machine but DisplayAlerts may be what
> you need.
>
> HTH
>
> Barry
>
>
> "Trefor" wrote:
>
> > I am using an .XLA in a common directory so multiple people on multiple
> > machines can all share the same VBA.
> >
> > Assuming at this point Tools > Add-Ins does not have my Add-in listed. To
> > make the adding of this automated for my users I have added the following
> > with help from this discussion group:
> >
> > DCMaster2 = "'Customer Data Collect Master v6.38.xla'"
> > MainPath = "M:\Work Flow"
> > .
> > .
> > .
> > Function AddinPresent(MainPath, DCMaster2) As Boolean
> > Dim WBName As String, lastError
> > 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
> > With AddIns.Add(Filename:=MainPath & "\" & DCMaster2) <---Point 1
> > .Installed = True
> > End With
> > 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
> > AddinPresent = True
> > End If
> > End Function
> >
> > At Point 1 above I get a message: "Copy 'Customer Data Collect Master
> > v6.38.xla' to the Addins folder for <user name>"
> >
> > If I answer NO, it continues to work fine running from the macros in the
> > common location. BUT it asks this question everytime you open the spreadsheet
> > that contains the above code.
> >
> > If I answer YES, it copies the .XLA to "C:\Documents and Settings\<user
> > name>\Application Data\Microsoft\AddIns". The problem now is if I want to
> > change/update the .XLA in the common location the end user will not get the
> > updated file even though the above code looks for a particular, instead it
> > will continue to use the "C:\Documents and Settings\<user name>\Application
> > Data\Microsoft\AddIns". I can manually go to Tools > Add-Ins and deselect the
> > .XLA and then re-run the macro and it copies accross the updated .XLA, but
> > this if harldy automated!
> >
> > Any ideas?
> >
> > --
> > Trefor