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

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

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

Showing last 8 comments of 93 in total (Show All Comments):

 


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].