AddIns.Add method

[RobBroekhuis]

Hi all,
I'm trying to write and execute some code for installing the tool I'm writing as an AddIn:

Sub InstallAsAddIn()
  Dim s As String
  If ThisWorkbook.IsAddin Then Exit Sub
  s = Application.StartupPath
  s = Left(s, Len(s) - 13) + "addins\RC1 toolbox.xla"
  ActiveWorkbook.SaveAs s, xlAddIn
  Application.AddIns.Add s
  Application.AddIns("RC1 toolbox").Installed = True
End Sub

The code executes fine up to the addins.add statement (including a prompt whether to overwrite the existing .xla file, so I know that string variable s contains the proper filename), but then gives me a non-descript error (add method of addins class failed). What am I doing wrong?
Thanks for your help!
Rob

 

[nath]

Are you sure the addin has not been loaded already?

Nath

 

[RobBroekhuis]

I've definitely tested the code while the addin wasn't already in the AddIn manager's table. The folder I'm writing to is the folder that Excel looks into to find AddIns upon startup, so if I leave Excel and come back again, the add-in will be there, ready to install. But I want to install right then, without having to instruct the user to leave Excel, return, and check the appropriate box in Tools->AddIns.
If you mean "was the addin loaded into memory?", the answer is yes - it has to be, since the code that is executing is in the addin itself (hence the use of the ThisWorkbook object).
I've done a good bit more experimenting, but am still puzzled...
Rob

 

[nath]

Hi, I tested your code and the value of s before it adds the addin is:

"C:\Program Files\Microsoft Office\Oaddins\RC1 toolbox.xla"

so that you have the folder "Oaddins" instead of "addins". your Code should be:

[\code]
s = Left(s, Len(s) - 14) + "addins\RC1 toolbox.xla"

14 instead of 13.

If this was the exact code you used it might explain it.

Nath

 

[RobBroekhuis]

Nath,
Thanks for checking that out. Unfortunately, that's not the problem. In my company's Win2000 environment, len-13 is proper, and results in the correct full pathname (as evidenced by the fact that the SaveAs method works fine).
Rob

 

[nath]

Rob, I have found the following that confirms your code is not supposed to work. Unfortunately my first attempts to use their alternative are not more successful!

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q114436

(system doesn't take the end of the URL but you can type it in yourself. If it doesn't work, go to google and search for makeaddin vba

Let me know if it works!

Nath

 

[RobBroekhuis]

Nath - thanks for the research. I haven't tried it, because I finally figured out a way to make it work, using the following code:

Sub InstallAsAddIn()
  Dim s As String, oldname As String
  If ThisWorkbook.IsAddin Then Exit Sub
  ThisWorkbook.IsAddin = True
  s = Application.StartupPath
  s = Left(s, Len(s) - 13) + "addins\RC1 toolbox.xla"
'if necessary, remove older version of toolbox
  On Error Resume Next
  AddIns("RC1 toolbox").Installed = False
  Kill s
  On Error GoTo 0
  oldname = ThisWorkbook.FullName
  ThisWorkbook.SaveAs s, xlAddIn
  ThisWorkbook.IsAddin = False
  Application.DisplayAlerts = False
  ThisWorkbook.SaveAs oldname, xlWorkbookNormal
  Application.DisplayAlerts = True
  Application.AddIns.Add s
  AddIns("RC1 toolbox").Installed = True
  SkipDeleteMenus = True
  ThisWorkbook.Close
End Sub

The problem with the original code was that the addins.add method balked at using an already open file. So I had to first save the current worksheet to the appropriate location, with the IsAddIn flag set, and then effectively close that copy by resaving in the original location (after resetting the IsAddIn flag). Seems like a lot of work to accomplish something simple, but it's functional. I end by closing the .xls file, but I first have to set a flag which tells my Workbook_BeforeClose sub to not delete the menus created by my application (since the add-in is now loaded and will take over menu management).
Rob