Installer automatiquement VBA excel add-in

J'ai créé une installation automatique de la procédure pour être ajouté à la “Ce Classeur” section du fichier XLAM, de sorte qu'il s'exécute automatiquement lorsque le fichier est ouvert.
Afin de différencier le fichier d'installation et le fichier installé, l'installation de la version nommée “.installer.xlam” et la version installée est nommé “.xlam”. (Sinon Excel a une “Désolé, Excel ne peut pas ouvrir deux classeurs avec le même nom à la même heure.”

Procédure:
– Renommez votre fichier XLAM .installer.xlam
– L'ouvrir et le modifier dans Visual Basic Editor (VBE)
– Ajouter les procédures suivantes pour le “Ce classeur” dans VBE
– Enregistrer votre fichier

Afin de partager/l'installation de votre XLAM, vous pouvez maintenant demander à l'utilisateur double-cliquez sur le fichier XLAM, activer les macros nécessaires et d'accepter d'installer l'Add-In.

Si vous souhaitez mettre à jour votre XLAM plus tard, il vous suffira de double-cliquez sur activer les macros nécessaires et de refuser de l'installer. Ensuite l'éditer et enregistrer les modifications.

Voici le code à ajouter à “ThisWorkbook”:

‘ (c) Willy Roche (willy.roche(at)centraliens.net)
‘ Install procedure of XLAM (library of functions)
‘ This procedure will install a file name .install.xlam in the proper excel directory
‘ The install package will be name
‘ During install you may be prompt to enable macros (accept it)
‘ You can accept to install or refuse (which let you modify the XLAM file macros or install procedure
Option Explicit
Const bVerboseMessages = False ‘ Set it to True to be able to Debug install mechanism
Dim bAlreadyRun As Boolean ‘ Will be use to verify if the procedure has already been run
Private Sub Workbook_Open()
‘ This sub will automatically start when xlam file is opened (both install version and installed version)
Dim oAddIn As Object, oXLApp As Object, oWorkbook As Workbook
Dim i As Integer
Dim iAddIn As Integer
Dim bAlreadyInstalled As Boolean
Dim sAddInName As String, sAddInFileName As String, sCurrentPath As String, sStandardPath As String
sCurrentPath = Me.Path & “\”
sStandardPath = Application.UserLibraryPath ‘ Should be Environ(“AppData”) & “\Microsoft\AddIns”
DebugBox (“Called from:'” & sCurrentPath & “‘”)
If InStr(1, Me.Name, “.install.xlam”, vbTextCompare) Then
‘ This is an install version, so let’s pick the proper AddIn name
sAddInName = Left(Me.Name, InStr(1, Me.Name, “.install.xlam”, vbTextCompare) – 1)
sAddInFileName = sAddInName & “.xlam”
‘ Avoid the re-entry of script after activating the addin
If Not (bAlreadyRun) Then
DebugBox (“Called from:'” & sCurrentPath & “‘ bAlreadyRun = false”)
bAlreadyRun = True ‘ Ensure we won’t install it multiple times (because Excel reopen files after an XLAM installation)
If MsgBox(“Do you want to install/overwrite ‘” & sAddInName & “‘ AddIn ?”, vbYesNo) = vbYes Then
‘ Create a workbook otherwise, we get into troubles as Application.AddIns may not exist
Set oXLApp = Application
Set oWorkbook = oXLApp.Workbooks.Add
‘ Test if AddIn already installed
For i = 1 To Me.Application.AddIns.Count
If Me.Application.AddIns.Item(i).FullName = sStandardPath & sAddInFileName Then
bAlreadyInstalled = True
iAddIn = i
End If
Next i
If bAlreadyInstalled Then
‘ Already installed
DebugBox (“Called from:'” & sCurrentPath & “‘ Already installed”)
If Me.Application.AddIns.Item(iAddIn).Installed Then
‘ Deactivate the add-in to be able to overwrite the file
Me.Application.AddIns.Item(iAddIn).Installed = False
Me.SaveCopyAs sStandardPath & sAddInFileName
Me.Application.AddIns.Item(iAddIn).Installed = True
MsgBox (“‘” & sAddInName & “‘ AddIn Overwritten”)
Else
Me.SaveCopyAs sStandardPath & sAddInFileName
Me.Application.AddIns.Item(iAddIn).Installed = True
MsgBox (“‘” & sAddInName & “‘ AddIn Overwritten & Reactivated”)
End If
Else
‘ Not yet installed
DebugBox (“Called from:'” & sCurrentPath & “‘ Not installed”)
Me.SaveCopyAs sStandardPath & sAddInFileName
Set oAddIn = oXLApp.AddIns.Add(sStandardPath & sAddInFileName, True)
oAddIn.Installed = True
MsgBox (“‘” & sAddInName & “‘ AddIn Installed and Activated”)
End If
oWorkbook.Close (False) ‘ Close the workbook opened by the install script
oXLApp.Quit ‘ Close the app opened by the install script
Set oWorkbook = Nothing ‘ Free memory
Set oXLApp = Nothing ‘ Free memory
Me.Close (False)
End If
Else
DebugBox (“Called from:'” & sCurrentPath & “‘ Already Run”)
‘ Already run, so nothing to do
End If
Else
DebugBox (“Called from:'” & sCurrentPath & “‘ in place”)
‘ Already in right place, so nothing to do
End If
End Sub
Sub DebugBox(sText As String)
If bVerboseMessages Then MsgBox (sText)
End Sub

OriginalL'auteur Willy Roche