Deploying xla (Office 2003)

Topic

New Reply

A Lounge Luminary (was it HansV?) once posted code so that xla file could be emailed to each user and the code automatically installed the xla, deleting any previous versions. I’ve searched and searched with no luck. Does anyone remember this or know where it is?

Reply | Quote

Replies

Jan Karel Pieterse, a MS Excel MVP, does not list it on these pages, but in post 191952 he explains how you can download one of his programs (there is a link under his name in the post) and examine the install code (which is unprotected).

Steve

Reply | Quote

Jan Karel Pieterse, a MS Excel MVP, does not list it on these pages, but in post 191952 he explains how you can download one of his programs (there is a link under his name in the post) and examine the install code (which is unprotected).

Steve

Reply | Quote

For your convenience:

Option Explicit
Dim vReply As Variant
Dim AddInLibPath As String
Dim CurAddInPath As String
Const sAppName As String = "Name Manager"
Const sFilename As String = sAppName & ".xla"
Const sRegKey As String = "FXLNameMgr"    ''' RegKey for settings

Sub Setup()
    vReply = MsgBox("This will install " & sAppName & vbNewLine & _
    "in your default Add-in directory." & vbNewLine & vbNewLine & "Proceed?", _
     vbYesNo, sAppName & " Setup")
    If vReply = vbYes Then
        On Error Resume Next
        Workbooks(sFilename).Close False
        If Application.OperatingSystem Like "*Win*" Then
            CurAddInPath = ThisWorkbook.Path & "" & sFilename
            AddInLibPath = Application.LibraryPath & "" & sFilename
        Else
            CurAddInPath = ThisWorkbook.Path & ":" & sFilename
            AddInLibPath = Application.LibraryPath & sFilename 'syntax differs from Win
        End If
        On Error Resume Next  'This appears to be redundant
        FileCopy CurAddInPath, AddInLibPath
        If Err.Number  0 Then
            SomeThingWrong
            Exit Sub
        End If
        With AddIns.Add(FileName:=AddInLibPath)
        .Installed = True
        End With
    Else
    vReply = MsgBox(prompt:="Install Cancelled", Buttons:=vbOKOnly, Title:=sAppName & " Setup")
    End If
End Sub
Sub SomeThingWrong()
    If Application.OperatingSystem Like "*Win*" Then
        vReply = MsgBox(prompt:="Something went wrong during copying" & vbNewLine _
        & "of the add-in to your add-in directory:" _
        & vbNewLine & vbNewLine & Application.LibraryPath & "" _
        & vbNewLine & vbNewLine & "You can install " & sAppName & " manually by copying the file" _
        & vbNewLine & sFilename & " to this directory yourself and installing the addin" _
        & vbNewLine & "using Tools, Addins from the menu of Excel." _
        & vbNewLine & vbNewLine & "Don't press OK yet, first do the copying from Windows Explorer." _
        & vbNewLine & "It gives you the opportunity to ALT-TAB back to Excel" _
        & vbNewLine & "to read this text.", Buttons:=vbOKOnly, Title:=sAppName & " Setup")
    Else
        vReply = MsgBox(prompt:="Something went wrong during copying" & vbNewLine _
        & "of the add-in to your add-in directory:" _
        & vbNewLine & vbNewLine & Application.LibraryPath _
        & vbNewLine & vbNewLine & "You can install " & sAppName & " manually by copying the file" _
        & vbNewLine & sFilename & " to this directory yourself and installing the addin" _
        & vbNewLine & "using Tools, Addins from the menu of Excel." _
        & vbNewLine & vbNewLine & "Don't press OK yet, first do the copying in the Finder." _
        & vbNewLine & "It gives you the opportunity to Command-TAB back to Excel" _
        & vbNewLine & "to read this text.", Buttons:=vbOKOnly, Title:=sAppName & " Setup")
    End If
End Sub

Sub Uninstall()
    vReply = MsgBox("This will remove the " & sAppName & vbNewLine & _
    "from your system." & vbNewLine & vbNewLine & "Proceed?", vbYesNo, sAppName & " Setup")
    If vReply = vbYes Then
        If Application.OperatingSystem Like "*Win*" Then
            CurAddInPath = ThisWorkbook.Path & "" & sFilename
            AddInLibPath = Application.LibraryPath & "" & sFilename
        Else
            CurAddInPath = ThisWorkbook.Path & ":" & sFilename
            AddInLibPath = Application.LibraryPath & sFilename 'syntax differs from Win
        End If
        On Error Resume Next
        Workbooks(sFilename).Close False
        Kill AddInLibPath
        DeleteSetting sRegKey
        MsgBox " The " & sAppName & " has been removed from your computer." _
        & vbNewLine & "To complete the removal, please select the " & sAppName _
        & vbNewLine & "in the following dialog and acknowledge the removal", vbInformation + vbOKOnly
        Application.CommandBars(1).FindControl(ID:=943, recursive:=True).Execute
    End If
End Sub

Reply | Quote

Many thanks Jan … I will be trying it this week.

Reply | Quote

Many thanks Jan … I will be trying it this week.

Reply | Quote

For your convenience:

Option Explicit
Dim vReply As Variant
Dim AddInLibPath As String
Dim CurAddInPath As String
Const sAppName As String = "Name Manager"
Const sFilename As String = sAppName & ".xla"
Const sRegKey As String = "FXLNameMgr"    ''' RegKey for settings

Sub Setup()
    vReply = MsgBox("This will install " & sAppName & vbNewLine & _
    "in your default Add-in directory." & vbNewLine & vbNewLine & "Proceed?", _
     vbYesNo, sAppName & " Setup")
    If vReply = vbYes Then
        On Error Resume Next
        Workbooks(sFilename).Close False
        If Application.OperatingSystem Like "*Win*" Then
            CurAddInPath = ThisWorkbook.Path & "" & sFilename
            AddInLibPath = Application.LibraryPath & "" & sFilename
        Else
            CurAddInPath = ThisWorkbook.Path & ":" & sFilename
            AddInLibPath = Application.LibraryPath & sFilename 'syntax differs from Win
        End If
        On Error Resume Next  'This appears to be redundant
        FileCopy CurAddInPath, AddInLibPath
        If Err.Number  0 Then
            SomeThingWrong
            Exit Sub
        End If
        With AddIns.Add(FileName:=AddInLibPath)
        .Installed = True
        End With
    Else
    vReply = MsgBox(prompt:="Install Cancelled", Buttons:=vbOKOnly, Title:=sAppName & " Setup")
    End If
End Sub
Sub SomeThingWrong()
    If Application.OperatingSystem Like "*Win*" Then
        vReply = MsgBox(prompt:="Something went wrong during copying" & vbNewLine _
        & "of the add-in to your add-in directory:" _
        & vbNewLine & vbNewLine & Application.LibraryPath & "" _
        & vbNewLine & vbNewLine & "You can install " & sAppName & " manually by copying the file" _
        & vbNewLine & sFilename & " to this directory yourself and installing the addin" _
        & vbNewLine & "using Tools, Addins from the menu of Excel." _
        & vbNewLine & vbNewLine & "Don't press OK yet, first do the copying from Windows Explorer." _
        & vbNewLine & "It gives you the opportunity to ALT-TAB back to Excel" _
        & vbNewLine & "to read this text.", Buttons:=vbOKOnly, Title:=sAppName & " Setup")
    Else
        vReply = MsgBox(prompt:="Something went wrong during copying" & vbNewLine _
        & "of the add-in to your add-in directory:" _
        & vbNewLine & vbNewLine & Application.LibraryPath _
        & vbNewLine & vbNewLine & "You can install " & sAppName & " manually by copying the file" _
        & vbNewLine & sFilename & " to this directory yourself and installing the addin" _
        & vbNewLine & "using Tools, Addins from the menu of Excel." _
        & vbNewLine & vbNewLine & "Don't press OK yet, first do the copying in the Finder." _
        & vbNewLine & "It gives you the opportunity to Command-TAB back to Excel" _
        & vbNewLine & "to read this text.", Buttons:=vbOKOnly, Title:=sAppName & " Setup")
    End If
End Sub

Sub Uninstall()
    vReply = MsgBox("This will remove the " & sAppName & vbNewLine & _
    "from your system." & vbNewLine & vbNewLine & "Proceed?", vbYesNo, sAppName & " Setup")
    If vReply = vbYes Then
        If Application.OperatingSystem Like "*Win*" Then
            CurAddInPath = ThisWorkbook.Path & "" & sFilename
            AddInLibPath = Application.LibraryPath & "" & sFilename
        Else
            CurAddInPath = ThisWorkbook.Path & ":" & sFilename
            AddInLibPath = Application.LibraryPath & sFilename 'syntax differs from Win
        End If
        On Error Resume Next
        Workbooks(sFilename).Close False
        Kill AddInLibPath
        DeleteSetting sRegKey
        MsgBox " The " & sAppName & " has been removed from your computer." _
        & vbNewLine & "To complete the removal, please select the " & sAppName _
        & vbNewLine & "in the following dialog and acknowledge the removal", vbInformation + vbOKOnly
        Application.CommandBars(1).FindControl(ID:=943, recursive:=True).Execute
    End If
End Sub

Reply | Quote