Programmatically Save Excel Add-In

Question

I have a worksheet updated occasionally by users that is used to make an Add-In (.XLAM). The Add-In is located on a network share and users link to it. I am looking to enable users to easily update this add-in (ensuring it is marked as read only)

I have seen the article by Ken Puls here on deploying Excel Add-Ins however the .SaveCopyAs method he uses doesn’t seem to be able to accept a file type.

The .SaveAs method does, however when this was tried I gota message saying the file format or extension was invalid, I have tried both with .XLAM and .XLA as below.

DeployPath = "C:\Menu.xlam"
.SaveAs Filename:=DeployPath, ReadOnlyRecommended:=True, FileFormat:=xlOpenXMLAddIn

Any help in this regard would be greatly appreciated.

Answer 1

I think that you need to use (Excel 2003 and earlier)

ThisWorkbook.IsAddin = True
ThisWorkbook.SaveAs "fredyy", xlAddIn

For Excel 2007+ use

ThisWorkbook.SaveAs "fredyy", xlOpenXMLAddIn

Answer 2

This is the solution that worked for me:

Dim strRawName As String
strRawName = Left(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".", -1, vbTextCompare) - 1))
ThisWorkbook.IsAddin = True
ThisWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & strRawName & ".xlam", FileFormat:=xlOpenXMLAddIn