Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Update An add-in
Deze pagina in het Nederlands

Updating An add-in Through the Internet

Introduction

When you're offering an add-in through your website you usually have no connection to the people who are using your programming work. It is not unusual that you have discovered (or been pointed at) bugs in your code or that you've done some updating to the utility you'd like your users to benefit from. Sometimes it might be useful if your users could be notified automatically of such updates (many software titles have such a mechanism built-in). This little article shows a way to include that functionality with your add-in.

Assumptions for this article

I've assumed the following:

Excel 2007 peculiarities

The web query is inserted using VBA, because Excel 2007 disables all connections in an addin by default, until the user clicks the button on the message bar (top of Excel window, below the ribbon) and enables them:


Messagebar showing that something has been disabled in your addin.


What the user sees in the security dialog after clicking the Options button on the message bar.

Also, it appears Excel 2007 does not allow inserting a webquery in one of the add-in's worksheets. To work around this problem, the add-in's "IsAddin" property is temporarily set to False, then the web query is inserted and the property is set back to True.

Updating mechanism

The updating process works as follows:

Note: I've tried whether marking the add-in file as read-only would enable the code to delete the file itself, but this appears not to work when the file is on a local drive. I've heard reports that when the add-in is on a network folder, this does work. I don't know if it works in all cases though and would be pleased to get your feedback on that (use form at bottom of page or send me an email).

Update modes

The code handles two modes, automatic updates and manual update checks.

In the automatic case, the webquery is added to the addin, but is refreshed asynchronously. A worksheet change event is scheduled in the class module, which will fire as soon as the webquery has finished updating (or has timed out). This is done so the check for an update can run in the background whilst Excel finishes its startup process. The user won't have to wait (well, not noticably anyway) for your update to do its work.

In the manual case, the web query is updated synchronously (nothing will work in Excel until it has retrieved its result). I did this because in this case you will confuse your user of he does not get "immediate" feedback on the result.

Code

The code that does the actual updating is wrapped in a class module called "clsUpdate", see the entire code below.

Option Explicit

'-------------------------------------------------------------------------
' Module : clsUpdate
' Company   : JKP Application Development Services (c)
' Author : Jan Karel Pieterse
' Created   : 19-2-2007
' Purpose   : Class to check for program updates
'-------------------------------------------------------------------------
Option Explicit

Public WithEvents Sht As Worksheet

#If VBA7 Then
Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
        Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
                                    ByVal szURL As String, ByVal szFileName As String, _
                                    ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
#Else
Private Declare Function URLDownloadToFile Lib "urlmon" _
        Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
                                    ByVal szURL As String, ByVal szFileName As String, _
                                    ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
#End If

Private mdtLastUpdate As Date

Private msAppName As String
Private msBuild As String
Private msCheckURL As String
Private msCurrentAddinName As String
Private msDownloadName As String
Private msTempAddInName As String
Private mbManual As Boolean

Private Sub Class_Terminate()
    Set Sht = Nothing
End Sub

Private Sub DownloadFile(strWebFilename As String, strSaveFileName As String)
' Download the file.
    URLDownloadToFile 0, strWebFilename, strSaveFileName, 0, 0
End Sub

Public Function IsThereAnUpdate(Optional bShowMsg As Boolean = False) As Boolean
    Dim sNewBuild As String
    On Error Resume Next
    SaveSetting AppName, "Updates", "LastUpdate", CStr(Int(Now))
    If Err.Number <> 0 And bShowMsg Then
        MsgBox "Error retrieving update information, please try again later.", vbInformation + vbOKOnly
    End If
End Function

Public Sub DoUpdate()
    Dim sNewBuild As String
    sNewBuild = ThisWorkbook.Names("Available_build").RefersToRange.Value
    If Len(sNewBuild) = 0 Or Len(sNewBuild) > 4 Then
        MsgBox "Unable to fetch version information, please try again later.", vbOKOnly + vbInformation
        Exit Sub
    End If
    If CLng(sNewBuild) > CLng(msBuild) Then
        If MsgBox("We have an update, do you wish to download?", vbQuestion + vbYesNo) = vbYes Then
            DownloadName = "http://www.jkp-ads.com/downloadscript.asp?filename=" & ThisWorkbook.Name
            If GetUpdate Then
                Application.Cursor = xlDefault
                MsgBox "Successfully updated the addin, please restart Excel to start using the new version!", vbOKOnly + vbInformation
            Else
                Application.Cursor = xlDefault
                MsgBox "Updating has failed.", vbInformation + vbOKOnly
            End If
        Else
            Application.Cursor = xlDefault
        End If
    ElseIf Manual Then
        Application.Cursor = xlDefault
        MsgBox "Your program is up to date.", vbInformation + vbOKOnly
    End If
TidyUp:
    On Error GoTo 0
    Exit Sub
End Sub

Private Sub Sht_Change(ByVal Target As Range)
    Application.Cursor = xlDefault
    If Len(Target.Value) <= 4 Then
        DoUpdate
        Application.Cursor = xlDefault
    ElseIf Manual Then
        'Query failed to refresh and was called manually
        Application.Cursor = xlDefault
        MsgBox "Unable to retrieve version information, please try again later", vbInformation + vbOKOnly
    End If
    Set Sht = Nothing
TidyUp:
    On Error GoTo 0
    Exit Sub
End Sub

Public Sub PlaceBuildQT(ByVal bManual As Boolean)
    Dim oNm As Name
    On Error GoTo LocErr
    Application.ScreenUpdating = False
    For Each oNm In ThisWorkbook.Worksheets("Sheet1").Names
        oNm.Delete
    Next
    If CInt(Left(Application.Version, 2)) > 11 Then
        ' Trick!! Somehow Excel 2007 cannot insert a web query into an add-in!!
        ThisWorkbook.IsAddin = False
    End If
    With ThisWorkbook.Worksheets("Sheet1").QueryTables.Add(Connection:= _
                                                           "URL;" & CheckURL, Destination:=ThisWorkbook.Names( _
                                                                                           "Available_Build").RefersToRange)
        .Name = "autosafebuild"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = Not bManual
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        '  .WebDisableRedirections = False
        On Error Resume Next
        .Refresh BackgroundQuery:=Not (bManual)
        On Error GoTo 0
        If Not bManual Then
            Set Sht = ThisWorkbook.Worksheets("Sheet1")
        Else
            DoUpdate
        End If
    End With
TidyUp:
    If CInt(Left(Application.Version, 2)) > 11 Then
        ThisWorkbook.IsAddin = True
        ' Trick!! Otherwise, Excel 2007 will ask to save your add-in when it closes.
        ThisWorkbook.Saved = True
    End If
    Application.ScreenUpdating = True
    On Error GoTo 0
    Exit Sub
LocErr:
    If CInt(Left(Application.Version, 2)) > 11 Then
        ThisWorkbook.IsAddin = True
        ThisWorkbook.Saved = True
    End If
    Application.ScreenUpdating = True
    Application.Cursor = xlDefault
    If Err.Description Like "*QueryTables*" Then
        MsgBox "Error retrieving version information, please try again later.", vbInformation + vbOKOnly
        Resume TidyUp
    End If
End Sub

Public Property Get Build() As String
    Build = msBuild
End Property

Public Property Let Build(ByVal sBuild As String)
    msBuild = sBuild
End Property

Public Sub RemoveOldCopy()
    CurrentAddinName = ThisWorkbook.FullName
    TempAddInName = CurrentAddinName & "(OldVersion)"
    On Error Resume Next
    Kill TempAddInName
End Sub

Public Function GetUpdate() As Boolean
    On Error Resume Next
    'If workbook has been saved readonly, we can safely delete the file!
    If ThisWorkbook.ReadOnly Then
        Err.Clear
        Kill CurrentAddinName
    End If
    LastUpdate = Now
    ThisWorkbook.SaveAs TempAddInName
    DoEvents
    Kill CurrentAddinName
    On Error GoTo 0
    DownloadFile DownloadName, CurrentAddinName
    If Err = 0 Then GetUpdate = True
End Function

Private Property Get CurrentAddinName() As String
    CurrentAddinName = msCurrentAddinName
End Property

Private Property Let CurrentAddinName(ByVal sCurrentAddinName As String)
    msCurrentAddinName = sCurrentAddinName
End Property

Private Property Get TempAddInName() As String
    TempAddInName = msTempAddInName
End Property

Private Property Let TempAddInName(ByVal sTempAddInName As String)
    msTempAddInName = sTempAddInName
End Property

Public Property Get DownloadName() As String
    DownloadName = msDownloadName
End Property

Public Property Let DownloadName(ByVal sDownloadName As String)
    msDownloadName = sDownloadName
End Property

Public Property Get CheckURL() As String
    CheckURL = msCheckURL
End Property

Public Property Let CheckURL(ByVal sCheckURL As String)
    msCheckURL = sCheckURL
End Property

Public Property Get LastUpdate() As Date
    Dim dtNow As Date
    dtNow = Int(Now)
    mdtLastUpdate = CDate(GetSetting(AppName, "Updates", "LastUpdate", "0"))
    If mdtLastUpdate = 0 Then
        'Never checked for an update, save today!
        SaveSetting AppName, "Updates", "LastUpdate", CStr(Int(dtNow))
    End If
    LastUpdate = mdtLastUpdate
End Property

Public Property Let LastUpdate(ByVal dtLastUpdate As Date)
    mdtLastUpdate = dtLastUpdate
    SaveSetting AppName, "Updates", "LastUpdate", CStr(Int(mdtLastUpdate))
End Property

Public Property Get AppName() As String
    AppName = msAppName
End Property

Public Property Let AppName(ByVal sAppName As String)
    msAppName = sAppName
End Property

Public Property Get Manual() As Boolean
    Manual = mbManual
End Property

Public Property Let Manual(ByVal bManual As Boolean)
    mbManual = bManual
End Property

Alternatively, we could do this with the InternetExplorer control (and thus need a reference to the associated library) . This could be done like this:

Public Function IsThereAnUpdateUsingIE() As Boolean
    Dim oIE As InternetExplorer
    Set oIE = New InternetExplorer
    With oIE
        .Navigate2 CheckURL
        Do
        Loop Until .Busy = False
        If Len(.Document.body.innerhtml) > 0 Then
            If CLng(.Document.body.innerhtml) > CLng(Build) Then
                IsThereAnUpdate = True
            End If
        End If
        .Quit
    End With
    Set oIE = Nothing
End Function

In a normal module, we create an instance of this class, set its initial values and do the updating. The comments in the code describe what is being done:

Option Explicit

Dim mcUpdate As clsUpdate

Public Declare Function InternetGetConnectedState _
                         Lib "wininet.dll" (lpdwFlags As Long, _
                                            ByVal dwReserved As Long) As Boolean

Function IsConnected() As Boolean
    Dim Stat As Long
    IsConnected = (InternetGetConnectedState(Stat, 0&) <> 0)
End Function

Sub AutoUpdate()
    CheckAndUpdate False
End Sub

Sub ManualUpdate()
    On Error Resume Next
    Application.OnTime Now, "CheckAndUpdate"
End Sub

Public Sub CheckAndUpdate(Optional bManual As Boolean = True)
    Set mcUpdate = New clsUpdate
    If bManual Then
        Application.Cursor = xlWait
    End If
    With mcUpdate
        'Set intial values of class
        'Current build
        .Build = 0
        'Name of this app, probably a global variable, such as GSAPPNAME
        .AppName = "UpdateAnAddin"
        'Get rid of possible old backup copy
        .RemoveOldCopy
        'URL which contains build # of new version
        .CheckURL = "http://www.jkp-ads.com/downloads/UpdateAnAddinBuild.htm"
        'Started check automatically or manually?
        .Manual = bManual
        'Check once a week
        If (Now - .LastUpdate >= 7) Or bManual Then
            .PlaceBuildQT bManual
        End If
    End With
TidyUp:
    On Error GoTo 0
    Exit Sub
End Sub

Download Demo

Download the demo file here: Update An addin


Comments

All comments about this page:


Comment by: Nigel Heffernan (2/20/2007 3:19:28 AM)

This is functionally identical to the way we control our add-ins at *mumble mumble large bank in London*, with the exception that we run it over the intranet with a defined 'apps' server address and no need for the HTML controls.

However, rolling out our add-ins to overseas offices makes this web-based solution quite attractive, and I will be discussing it with my colleagues.

A very neat piece of work.

 


Comment by: Jan Karel Pieterse (2/20/2007 6:38:40 AM)

Hi Nigel,

Thanks for your feedback, much appreciated.

 


Comment by: J T F (11/27/2007 4:40:54 AM)

I have implemented this same type of functionality, but this code is much more elegant. It does still suffer from the exact same problem that I have with mine; it apparently cannot update from a username/password protected http page. It would appear that the API call (URLDownloadToFile) does not support any authentication. This is a critical problem for most us, as we are required to protect our code from casual downloading.

Any ideas on how to alleviate this?

 


Comment by: Jan Karel Pieterse (11/27/2007 5:34:10 AM)

Hi JTF,

One way around that would be to create a web service on your site that is implementing proper authentication and call that webservice from VBA to retrieve a file.

 


Comment by: Matthew Pfluger (1/21/2008 11:24:14 AM)

Could you implement this for Name manager? It would be helpful.

Also, I wrote an add-in that serves as a company-wide storage for custom macros. I placed it on our shared drive and instructed everyone to load the add-in without copying it to their hard drive. I use a WIP version that I overwrite the public version with when I deem it stable. There have been no major problems with this method at this point.

 


Comment by: Jan Karel Pieterse (1/23/2008 11:32:52 PM)

Hi Matthew,

Good idea. But since NM is also part of a commercial addin by Charles Williams (FastExcel, www.decisionmodels.com) I don't think we can implement that.

 


Comment by: DetePayoday (2/18/2008 1:27:09 PM)

The only place where I could find what i was looking for.
I will be back soon.

 


Comment by: Brian (2/26/2008 8:21:27 AM)

Will this work on Mac machines? I'm guessing "no" since there's the dll call in the "InternetGetConnectedState" function, but it also appears that this function is unnecessary (the "isconnected" variable doesn't get used anywhere, as far as I can tell).

I also have concerns about security. I have free, beta and full versions of my app. I would like all of them to have this update capability. Would it be reasonably secure to have each in a separate sub directory on the web, with indexing disabled on each of them (as well as on the next higher directory) so people wouldn't be able to just navigate to the files? Obviously, I would need to put in some #if statements in the code to make sure each version gets the proper update.

By the way, thank you so much for what you've made available on this site. It's great!

 


Comment by: Jan karel Pieterse (2/26/2008 9:53:34 AM)

Hi Brian,

I guess you're correct, IsConnected isn't in use anymore so you could get rid of it.

Of course security on your site is a different matter, up to you to decide how you arrange that. I just have the file available in a specific folder.

 


Comment by: Brian (2/26/2008 10:48:03 AM)

Will getting rid of IsConnected make it Mac compatible?

 


Comment by: Jan karel Pieterse (2/26/2008 11:24:39 AM)

Hi Brain,

I hope so, don't have a MAC. Make sure you remove the InternetGetConnectedState declaration lines as well.

 


Comment by: Elashdaf (3/31/2008 7:58:22 PM)

Hello
Wasjust serfing on net and found this site...want to say thanks. Great site and content!

 


Comment by: jeffersonwhite (4/1/2008 3:14:00 AM)

Thanks for an interesting post

 


Comment by: Eric (4/4/2008 1:53:33 PM)

Just curious why you have AutoUpdate and ManualUpdate run CheckAndUpdate in different ways, as shown below. Is there some advantage in calling a procedure via "Application.OnTime Now, ..."? I just noticed you can't step through the code if you do it that way - interesting...

Sub AutoUpdate()
    CheckAndUpdate False
End Sub

Sub ManualUpdate()
    On Error Resume Next
    Application.OnTime Now, "CheckAndUpdate"
End Sub

 


Comment by: Jan Karel Pieterse (4/6/2008 4:36:29 AM)

There is no real reason for the difference here. The Ontime method ensures the entire call tree is finished and gives Excel the chance to finish any pending work. It sometimes prevents unexpected behaviour.

 


Comment by: JPG (4/22/2008 7:08:15 AM)

Hey, I'm trying to use a slightly modified version of this and I am having some trouble getting it to work. The problem occurs in RemoveOldCopy(). The first time I update, it works fine, however, any subsequent attempts to update fail because a "Permission denied" error is thrown in RemoveOldCopy(). This is because, after updating once, there is already a file in the XLStart directory named something like Addin.xla(OldVersion) so the line:
Kill TempAddInName
fails because Excel has the file locked. Even though the file extension has been changed, Excel still loads it. Is there any way to work around this?

 


Comment by: Jan Karel Pieterse (4/22/2008 7:56:17 AM)

Hi JPG,

The code fails because Excel loads EVERYTHING from the XLSTART folder, whatever it may be called.
Addins do not belong in the XLSTART folder, they should be placed anywhere but the XLSTART folder and be "installed" using Tools, Addins, browse. Once setup this way, things should work as advertised.

Regards,
Jan Karel.

 


Comment by: JPG (4/23/2008 6:52:27 AM)

Unfortunately, I don't have a choice, the addin needs to be in the XLStart folder. I worked around the issue by creating a batch file that is run when the addin is closed(when Excel exits) that waits 10s, then deletes the "(OldVersion)" file.

 


Comment by: Jan Karel Pieterse (4/23/2008 8:06:35 AM)

Hi JPG,

OK, if that is your requirement. You might get away with setting the addin's readonly property to true?

 


Comment by: Dominic (8/27/2008 6:17:55 AM)

Great code - haven't actually trialed it yet, just had a look through.

Main question I had was whether this would be a good mechanism for updating COM
Addins ?

Advice appreciated

 


Comment by: Jan Karel Pieterse (8/28/2008 5:08:24 AM)

Hi Dominic,

Hmm, dunno for sure since if the dll is the one checking for updates it is likely
you can't replace the file. Also a dll has to be unregistered before removal, which
is a bit akward if it is running.
I think you should be able to find samples for dll internet updates if you do a
google search for it?

 


Comment by: Unison (10/6/2008 4:32:05 PM)

well... really good post!

 


Comment by: Deveadami (3/6/2009 7:13:15 AM)

Great post dude,
Thanks alot......for sharing.
You are so helpful......

 


Comment by: ScottFree (12/24/2009 11:09:39 AM)

This is Great! I see that you wrote it for an Excel 2007 addin; any reason it would not work for a 2003 addin?

 


Comment by: Jan Karel Pieterse (12/25/2009 3:27:30 AM)

Hi Scott,

This is not for Excel 2007 per se, it works for any version.
Excel 2007 just has an extra peculiarity to recon with.

 


Comment by: JimC (1/12/2010 9:53:57 AM)

Hi Jan,

I modifed the code based on what I needed, which included being able to update multiple other files, not just the add-in containing the code.

While it works great in Excel 2003, I have found that it doesn't work in Excel 2000 (and unfortunately I still need to support users with both versions). Everything copies, it's just that functions in the add-in contining the code do not work until Excel is restarted.

Any idea on what would need to be done to have it work in Excel 2000?

 


Comment by: Jan Karel Pieterse (1/12/2010 11:47:27 PM)

Hi Jim,

Are these User Defined Functions (UDF's) called from worksheet formulas?
Note that the update does not take effect until the user restarts his Excel anyway. Also note that maybe you are experiencing what is described here:

http://www.jkp-ads.com/Articles/FixLinks2UDF.asp

 


Comment by: Merrill McHenry (2/10/2010 12:54:44 PM)

My goal is to totally disable a spreadsheet months after it's date of origin (by referencing a date cell i have auto-updated when a data cell is updated). What would be the VBA code (or change from above for that)?

Thank you kindly

Great stuff - thanks

 


Comment by: Jan Karel Pieterse (2/11/2010 5:42:03 AM)

Hi Merrill,

Something like this in the Workbook_Open event in the ThisWorkbook module:

If Date - ThisWorkbook.Names("DateCell").RefersToRange.Value > 60 Then
    Msgbox "Workbook has expired!!", vbExclamation
    Thisworkbook.Close False
End If

 


Comment by: Jarom Petrich (4/11/2010 1:53:30 AM)

Wow, excellent post.

I'm still a newby to Visual Basic, but have recently created an addin that needs a self updating feature. This addin is stored on a network drive and doesn't have a build number, instead the addin name changes from "MyAddinV1.12", to "MyAddinV1.13". I've tried unsuccessfuly to rework your code to suit my purposes. Any help/insight would be greatly appreciated.

 


Comment by: Jan Karel Pieterse (4/11/2010 2:27:48 AM)

Hi Jarom,

I would change that and use the same addin name.
If you place a new copy of your add-in in that network location, set it to read-only. This means next time you can simply replace the file even while people are using it.

If everyone is using the addin off of the network, you are done. Otherwise for example if people use laptops and are offline sometimes), I would place a simple textfile in the same network folder which only contains your build number. If you give that file the html extension, you can use that in the example code I gave, it should work.

 


Comment by: david helks (8/14/2010 3:10:57 AM)

Hi
sorry to appear dumb.

I tried to run the macro this weekend Fri eve 13/8 and Sat am 14/8. I keep getting 'page not found' error when it tries to look for the url

http://www.jkp-ads.com/downloads/UpdateAnAddinBuild.htm

the macro just dumps the text of the web page in cell B1 and below. B1 contains text HOME rather than a number

I assume the macro errors because it has set a text string to the version number when it expects a numeric value for the version number

appreciate your advice. Am sure it's something simple
thanks
David

 


Comment by: Jan Karel Pieterse (8/16/2010 4:41:44 AM)

Hi David,

The correct url for that web page is:
http://www.jkp-ads.com/UpdateAnAddinBuild.htm

 


Comment by: Matt Holt (8/16/2010 2:51:21 PM)

Hi JKP, this is a great utility, thanks for making this available.

Just a quick question, my add-on currently can function when no workbooks are open and it stores any configuration settings to a .properties file.

Am I correct in saying that your utility requires a workbook so it can save the query? Is there anyway to modify it so a workbook doesn't have to be open (and if need be, I can store any settings in my properties file)?

Thanks a bunch,
Matt

 


Comment by: Jan Karel Pieterse (8/16/2010 11:14:51 PM)

Hi Matt,

The code uses a worksheet in your addin itself, so it needs no other open workbooks. Of course you can also use a different approach, where you do not use a webquery to read the web page, but do it all from within VBA, using -for example- the Microsoft Internet Controls object library.

 


Comment by: aodeng (10/27/2010 12:37:28 PM)

Hi Matt,

I am doing something like automatic update and your example help me a lot. I got a problem though after I made some changes: the downloaded file and the "old version" file are both saved to C:\Documents and Settings\USERNAME\My Documents, not the add-ins directory. Do you know what is the reason?

 


Comment by: aodeng (10/27/2010 2:36:12 PM)

Matt,

please ignore my previous post, I found why the files are located at my documents, that's because I modify the code and change the file's fullname to name thus missing the path information.

You code actually help me a lot. Thanks

 


Comment by: William Benson (1/2/2011 10:15:01 PM)

The sample file is producing an error because the word "Home" is appearing after the web query refreshes, in a cell which you have designated as available build.

 


Comment by: Jan Karel Pieterse (1/2/2011 10:17:41 PM)

Hi Bill,

Thanks!

 


Comment by: Aaron (1/19/2011 8:57:34 AM)

I have looked everywhere and am unsure of how to give my addin a build so i can automatically update. any help?

 


Comment by: Jan Karel Pieterse (1/19/2011 9:11:07 AM)

Hi Aaron,

There are many possibilities here. You can have a hidden worksheet which contains the build number in cell A1, you can define a range name that refers to a constant (the build number), you can hard-code the buildnumber into your VBA as a constant, up to you really!

 


Comment by: Colin Norris (4/13/2011 8:28:05 AM)

Hi JKP

This feature does exactly what I need, however I'm struggling to update it to work from a network drive location rather than a website. when i try to run the code i get an error user defined type not defined in section

Public Function IsThereAnUpdate(sError As String, Optional bShowMsg As Boolean = False) As Boolean

with the following line highlighted
Dim oHTTP As MSXML2.XMLHTTP

Please help
Colin

 


Comment by: Jan Karel Pieterse (4/13/2011 9:31:05 AM)

Hi Colin,

You need to add a reference to the Microsoft XML v3.0 type library (VBA editor, tools, references)

 


Comment by: William Benson (4/30/2011 9:00:54 AM)

Hi, the example file is an XLS file, not an installed add-in. So I would say this is for updates to an Excel workbook, not an installed add-in. I take it that there has to be a lot of additional code for converting the workbook thereafter to an Add-in, such as the user using SaveAs, and making the file an addin some place. Of course if the user has the add-in installed, this is very difficult to achieve 100% reliably while Excel is open - which, if handled via this workbook itself, is the case.

I do have Visual Studions Pro 2008 but don't know much about it. Is it possible to use that package to create an installer package to deliver the add-in and do checks on version - the way this workbook does - without using Excel? I am hopin that in this way it might be possible, so long as Excel is closed, to enable to user to download an update into a temp folder, kill the old add-in, and overwrite with the new one - without using Excel as the tool for exchange.

 


Comment by: Jan Karel Pieterse (5/1/2011 9:47:04 PM)

Hi William,

Though the example file is an xls the code works on any Excel file type, including add-ins. No additional code is needed to convert to add-in if you use an add-in file to start with. Sorry if the example confused you.

 


Comment by: HuctusKic (6/13/2011 7:18:55 PM)

just examined the topic! great work.

 


Comment by: Ken (6/20/2011 11:18:46 AM)

Hi - just stumbled across this and sounds great. I have an add-in to distribute and there are 3 ancillary files that go with it and should be in the same directory. One is a help file and the other two files relate to a free editor that I distribute. I do not have a webpage to download from. We have an FTP site that requires a password, but have tried for months to use that option and it won't work. Right now I have my files in a zip file on a Sharepoint site (not all users have access to it). The users have to manually install the add-in by opening the zip and saving the files themselves. Would like to change that to something more automatic. Are there webpages I can "rent" to post my files to that this will work on?

 


Comment by: Jan Karel Pieterse (6/21/2011 1:17:50 AM)

Hi Ken,

Why not use e.g. a Microsoft Live account. You can publish files there and make them publicly available, no cost involved.

 


Comment by: Don (9/8/2011 8:22:03 PM)

Hello,

Thanks for this code. I've modified it a bit to work with a simple file copy from out local network.

However, I am running into one problem. When the add-in gets saved as the temporary filename (so that the new addin can be placed in the right spot). And the used saves the current workbook when the update process finishes, all the references of the UDT in the addin are changed to external links that point to the temporary filename.

I can't seem to get Excel to not make these changes to the users formulas.

My first thought is to make the user close all open workbooks before the update check. But wanted to see if you had a any other suggestions.

Thanks,
Don

 


Comment by: Jan Karel Pieterse (9/9/2011 3:10:23 AM)

Hi Don,

I think the best solution is to have the add-in fix the references when a file is opened. See:
http://www.jkp-ads.com/Articles/FixLinks2UDF.asp

 


Comment by: Klaus (9/29/2011 1:17:04 AM)

Hi Don,

sounds interesting. Just wondering if you could make a copy of your modification available?

 


Comment by: DeneenGeel (3/5/2012 11:16:54 PM)

hello there and thank you for your info – I have certainly picked up anything new from right here. I did however expertise several technical issues using this web site, as I experienced to reload the site lots of times previous to I could get it to load correctly. I had been wondering if your web hosting is OK? Not that I am complaining, but sluggish loading instances times will sometimes affect your placement in google and could damage your high quality score if advertising and marketing with Adwords. Anyway I’m adding this RSS to my e-mail and can look out for a lot more of your respective fascinating content. Ensure that you update this again soon.

 


Comment by: Ken (5/21/2012 10:40:40 AM)

I am using your code and it works fine. I did have a question. When my build number gets to 10 will it still read it correctly or is it expecting a 1 digit build number?

 


Comment by: Jan Karel Pieterse (5/21/2012 11:46:04 AM)

Hi Ken,

It will treat it as a number, so should work fine. My Autosafe tool uses this and it is at build 134.

 


Comment by: Bob (8/23/2012 5:46:07 AM)

I just found this website - if the update module is used with an Add-In that is wrapped in a Setup.exe file (in another article you suggest IndigoRose) can the update still work without downloading a new setup file?

 


Comment by: Jan Karel Pieterse (8/23/2012 10:09:07 AM)

Hi Bob,

That depends on what exactly the setup file does/has to do incase of an update.

If the update requires the setup program to do more than just copying the new file, like making changes to the registry, then it will need extra coding to do those.

 


Comment by: Boopathi (10/26/2013 5:52:44 PM)

I am in the learning phase of excel vba and have done few add-ins and it has been stored in our network folder. I have distributed to by colleagues once. But I would like to have an auto update feature that when I update an addin in my network folder, the existing old file should get updated automatically in my colleagues desktop. FYI: I am not using any FTP/HTTP mode.

Any help would be much appreciated.

 


Comment by: Jan Karel Pieterse (10/27/2013 5:39:40 PM)

Hi Boopathi,

All you have to do is make sure the add-in is placed on a network share and set to read-only (using file properties from Windows Explorer). Now install the add-in for everyone using that network share and do not copy the file to their local computer.

If you need to update, simply replace the network file and set it to read-only again.

 


Comment by: Eric (11/9/2013 5:18:31 PM)

This is really good stuff! I don't see an area of the code where you remove the add-in from the list of add-ins in Excel. Do you know a way to handle that problem?

Thanks!

 


Comment by: Jan Karel Pieterse (11/9/2013 7:22:53 PM)

Hi Eric,

Why would you want to remove the add-in from the list if you're just updating it?

 


Comment by: Eric (11/9/2013 11:05:40 PM)

Hi Jan,

Here is my situation:

I have an add-in that I have serialized into a database. I've written an installer that can ask the database what version is appropriate for the user (based on some criteria). I identify which add-in they're on based on the filename (which violates one of your assumptions at the top). I noticed that you set the current version to 0 each time the update is checked, so essentially the add-in is updated whether or not there is a change. I my case that's not desirable because I have written the add-in in VB.NET and the user would lose their settings every day if I force upgrade when no new version is available. I am open to using the same name if I can still identify what version is currently installed. Otherwise I need to completely remove the old version and put in the new one.

 


Comment by: Jan Karel Pieterse (11/11/2013 7:23:51 AM)

Hi Eric,

Setting the current build to zero is just to initialize the property in question, the actual (on-line!) build number available for download is fetched in the class.
The build number of the file itself is either stored in a cell in a sheet or as a constant in the code.
I think there are existing (sometimes paid-for) tools for .NET add-ins to handle updating.

 


Comment by: Peter Slezak (6/18/2014 5:57:01 PM)

Hi Jan,
I found your site very useful especially this part about addin updating. Hovewer, when I try to adjust your code to work with Addin (XLAM) not (XLS) it works just perfect but there is one problem. I just changed the name of your original file and I changed the URL for downloading the file. The new version of addin is downloaded correctly, the old one is renamed to “updateanaddin.xlam(old version) “. But when I restart excel I get the error:
“Excel cannot open updateanaddin.xlam file because the file format or file extension is not valid. …”

Do you know a way to handle this problem?

 


Comment by: Jan Karel Pieterse (6/19/2014 7:18:18 AM)

Hi Peter,

Not sure what goes wrong on your end, all works fine with the add-ins from me that use this technique.
I would need to see your code adjustments to find out what is wrong.

 


Comment by: Peter Slezak (6/19/2014 10:04:09 AM)

Thank you for the fast response.


I've just saved the file as excel addin, changed the URL to download update from my dropbox folder.

https://www.dropbox.com/s/thpjtcy2m501ymq/updateanaddin.xlam

You can download the file from there.
And one more thing I added 'ptrsafe' to functions declaration since I'm working with 64bit Excel 2010.
Thank you very much in advance.

 


Comment by: Jan Karel Pieterse (6/19/2014 10:26:04 AM)

Hi Peter,

The reason your code fails is because the downloadlink to dropbox does not download an Excel file, but rather the HTML of the Dropnox webpage that link takes you to.

I expect if you use this url all works as expected:

https://www.dropbox.com/s/thpjtcy2m501ymq/updateanaddin.xlam?dl=1

 


Comment by: Peter Slezak (6/19/2014 10:38:20 AM)

Thank you Jan,
It was really ID107 error.

 


Comment by: Daniel W (8/18/2014 10:50:34 PM)

Jan,

This looks awesome but I'm having trouble getting it going on my file. I don't understand everything that's going on in the process so I'm sure I'm missing something.

I downloaded the example you have and copied over the modules, including the class module to my addin. I changed this piece:

         .CheckURL = 'w.jkp-ads.com/Updateanaddinbuild.htm'
        .DownloadName = 'w.jkp-ads.com/downloadscript.asp?filename=' & ThisWorkbook.Name


to
         .CheckURL = 'mysite'/Updateanaddinbuild.htm'
        .DownloadName = 'mysite'/downloadscript.asp?filename=' & ThisWorkbook.Name


My local file I'm testing is named Merlin_wUpdate_v0.xlam and the server file is named ...v1.xlam. I've created a simple htm file by going into notepad, typing '1' and saving as Updateanaddinbuild.htm

That's all I've edited but it doesn't work. When I try to run a manual update 'Oops, an error has occurred in Update Addin Demo Error 104: Application-defined or object-defined error in CheckAndUpdate.Module modUpdate'

I'm sure it's something I did/didn't do. Any advice? PS - I had to edit some of the links to be able to post but hopefully it makes sense.

 


Comment by: Jan Karel Pieterse (8/19/2014 10:02:37 AM)

Hi Daniel,

I expect the problem lies in your download URL.
I would first try entering the download url directly in a to check whether that actually leads to a downloadable file. Probably you need to remove this from the code:

"downloadscript.asp?filename="

because that part stems from my own website which is used to count the number of downloads per file.

So if your file is located in www.yoursite.com/SomeFolder/yourfile.xls
then that should be the resulting URL for the download.

 


Comment by: Gary Michalske (1/5/2015 8:52:19 PM)

I'm with William Benson regarding and actual XLAM file. I don't see how the code will actually overwrite the Add-In file which is stored (Windows 7) by default in C:\Users\username\AppData\Roaming\Microsoft\AddIns. To do something similar, I had to create a VBScript file to actually install the Add-In on the user's machine. The Add-In file and the VBScript file are both stored on a file share. When the user clicks on the VBScript file link in their email, the VBScript file runs.

The code is too long to post in one post (the page will only allow 2000 characters) so I'll split it up.

Here's the first part of the code I had to use to get the Add-In to update:

Const OverwriteExisting = True
Dim objFSO, objNetwork, objXL

On Error Resume Next
Set objXL = GetObject(, "Excel.Application")

If Not TypeName(objXL) = "Empty" Then
MsgBox "Excel must be closed to install the addin. Please save your work, close excel, and then click 'OK'", vbOKOnly + vbInformation, "Excel Status Check"
objXL.Quit
Set objXL = Nothing
End if

Set objNetwork = CreateObject("WScript.Network")
Set objFSO = CreateObject("Scripting.FileSystemObject")

For Each os In GetObject("winmgmts:").InstancesOf("Win32_OperatingSystem")
    ver = Left(os.Version, 3)
Next

sUser = objNetwork.UserName
sFileLocalAddin = "C:\Users\" & sUser & "\AppData\Roaming\Microsoft\AddIns\sap_xl_addin.xlam"
sFileServerAddin = "\\<server>\Data\TSE\Installs\SAP\sap_xl_addin.xlam"

 


Comment by: Gary Michalske (1/5/2015 8:54:06 PM)

And here's the last part of the VBScript Code:

'**************************************************************************************
'install addin if it doesn't exist; if it does, then update it with the latest version.
'**************************************************************************************
    If Not objFSO.FileExists(sFileLocalAddin) Then
    
MsgBox "The SAP Excel Addin installation may take up to 2 minutes depending on your " _
& "distance from the Nashville server. A notification will appear once the installation " _
& "is successful.", vbInformation, "SAP Excel Addin Installation"
Set oXL = CreateObject("Excel.Application")
oXL.Workbooks.Add
Set oAddin = oXL.AddIns.Add("\\172.18.59.60\Data\TSE\Installs\SAP\sap_xl_addin.xlam", True)
oAddin.Installed = True
oXL.Quit
Set oXL = Nothing
MsgBox "The SAP Excel Addin has been installed successfully.", vbInformation, "SAP Excel Addin Installation"

    Else
    
        Set objLocalFile = objFSO.GetFile(sFileLocalAddin)
        Set objServerFile = objFSO.GetFile(sFileServerAddin)
        objFSO.CopyFile objServerFile.Path, objLocalFile.Path, OverwriteExisting
        MsgBox "The SAP Excel Addin was successfully updated.", vbInformation, "SAP Excel Addin Update"
        
    End If

 


Comment by: Jan Karel Pieterse (1/6/2015 9:54:29 AM)

Hi Gary,

The trick is that the code first does a save-as of the "old" add-in file into the same folder, thereby creating a new add-in file with a different name. The side effect being that the original "old" add-in file is not no longer marked as being open in Excel (it is released by Windows too) so you can then replace it. This is the next step of the code: download the updated add-in and save it to the same location, replacing the old copy. The final step is performed after a restart of Excel: the new add-in's code checks if the folder contains any "old versions" and deletes them.

 


Comment by: Gary Michalske (1/8/2015 3:59:22 AM)

Hi Jan,

I stepped though your code to get a better understanding of it and now I understand what you mean about the Old save "trick". It works just fine with an xlam file and overwrites it exactly in the same place.

I was able to save my xlam file to a SharePoint document library and a network file share and get it to download just fine from both locations. I am having an issue trying to read the htm file though. When I create a simple htm file from notepad and upload it to either a SharePoint library or Microsoft One Drive on Live.com, the variable is returning all of the html code instead of just the "1". How exactly are you able to get the variable to just return the "1". I may have to resort to your alternative suggestion of using the InnerHTMl method.

 


Comment by: Jan Karel Pieterse (1/8/2015 10:55:28 AM)

Hi Gary,

My code assumes it can go to a webpage and all that page contains IS the buildnumber. Indeed if your situation differs, by all means read the innerhtml and go from there.

 


Comment by: Gary Michalske (1/8/2015 6:00:07 PM)

Yeah, I noticed that. When I go to your html page and view the source, all I see is the "1". When I go to any other web page, including the ones I've been creating to work with this solution, I see all the HTML code when I view the source. How are accomplishing that? Are you dymanically creating your web page with ASP or PHP from your server or something?

 


Comment by: Jan Karel Pieterse (1/8/2015 10:54:15 PM)

Hi Gary,

An HTML file is not much more than a textfile, so I just have a textfile with that content which I upload to my site.

 


Comment by: Gary Michalske (1/9/2015 12:53:57 PM)

I think I discovered the issue. When I create a file in notepad with a HTM extension, open it and view it's source, all I see is the version number. After I upload it to SharePoini and open it I see the following added code (the 13 at the upper left is the version number):
13<html xmlns:mso="urn:schemas-microsoft-com:office:office" xmlns:msdt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"><head>
<!--[if gte mso 9]><xml>
<mso:CustomDocumentProperties>
<mso:_dlc_DocId msdt:dt="string">DPNRWTWJ2VJ5-4-7</mso:_dlc_DocId>
<mso:_dlc_DocIdItemGuid msdt:dt="string">c0b91160-d69b-4dec-a6c2-b9decbac7c2b</mso:_dlc_DocIdItemGuid>
<mso:_dlc_DocIdUrl msdt:dt="string">https://mysite.tcco.com/personal/gmichalske/_layouts/DocIdRedir.aspx?ID=DPNRWTWJ2VJ5-4-7, DPNRWTWJ2VJ5-4-7</mso:_dlc_DocIdUrl>
</mso:CustomDocumentProperties>
</xml><![endif]-->
</head>

So What I did to solve the issue was to upload the file with a TXT extension. Then your code was still able to open it and return the version number correctly.

 


Comment by: Gary Michalske (2/2/2015 5:37:53 PM)

Thanks again for this very helpful VBA class. I've been able to implement it and get it to work with the supporting files stored on an intranet SharePoint site.
One thing I can't seem to understand is how the parameters are working with the Application.OnTime function. I’ve researched this on the msdn.com site but fail to understand how
"'" & ThisWorkbook.FullName & "'!CheckAndUpdate"
is able to call the CheckAndUpdate procedure. I guess I don’t see how the exclamation point, apostrophe and quotation marks work together. I also noticed that you are not able to step through the code when calling the ManualUpdate procedure as once the Application.OnTime line completes the code seems to stop.
Will you explain all of this please?
Sub ManualUpdate()
    On Error Resume Next
    Application.OnTime Now, "'" & ThisWorkbook.FullName & "'!CheckAndUpdate"
End Sub

 


Comment by: Jan Karel Pieterse (2/2/2015 5:50:47 PM)

Hi Gary,

The OnTime method schedules a certain macro to run at a set time. However if you have more than one workbook open which also happen to have macros with equal names you may be in for a spurprise. You will have no control over which "workbook version" of that macro will be called if you simply use the name of the macro.

That is why I always prepend the name of the macro with teh full name of the workbook containing the macro. The syntax to that is very similar to the syntax of a range name you want to point to that is in another file:

'c:\users\jkp\documents\some file.xlsm'!TheMacroYouWantCalled

The apostrophes are there to make sure all works well when there are spaces in the filename or other characters Excel doesn't like much.

 


Comment by: Gary Michalske (2/26/2015 1:25:12 AM)

Hi Jan,

I have a question using the URLDownloadTofile funtion. When I use that function, and then open the file, it seems to render the file in state where I can't call a procedure in it from another workbook. For instance, I'm using this line of code to run a procedure called ExportModule. Here's the line of code:

If Application.Run(wbNew.Name & "!ExportModule") = False Then Exit Function


I'm using the ExportModule function to export all modules from the Master file from the website, then temporarily import them into to the Slave file on the user's computer and then transfer the code from each module from the Master to the Slave. Then the Master modules are removed fromt the Slave.

If I open the Master with Workbooks.Open (<fullPathToFile>), the ExportModule function runs just fine. When I try to open it after downloading it with the URLDownloadToFile function, I get an error that says macros may not be enabled.

Any ideas how to use the URLDownLoadToFile function successfully? I like this method better because it's faster than the direct open method.

 


Comment by: Jan Karel Pieterse (2/26/2015 6:24:22 AM)

Hi Gary,

Probably Excel is opening the file in protected view (in the UI you would have to click enable editing).
For the activeworkbook, you can enable editing using:

application.ActiveProtectedViewWindow.edit

 


Comment by: Gary Michalske (2/26/2015 7:25:27 PM)

Well it wasn't in a protected mode because I didn't see the "Enable Editing" button and the line of code gave me the "With Statement or Object not set..." error. At any rate, it ended up working anyway so I'm not sure at this point why it was failing last night. Maybe Excel was as tired as I was...

 


Comment by: Ali (6/3/2015 2:15:06 PM)

Hello
thanks for the great article and i would like to know if i change the build number in the code to be like "1" and then i saved a word file as html include the new build number like "2" and uploaded to my dropbox and also the addin to my dropbox.
please advice if it will work like this? or there is something more technical i have to do?

thank you

 


Comment by: Jan Karel Pieterse (6/3/2015 4:48:53 PM)

Hi Ali,

Well, I would use a text editor like NotePad and save a file with JUST the number as a textfile. Then rename the .txt to .html and upload that file to your site somewhere.

 


Comment by: Ali (6/3/2015 7:48:33 PM)

Hi Jan
actually thanks for reply to my question and i have one more last question if i may, i could upload the new html file i named it "newversion" and it has the build number "2" only number two, but i can`t actually know how to set a build number for the current version?

 


Comment by: Jan Karel Pieterse (6/4/2015 7:31:55 AM)

Hi Eduardo,

The HTML file contains the number for the most recent version of your add-in. The add-in itself contains the verison number of the add-in. So the code compares the version number IN the add-in with the HTML file and if the html file has a higher number, you know you can offer the user to download the new version of the add-in.

 


Comment by: Ali (6/4/2015 8:34:56 PM)

hi Jan
me again and apologize for many questions.
i was able to get the build issue fixed i could set build for the current version and to the html for the new version.
everything seems to be fine but the problem when i click " Update" and it finish a new file is created named xxxOld Version, and the current file which i run the update from it when i try to reopen it gives extention change error and never open. beside the addin its not really updated.
do you make a video for this tutorial before? or can you help me out with these problems,
thanks

 


Comment by: Ali (6/5/2015 6:01:59 PM)

hello
actually i am shy to question again. but kindly ignore my previous questions after reviewing the comments i saved your demo copy as xlam file and i was able to run the updated but the only problem now the new version does not replace the current version although all same name. but the both appear side by side in excel tabs. i dont know how to fix this problem and also when i tried to copy the modules and classes to my addin itself i got hidden module compile in the class module :(
sorry JKP

 


Comment by: Jan Karel Pieterse (6/8/2015 6:14:08 AM)

Hi Ali,

Do you by any chance place the xlam in the XLSTART folder? That is not a good place for and add-in.

 


Comment by: Ali (6/8/2015 8:07:45 AM)

Hi Jan.
i got it worked after all actually thanks to your recommendations to all members i reviewed all comments and got many fixes.
now it works like a charm and not from XLStart just anywhere the addin it works great.
thanks for the 100% useful addin.
Respect.

 


Comment by: Bill (6/21/2015 6:02:56 PM)

Thanks Jan for creating this! I had been searching for a while for a really good way to automatically update add-ins, but nothing was really what I wanted...then I found your elegant and inspired solution.

I implemented your code with one change. In the function IsThereAnUpdate I found that MSXML2.XMLHTTP was using cached responses, so I switched to WinHttp.WinHttpRequest and everything works great! Thanks again! /bill

 


Comment by: Elle (12/12/2015 1:07:51 AM)

Hi Jan,

at first: Big Thanx for this code. I have edited it to my needs and it works!

But I have 2 questions:
1. Where do I have to change the Build number, if I make an update? Is it right, that I have to change the value of the cell named "Available_Build" and also the same number in my htm-file?

2. (the bigger one):
Inside my AddIn I have some sheets, wich control the AddIn's functionallity. Some values of the sheet "Einstellungen" will be change by the users to set theyr settings.

HOW and WHERE can I copy these settings to the updated new AddIn-Sheet("Einstellungen")?

Thank you for support
nice regards
Elle

 


Comment by: Jan Karel Pieterse (12/14/2015 9:00:13 AM)

Hi Elle,

The workbook contains a rangename called Build, which has this formula: =0. If your buildnumber equals 101, you should edit that range name so its formula becomes =101

Your version of the update code should handle copying of settings. In the class there is a routine called "RemoveOldCopy". This is where you add code to open the old copy and copy its settings to the new one, just before the line that kills the old version.

 


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].