Programmatically save an Excel add-in

Question

I have a worksheet, sometimes updated by users who are used to create an add-in (.XLAM). The add-in is located on a network share, and users refer to it. I want users to be able to easily update this add-in (ensuring that it is marked as read-only)

I saw a Ken Puls article here when deploying Excel add-ins, however the .SaveCopyAs method that it uses seems to be unable to accept the file type.

The .SaveAs method, however, when it was checked, I received a message stating that the file format or extension was invalid, I tried both with .XLAM and .XLA, as shown 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 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