PDA

View Full Version : Solved: Methods to update an add-in



Ken Puls
09-14-2005, 03:39 PM
Hey guys,

I'm looking for ideas from the floor on how to update an add-in easily. Here's the situation...

I have an add-in which is pretty much all code modules, except that I've been using J-Walk's menu maker for the menu structure. So there is one worksheet in it. The rest of the project is all contained in separate template files, so when I need to create a specific anything, it looks up the appropriate file and leverages it. (I am making an attempt to split the user interface from the business logic.)

This add-in is stored on a terminal server, so every user has to copy it into their own place to use it, or the file gets locked up and no one else can use it.

Now, here's the issue...

I am constantly adding to the functions in this add-in. I add new code, update some, and add new modules. Naturally, as I bring more functionality online, I also add the menu item structure to the worksheet that controls the menus.

So the question... how do I easily update the add-ins that the users have installed when I realease a new version?

I'm trying to figure out how to build an updater, but I just see that it will involve another workbook to hold code, uninstall the addin, copy and reinstall.

Any thoughts here?

Bob Phillips
09-14-2005, 03:53 PM
Ken,

Just a thought off the top.

How about having a simple read-only text file on the network server that the add-in checks first time it runs. This file holds the version number, if it doesn't match with the add-in's value, give the user a message that they have to install a newer version.

BTW, what problems are you getting with the add-in? I have used server add-ins with no problems frequently.

Bob Phillips
09-14-2005, 04:07 PM
There is also a free extractor at http://www.disoriented.com/FreeExtractor/

Ken Puls
09-14-2005, 04:17 PM
Hi Bob,

As I remember, it locked the file if someone was in it... Now that you question me though, I can't be sure. I'll have to check on it...

Bob Phillips
09-14-2005, 04:20 PM
Hi Bob,

As I remember, it locked the file if someone was in it... Now that you question me though, I can't be sure. I'll have to check on it...

Ken,

As long as they install it, not load it as a workbook, it should be fine.

Ken Puls
09-14-2005, 04:24 PM
Hmmm... okay. Maybe I just assumed then...

I know what I did do, and that was copy it into the Addin directory for each user on the server, which was a real PITA. I know that right now I have 2 separate versions out there, and will have a third once I figure out how to deploy here.

So do they just uninstall and then reinstall through a Browsing to the updated file then? I think I'll go try that now...

Ken Puls
09-14-2005, 04:40 PM
Interesting...

I had placed the addin in the following folder for them, then browsed right to it:
M:\Document and Settings\username\Application Data\Microsoft\Addins

When I deleted that file then reinstalled it, it gave me the option of copying it to the Addin library. I said yes, and it dumped it in:
M:\Program Files\Microsoft Office\Office\Library

Maybe this is an office 97 thing... I don't know. Regardless, it seems like that is a shared folder that would update everyone on the terminal server. Now only concern is my actual workstations who are running XP...

I'm leary to make users go in an Browse to a new version though... curious behind the logic to code it. Woudl it be necessary to:
-Uninstall the addin
-Reinstall the addin

Or would there be more to it?

Bob Phillips
09-15-2005, 02:21 AM
Ken,

I understand the apprehension on the browsing thing, that assumes an ability that maybe not all users possess.

This is off the top of my head now, not checked it, but I think you need to
- uninstall the addin (makes it over-writable)
- copy the new version across
- install the addin

Don't worry about copying to the MS library, that is just MS's default, but it can be wherever you want.

Problem with this approach is it still requires an app to do it, and you MUST always re-install in the same directory, else the registry entry and the physical location do not match. Talking about the registry, how will you handle first time loading, as the registry needs to be updated, and there are so many locations depending upon the Excel version. I wrote a VB6 app to do all this once, but I never fully finished it. I should complete it, it would be very useful.

.

Insomniac
09-15-2005, 04:07 AM
it gave me the option of copying it to the Addin library. I said yes, and it dumped it in:
M:\Program Files\Microsoft Office\Office\Library

What I do is 'NOT' copy to local comp, but install from central public network folder. I create & test addin on my own comp & then save it to network and make it readonly. This way you can easily save over it with new version as no-one has the file locked for edit. Heres some simplified code of how to have the addin automatically check itself for any upgrades:
Option Explicit
Dim NewVersion As Boolean
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not NewVersion Then VersionCheck True 'Cancel the next Ontime if not new version
End Sub
Private Sub Workbook_Open()
With Me
.Worksheets(1).[A1].Value = FileDateTime(Me.FullName)
If Not .ReadOnly Then 'clear our cell variables if not readonly ready for saving
.Worksheets(1).[A1].Value = Empty
.Worksheets(1).[A2].Value = Empty
.Worksheets(1).[A3].Value = False 'important
Else
.Worksheets(1).[A3].Value = True 'prevents recursive calls to VersionCheck
VersionCheck False
End If
End With

End Sub
Private Sub VersionCheck(ByVal Cancel As Boolean)
Dim CheckTime As Date, Proc As String

On Error Resume Next
Proc = "'ThisWorkbook.VersionCheck False'" 'our Ontime procedure in ThisWorkbook
'prefer to store time in a Cell [A2] v Public variable
'in case of errors causing public variable to go out of scope
'ie. some Application Level Crash may cause this.

If Cancel Then
CheckTime = Me.Worksheets(1).[A2].Value
Application.OnTime CheckTime, Proc, , False
Else

With Me
If .Worksheets(1).[A3].Value Then
If .Worksheets(1).[A1].Value <> FileDateTime(Me.FullName) Then
MsgBox "new version of " & Me.Name & " ready to update"
VersionCheck True
Application.OnTime Now, Proc
NewVersion = True: Me.Close False
End If
End If
End With
VersionCheck True 'Cancel any other schedules
CheckTime = Now + TimeValue("00:00:10") 'set for 10 seconds testing
Me.Worksheets(1).[A2].Value = CheckTime
MsgBox "10 second test"
Application.OnTime CheckTime, Proc

End If

End Sub

Note:
You can test this locally by creating an addin with this code & saving>open a new instance of xl & open the addin, it should give you 10 second msgbox.
Save the addin in 1st instance & 2nd instance should tell you new version is to be loaded......

If you just open the file from xl it will always ask to enable macros, but if you install it with 'Trust all installed addins' checked it will not.

(Of course the real application you would set a reasonable time for check (maybe 1 hr or more) & not make a msgbox)

[Edit] all the code was quickly writtten to be dumped into any 'ThisWorkbook Class module, it is more refined to ammend it to run in standard modules.

Bob Phillips
09-15-2005, 04:29 AM
If it is on the network, why would you need to test for upgrades?

Insomniac
09-15-2005, 04:35 AM
If it is on the network, why would you need to test for upgrades?
Because like I said, (what I do anyway), make the addin 'readonly' when saved on the network. If I happen to upgrade the code & save over the existing addin then any comp that has already loaded the 'old readonly' version will automatically upgrade itself with the OnTime code.

Justinlabenne
09-15-2005, 04:51 AM
Hmm...... Interesting topic, can't wait to see more feedback on it. To hopefully add some useful ideas: I generally tag all projects with a Custom Document property if I know that the users will be abel to copy or save add-in's or workbooks where ever the feel like. I generally use code similar to this Kb Entry (http://vbaexpress.com/kb/getarticle.php?kb_id=694) although the method can be quite slow and I rarely use it unless I have at least a general idea of where projects may be so it doesn't spend an hour searching all folders on a network. One way, and possibly not the best or fastest.....

Just as you were saying about building your own updater Ken, I honestly built my own installer/updater from an Excel file that I use to log the path of where the installed add-in's are located, and then any subsequent updated add-ins just get copied over in this same location. Here is some code that may be a bit relevant (maybe?) to copying over an existing and installed add-in:


' Obtain current addin location
Dim szCopyPath As String
szCopyPath = gsValidPath & gsAddin

' Obtain path to install
Dim szDestPath As String
szDestPath = Me.lblDestPath.Caption


' Store the location on a sheet to be used later for the Uninstaller
With shMain
.Range("ptrAddinName").Value = gsAddinName
.Range("ptrAddinPath").Value = szDestPath
End With


' Check for a currently installed version of the add-in we are installing
' by looping through the add-in's and looking for matching names
Dim Addy As AddIn
For Each Addy In Application.AddIns
If Addy.Name = gsAddin Then

gsMsg = "There is a version of " & gsAddinName & " currently installed" & vbNewLine
gsMsg = gsMsg & vbNewLine & "Overwite currently detected version?"
gsStyle = 36

' Prompt for overwrite if add-in is detected
If MsgBox(gsMsg, gsStyle, gsAddinName) = 7 Then

' if user won't overwrite then:
' Lose the form
Unload Me

' Start the workbook's shutdown proceedings
ShutDownApp
Exit Sub
End If

End If
Next Addy

' If a current version is being used, and we are overwriting it, we have to
' uninstall it before we can overwrite
On Error Resume Next
If AddIns(gsAddinName).Installed = True Then AddIns(gsAddinName).Installed = False
On Error GoTo 0

On Error GoTo ErrHandle
' Install the add-in using FileCopy method, to whatever directory is chosen
FileCopy szCopyPath, szDestPath


' Install it after delivery
With AddIns.Add(szDestPath)
.Installed = True
End With


This comes out of a form I use, and it has plenty of variables that you don't see defined, but the base of what it does is there. Basically, try to install in a user selected (you would probably hard-code for your needs) path. If the add-in already exists here, ask to overwrite it, it they say yes, Uninstall the add-in, then do a file copy to the location, add it to the list, and install it.

This has flaws, like if the add-in we uninstalled is not actually in the same path we just installed the update in. We would probably want to delete that old one no matter which directory it was im, so using the kill statement we could grab the old versions full name and remove it after it is uninstalled:
Dim addy As AddIn
Kill addy.FullName

Just some extra ideas for you, hope they are a bit relevant to you, if not just disregard my rambling's. I am interested to see what other ideas Bob offers up, the first suggestion was something I have not yet got around to toying with, but seems like a very simple and effective method.

Later.

XL-Dennis
09-15-2005, 06:56 AM
Ken,

Follow this link to MrExcel and it will give You an alternative approach that, AFAIK, well:

Install & Uninstall add-ins with a VB 6.0-based approach (http://www.mrexcel.com/board2/viewtopic.php?t=150635&highlight=)


Comments most welcome :)

Kind regards,
Dennis

Ken Puls
09-21-2005, 01:38 PM
Back to this...

Dennis, I've taken a couple of quick reads through what you're got there, but I'm not sure if it's 100% what I'm after. Admitedly, though, I may not be understanding, and reserve the right to come full circle on that comment! ;)

I fooled around some evenings last week writing some code to uninstall, delete, copy and reinstall the addin, and it worked great until I tried to run it from a class module using a Workbook_AddinInstall event. That one locked up the addin file so that I couldn't delete it.

I figured I'd try to go back to the simple suggestion... just in case I missed something though.

Today I changed one of my 2003 users to run the addin from the network. I also said NO to installing a local copy in the addins folder. This worked just fine, but gave me an issue that I don't want. I can't replace the addin on the fly. If someone has Excel open with this addin installed, it locks the file. I completely expected this, too, but made sure to confirm it.

I do work on updates to this file locally, (adding new tools, bettering code, etc...) and want to deploy them when I'm finished. The problem is that it's installed by about 8 users (in different buildings), and we're a 24/7/365 operation. While scheduling is possible, it's a PITA that I'd like to avoid if possible.

I'll dig out the code that I've done as soon as I can get to it. Am going to re-look at all the posts in this thread again as well. :)

dvenn
01-13-2006, 04:28 PM
I know it's been a couple of months since this thread has seen any action, but it's exactly what I am looking for....

Thought maybe it deserved a bump to see it it could get solved..

mdmackillop
01-13-2006, 04:47 PM
I'm looking at some code to copy all code from a workbook to a set of similar ones here http://vbaexpress.com/forum/showthread.php?t=6685 and possibly my add-in as well. I can load and unload the add-in with AddIns("timesheet").Installed = True/False, which should let me into it without Excel having to be closed on all PCs.

Ken Puls
01-13-2006, 05:25 PM
The timing of this is AWESOME! I JUST (like 20 minutes ago) finished deploying my final solution to this problem. My day is almost over, but I'll hop on tonight and share what I did to solve the issue. Hopefully you all can wait. :)

johnske
01-13-2006, 07:40 PM
...Now, here's the issue...

I am constantly adding to the functions in this add-in. I add new code, update some, and add new modules. Naturally, as I bring more functionality online, I also add the menu item structure to the worksheet that controls the menus.

So the question... how do I easily update the add-ins that the users have installed when I realease a new version?

I'm trying to figure out how to build an updater, but I just see that it will involve another workbook to hold code, uninstall the addin, copy and reinstall.

Any thoughts here?This one wasn't specifically designed with an add-in in mind, but you can update code modules and you could easily modify to look for new ones as well - perhaps you could modify? http://www.vbaexpress.com/kb/getarticle.php?kb_id=174&PHPSESSID=c5ed4e1ca7e25da6d8584507b2f63b4c

Justinlabenne
01-13-2006, 09:59 PM
Similiar to xld's solution.... I use this to add-ins. The code is contained within the add-in, and on opening, it checks a text file that contain a version #. If they match do nothing, if not: inform > uninstall > replace > reinstall.

Option Explicit

Public Const glVersion As Long = 1


Public Sub JLXLUpdateCheck(ByVal szCheckFullName As String, _
ByVal lVersionNumber As Long, _
ByRef szFileNameAndExt As String, _
ByRef szNewVersionFullName As String)
Dim gbOk As Boolean
gbOk = Dir(szCheckFullName) <> ""
If Not gbOk Then Exit Sub

On Error GoTo ErrorHandle
Dim szLine As String
Dim nFile As Integer
nFile = FreeFile()
Open szCheckFullName For Input As nFile

While Not EOF(nFile)
Line Input #nFile, szLine
Wend
Close nFile
If szLine <> lVersionNumber Then
MsgBox "An update is available for this application", 64
JLXLReplaceVersion szFileNameAndExt, szNewVersionFullName
End If
Exit Sub

ErrorHandle:
Close
End Sub

Private Sub JLXLReplaceVersion(ByVal szNewFileName As String, _
ByVal szNewVersionFullName As String)
Dim gbOk As Boolean
gbOk = Dir(szNewVersionFullName) <> ""
If Not gbOk Then Exit Sub

Dim oTempBook As Object
Set oTempBook = Workbooks.Add
With ThisWorkbook
Open .Path & "\xx.bas" For Output As #1
Print #1, "Sub Temp"
Print #1, "Workbooks(" & """" & .Name & """" & ").Close False"
Print #1, "Dim a As AddIn"
Print #1, "For Each a In Application.AddIns"
Print #1, "If a.Name = (""" & szNewFileName & """) And a.Installed = True Then"
Print #1, "a.Installed = False"
Print #1, "End If"
Print #1, "Next a"
Print #1, "Kill " & """" & .Path & "\" & .Name & """"
Print #1, "Kill " & """" & .Path & "\xx.bas" & """"
Print #1, "FileCopy """ & szNewVersionFullName & """,""" & ThisWorkbook.Path & "\" & szNewFileName; """"
'Print #1, "Kill """ & szNewVersionFullName & """"
Print #1, "For Each a In Application.AddIns"
Print #1, "If a.Name = (""" & szNewFileName & """) And a.Installed = false Then"
Print #1, "a.Installed = true"
Print #1, "End If"
Print #1, "Next a"
Print #1, "ThisWorkbook.Close False"
Print #1, "End Sub"
Close #1
oTempBook.VBProject.VBComponents.Import Filename:=.Path & "\xx.bas"
End With
Application.OnTime Now(), oTempBook.Name & "!Temp"
End Sub


Then used like this in the add-in's open or install event.

Private Sub Workbook_Open()
JLXLUpdateCheck "C:\YourPath\Updates\Version.txt", glVersion, _
"ProjectName.xla", "C:\YourPath\Updates\ProjectName.xla"
End Sub


Every time I release an updated version of the add-in, I change the global glVersion Constant, and the number within the textfile to match.

Ken Puls
01-13-2006, 10:36 PM
Okay, guys, so here's what I ended up doing to make this as easy as possible to build, deploy and update in my situation...

First off, I uninstalled all the current installations where I had said "yes" to copying the file locally upon install. To do this, I had to open Excel, uncheck the addin, close Excel and delete the local copy, reopen excel an attempt to enable the addin again. When I clicked the chekbox, it told me that the file was no longer there and I agreed to removing it from the list. An Excel restart and I knew it was clean and ready to go.

So now, what I did is to copy the latest file to the network location. In my case, that is in the "F:\Templates\" folder. I installed it for each of my 8 users, saying No to the "copy this to the local folder" prompt. (I forget the exact verbiage there.) So at this point, I have 8 users who are all working from the same addin.

Being a doubter, I had four of them open it at the same time, and all was good. :) It looks like, although I have not tested this completely, that the first user in gets read/write access, and all subsequent users get read only access. Again, not tested, but from what I've heard from my friends, that should be correct.

The last place I installed the addin was on my machine. This time, however, I did install it locally. The reason for this is that I am the only developer in the company, and I wanted a stand alone version in which to develop and test updates.

Now I needed a way to push out any updates easily. I know that as soon as one of my 8 users starts Excel, the file will be locked for overwriting, and I also know that one of my 8 users has Excel open pretty much all day. I?m hypothesizing that I only need to kick the first user out of the file to overwrite it, but I haven?t tested that to date. Regardless, I?ve made the conscious choice that even if I have to kick each user out, I?ll phone them. It beats the alternatives, but I?ll get in to that later.

To make the deployment easy, I grabbed a little code from Ivan F Moala?s site, which checks if a network file is open, and who has it open. I tied it in to a procedure to save the addin from my local folder to the network folder as well. This allows me to cleanly automate the process of updating the file. So basically, if the file is open, I know who to call. If not, it will save over the network version using the SaveCopyAs method. I also use a custom document property to save the version number, which can be called from a menu, so I can ensure that the version transfer has completed correctly.

The code to deploy the addin is following:

Const sTemplateRootPath = "F:\Templates\"

Sub DeployAddin()
'Check if current template file can be accessed

If IsNetworkFileOpen(sTemplateRootPath & ThisWorkbook.Name) Then
MsgBox "The network file is in use by " & LastUser(sTemplateRootPath & ThisWorkbook.Name) _
& vbnewline & "Please have the user exit the file, then " _
& vbnewline & "re-run this routine.", vbCritical + vbOKOnly, "File in Use"
Exit Sub
End If

'Save a copy of the addin to the root template folder
ThisWorkbook.SaveCopyAs sTemplateRootPath & ThisWorkbook.Name

End Sub

The following two functions come from Ivan F Moala's site (http://www.xcelfiles.com), and are used in checking if the file is open. All I did to them was rename the first function for my purposes.

Function IsNetworkFileOpen(strFullPathFileName As String) As Boolean
' Adapted from IsFileOpen routine (VBA Method) found at Ivan F Moala's site:
' http://www.xcelfiles.com/IsFileOpen.html
'
'// VBA version to check if File is Open
'// We can use this for ANY FILE not just Excel!
'// Ivan F Moala
'// http://www.xcelfiles.com

Dim hdlFile As Long

'// Error is generated if you try
'// opening a File for ReadWrite lock >> MUST BE OPEN!
On Error GoTo FileIsOpen:
hdlFile = FreeFile

Open strFullPathFileName For Random Access Read Write Lock Read Write As hdlFile
IsNetworkFileOpen = False
Close hdlFile
Exit Function

FileIsOpen:
'// Someone has it open!
IsNetworkFileOpen = True
Close hdlFile

End Function

Function LastUser(strPath As String) As String
'Code as posted on Ivan F Moala's site 01/13/2005
'' http://www.xcelfiles.com/IsFileOpen.html
'
'// Code by Helen from http://www.visualbasicforum.com/index.php?s=
'// This routine gets the Username of the File In Use
'// Credit goes to Helen for code & Mark for the idea
'// Insomniac for xl97 inStrRev
'// Amendment 25th June 2004 by IFM
'// : Name changes will show old setting
'// : you need to get the Len of the Name stored just before
'// : the double Padded Nullstrings

Dim strXl As String
Dim strFlag1 As String, strflag2 As String
Dim i As Integer, j As Integer
Dim hdlFile As Long
Dim lNameLen As Byte

strFlag1 = Chr(0) & Chr(0)
strflag2 = Chr(32) & Chr(32)
hdlFile = FreeFile

Open strPath For Binary As #hdlFile
strXl = Space(LOF(hdlFile))
Get 1, , strXl
Close #hdlFile

j = InStr(1, strXl, strflag2)

#If Not VBA6 Then
'// Xl97
For i = j - 1 To 1 Step -1
If Mid(strXl, i, 1) = Chr(0) Then Exit For
Next
i = i + 1
#Else
'// Xl2000+
i = InStrRev(strXl, strFlag1, j) + Len(strFlag1)
#End If

'// IFM
lNameLen = Asc(Mid(strXl, i - 3, 1))
LastUser = Mid(strXl, i, lNameLen)

End Function

So far it seems to have worked quite well. I do still want to test my theory about having 4 users in, kicking out the first, and overwriting, but I believe that it shouldn?t be an issue there.

What?s really cool to me is that this all works with Excel 97 as well. :)

Now, as for why I went this route?

First, I started running down other paths due to a misunderstanding of how the network installation worked. That?s an honest truth. I assumed that the file would lock if multiple users installed it, and as Bob pointed out, that?s not the case.

Second, while designing code to check for updates is easy, I didn?t like the error potential of exporting/importing code modules into a local addin. In order to do that, I?d need to export all the code modules to a network folder, which is much more work than just copying over the addin with a new version.

Another thought I had was to was having a version check in the (local) addin (#1) that ?phoned home? to central, and if a new version was present, kicked off a routine in addin #2 via OnTime (or something) to allow the addin #1 to close and the addin #2 to copy the updated file over addin #1. I?m curious to know how my users would have felt watching the OnTime clock tick away and hold them up during all this as well.

Both of the above methods would have taken time to develop and test, and would need a bunch of error handling to make it bullet proof. It just honestly seemed like too much effort with too many pitfalls.

My final thought was asking my users to uninstall and reinstall. The thing I don?t like about that is? well? I don?t like asking my users to do that stuff. They really shouldn?t need to, and I can?t bank on computer literacy in all areas it is being used.

That?s it for now. I?ll post back when I have more info on the file lock during update part.

Hopefully this will help someone. :)

EDIT: PS, Daniel... thanks for bringing this up again. I'd completely forgotten that I'd posted it. I shelved the update for a while, but had been working on it this last week.

Ken Puls
01-17-2006, 03:51 PM
So far it seems to have worked quite well. I do still want to test my theory about having 4 users in, kicking out the first, and overwriting, but I believe that it shouldn?t be an issue there.

Just posting back to confirm a successful test of this theory.

I had User 1 open excel (and therefore launch the network addin), then had User 2 also go in. I then had User 1 close Excel, but left User 2 in the file. Ran the macro to update and it worked great. No prompting, no errors.

I kicked everyone out, had User 1 reopen Excel again. Tried to update, and got told that she was in it.

So it looks like it's probably as good a method to update as any. You get notification of who you need to boot out, and can update when they do. The next users get the update as soon as they restart Excel. (Actually as soon as they reload the addin, but let's face it... that's usually the same.)

:hi:

mdmackillop
01-17-2006, 04:05 PM
Hi Ken
I've not progressed the update yet, but by running
AddIns("timesheet").Installed = True as a workbook.open event and
AddIns("timesheet").Installed = False as a workbook.close event, I can access my add-in without having to boot everyone out of Excel.
My add-in is of use only in this one workbook of course, but I'm guessing that this also frees up resources that a loaded add-in will use, even when it's not required. Will this make any difference to general Excel performance?
Regards
Malcolm

Ken Puls
01-17-2006, 04:21 PM
Hi Malcolm,

I've actually got two versions of the addin running. One, which is shared by everyone but me, sits on the network. Mine is on my local PC. With this setup, I can test, debug, etc... in my (local) development copy before I push it out to my users.

As far as kicking them out, the only reason I do it is to get that one user out of the file so that I can overwrite it. I only truly need to have the first user uninstall the addin to make it available, but most users understand "can you just close Excel for a minute" much better. :)

As far as the performance, goes, I'm not sure what you're asking... If the addin isn't installed, I'd guess that you'd have better performance from Excel, as less needs to reside in memory, theoretically. Your code only sets the property to true when it loads and false when it unloads though. I would have thought that was the default action, so it seems kind of redundant to me.

Unless I've missed something... quite possible... :dunno

mdmackillop
01-17-2006, 04:31 PM
Hi Ken,
As far as I can see, a loaded add-in is there for all excel files. In my case, if no-one has Timesheet.xls open, with its associated XLA, then I can amend the add-in as required, and the users can carry on using Excel without interruption. I'll look at your solution with interest, as not all my XLA files are specific to a single workbook.
Regards
Malcolm

MrRhodes2004
02-10-2006, 12:24 PM
Hey Group,
That is a lot of discussion and many different solutions. I have the same problem as Ken. I update my add-in all the time and do not want to have to disturb the user about updates.

What I have done: I save the xla to a network location where everyone has access and make the file 'read only'. Any new versions of the file are just copied over the existing. The user is automatically updated when they restart their excel ONLY if they did NOT COPY the file to their local drive!


QUESTION 1:
When a user originally loads the add-in, excel asks the user if they would like to copy that file to the local. Is there a way to prevent this question being asked of the user for your particular add-in?

QUESTION 2:
OR, if the user mistakenly copies it to the local, is there a way that the add-in can delete the local copy and redirect add-in to the network location?

XLGibbs
02-10-2006, 06:39 PM
I think for question 2, something like this may work...without asking the user..

Dim strAddInPath as string
StrAddInPath = "G:\NetworkFolder\AddInFolder"

If Addins("AddInName").Installed = True then
If AddIns("AddInName").Path <> strAddInPath Then
On Error Resume Next '
AddIns("AddInName").Installed = False
End if
Else
AddIns.Add Filename:=strAddInPath & "\AddInName.xla"

End If


Question 1 may be avoided by display alerts = False...not sure with add ins though...will test some stuff out...

Ken Puls
02-10-2006, 11:26 PM
Hey Guys,

Pete, I'd be curious to know if your code does work to switch the path back. In my experience, the only (reliable) way to fix it has been to close Excel, nuke the addin file, reopen Excel, acknowledge that the addin has been removed, restart Excel, then install the new version. The last restart was critical, as I remember, or it picks up the old one.

I remember thinking that the path to the addin was saved in the registry, and didn't update till Excel was closed... but could be wrong... :dunno

Good questions, MrRhodes! :yes

Bob Phillips
02-11-2006, 03:05 AM
I remember thinking that the path to the addin was saved in the registry, and didn't update till Excel was closed... but could be wrong... :dunno

It is, and it gets complex becuase of the versions and locations, but it should be possible to do it on the fly. I will have a play this weekend with it.

Ken Puls
02-11-2006, 06:37 PM
Looking forward to your results, Bob! :)