VBA Overwrite Addin Workbook

Question

So I have done some research but I can't get the feature I am looking for to work.

I am deploying a workbook to a lot of people on a network. I have an add-in on a network drive that all of the workbooks reference to. It works great when running the macros, no problem at all. My issue is updating the macros when people have one of the workbooks open. People leave things open for days sometimes, I know, cause I do the same thing sometimes.

I found this link, with the code below but I can't get it to work.

https://www.excelguru.ca/content.php?152-Deploying-Add-ins-in-a-Network-Environment

The macro saves it as a .xlsm or a macro enabled workbook. It won't save it as a .xla

Sub DeployAddIn()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: To deploy finished/updated add-in to a network
'               location as a read only file
    Dim strAddinDevelopmentPath As String
    Dim strAddinPublicPath As String

    'Set development and public paths
    strAddinDevelopmentPath = ThisWorkbook.Path & Application.PathSeparator
    strAddinPublicPath  = "F:\Addins" & Application.PathSeparator

    'Turn off alert regarding overwriting existing files
    Application.DisplayAlerts = False

    'Save the add-in
    With ThisWorkbook
        'Save to ensure work is okay in case of a crash
        .Save

        'Save read only copy to the network (remove read only property
        'save the file and reapply the read only status)
        On Error Resume Next
        SetAttr strAddinPublicPath & .Name, vbNormal
        On Error Goto 0
        .SaveCopyAs Filename:=strAddinPublicPath  & .Name
        SetAttr strAddinPublicPath & .Name, vbReadOnly
    End With

    'Resume alerts
    Application.DisplayAlerts = True
End Sub

I've tried doing

.SaveAs FileFormat:=xlAddIn

Or

.`SaveAs FileFormat:=18`

Niether of those work. SaveCopyAs doesn't have a fileformat option.

Any suggestions or any other methods that might work?

For reference, this is the setup I have at the moment. fontstuff.com/vba/vbatut08.htm — Tfo

braX braX · Accepted Answer · 2018-01-30T23:39:35

You keep a copy of the XLAM on your local machine that you update, and then when you are ready to deploy it, you copy the XLAM to the server. Everyone else runs off a copy that is on the server (you dont want them to copy it to their local machine, btw) - The key is that when you copy it to the server, you have to set it to read-only in the file properties. You have to do that every time you copy it. Then you can overwrite it at any time.

Comment if you have any questions - I do this every day for work.

VBA Overwrite Addin Workbook

1 Answers