7

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.

brettdj
  • 54,857
  • 16
  • 114
  • 177
Tom
  • 71
  • 1
  • 2
  • 1
    I've found that sometimes I can have problems when not using the number values for FileFormat. Try using `FileFormat:=55` instead of `FileFormat:=xlOpenXMLAddIn` – PermaNoob Dec 27 '13 at 16:33
  • Not sure what are you trying to achieve... Are you trying to save currently opened workbook (not addin) as an addin or are you trying to save addin as addin? I think you're talking about template instead addin. – Maciej Los Dec 29 '13 at 21:17

2 Answers2

6

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
Yishai
  • 90,445
  • 31
  • 189
  • 263
Charles Williams
  • 23,121
  • 5
  • 38
  • 38
  • When I try this, saving as a 2007 Macro i get the same error however when saving as a .XLA, Excel says that the file has an incorrect extension and opens the file as a regular workbook not an add-in – Tom Dec 22 '10 at 21:08
  • Thanks for your help. Running this (even in the immediate window) the file is created, when opened however Excel throws an error claiming an invalid extension or corrupted file. I tried renaming the workbook to a .ZIP file which normally works with other office 2007/10 files but also results in a corrupted file error. This worked to an extent when saving as a .XLA; the code module was there, but as it relies on a ribbon menu none actually worked. Saving from the menu works fine. – Tom Dec 23 '10 at 02:13
4

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
Yishai
  • 90,445
  • 31
  • 189
  • 263