I need some way to update an excel addin shared among my staffs so as everyone don't have to download & install it manually.

I have googled and see that we can write file to the OS file system so the task ends up with writing the new-version addin, i.e. the .xlam file, to overwrite itself.

I have no idea on how to do this. If you do have ones, please share! Thank you!

I don't know if there's a less crude way of doing it, but I have "hacked" a solution that involves SendKeys. Yuck, I know. Hopefully someone else will have a better solution.

As I recall, you need to uninstall an addin before you can overwrite the .xla(m) file and I couldn't find a way to do this purely using built-in objects.

The code below basically uninstalls the add-in, invokes the "Add-ins" dialog box and uses SendKeys to remove it from the list, before copying the new file and reinstalling the add-in.

Amend it for your circumstances - it will depend on your users having their security settings low enough to let it run, of course.

Sub UpdateAddIn()
    Dim fs As Object
    Dim Profile As String

    If Workbooks.Count = 0 Then Workbooks.Add
    Profile = Environ("userprofile")
    Set fs = CreateObject("Scripting.FileSystemObject")
    AddIns("MyAddIn").Installed = False
    Call ClearAddinList
    fs.CopyFile "\\SourceOfLatestAddIn\MyAddIn.xla", Profile & "\Application Data\Microsoft\AddIns\", True
    AddIns.Add Profile & "\Application Data\Microsoft\AddIns\MyAddIn.xla"
    AddIns("MyAddIn").Installed = True
End Sub

Sub ClearAddinList()
    Dim MyCount As Long
    Dim GoUpandDown As String

    'Turn display alerts off so user is not prompted to remove Addin from list
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    Do
        'Get Count of all AddIns
        MyCount = Application.AddIns.Count    

        'Create string for SendKeys that will move up & down AddIn Manager List
        'Any invalid AddIn listed will be removed
        GoUpandDown = "{Up " & MyCount & "}{DOWN " & MyCount & "}"
        Application.SendKeys GoUpandDown & "~", False
        Application.Dialogs(xlDialogAddinManager).Show
    Loop While MyCount <> Application.AddIns.Count    

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub