Solved

Excel VBA: Registering an add-in automatically

Posted on 2006-11-28
9
3,263 Views
Last Modified: 2011-04-14
I wrote a small add-in for excel and would like the user to just have to double-click on an excel file to get it installed on their computer.  I do two things: 1) I save the excel file as an .xla to the users directory; 2) I register the add-in.  I having trouble with the secong part.  Here's the code I'm using:

Sub Register_Addin()

    Dim oAddin As Object
    Dim oXl As Object
    Dim strU As String
    Dim strFileName As String

    strU = Environ$("Username")
   
    strFileName = "C:\Documents and Settings\" & strU & _
    "\Application Data\Microsoft\AddIns\Madrid_Format_Add-in.xla"

    Set oXl = CreateObject("Excel.Application")
    oXl.Workbooks.Add
    Set oXl = CreateObject("Excel.Application")
    Set oAddin = oXl.AddIns.Add(strFileName, True)
    oAddin.Installed = True
    oXl.Quit
    Set oXl = Nothing

End Sub

When I run this code I get the dreaded:

Run-time error '1004': Unable to get the Add property of the AddIns class

The MSDN for this procedure says that to avoid this error I need to add a workbook before adding the add in, which the procedure they gave me above does.  Any ideas on how I could solve this puppy.  Thank you very much!!!
0
Comment
Question by:Rerodrig
  • 5
  • 4
9 Comments
 
LVL 35

Expert Comment

by:mvidas
Comment Utility
Rerodrig,

First off, you can save a couple lines there by using environ$("appdata") to get the application data path (also in case the user decided to change their default).  

The reason you're getting that error is you're creating two excel application instances.  Give the following a try:

Sub Register_Addin()

    Dim oXl As Object
    Dim strFileName As String
   
    strFileName = Environ$("appdata") & _
        "\Microsoft\AddIns\Madrid_Format_Add-in.xla"

    Set oXl = CreateObject("Excel.Application")
    With oXl.Workbooks.Add
        oXl.AddIns.Add(strFileName).Installed = True
        .Close False 'to close the blank workbook that was opened
    End With
    oXl.Quit
    Set oXl = Nothing

End Sub

You shouldn't need that "True" argument on the addins.add, since your addin is already located in the users addin folder.  Also, you can add and install on one line, as shown above.

Let me know if you have any questions!
Matt
0
 
LVL 2

Author Comment

by:Rerodrig
Comment Utility
Hi Matt,

I made the two corrections you suggested and tried the installed property of the add method just as you have it above but it didn't work.  It doesn't generate any errors and when I open up excel again, although the add-in appears alongside other add-ins in the add-in menu, it is not installed.  Here's the code for your review:

Sub Addin_Install()

    Dim strFileName As String

    Application.AlertBeforeOverwriting = False

    strFileName = Environ$("appdata") & "\Microsoft\AddIns\Madrid_Add-in.xla"
       
    ThisWorkbook.IsAddin = True
   
    ThisWorkbook.SaveAs strFileName, xlAddIn

    Set oXl = CreateObject("Excel.Application")
   
    With oXl.Workbooks.Add
        oXl.AddIns.Add(strFileName).Installed = True
        .Close False 'to close the blank workbook that was opened
    End With
    oXl.Quit
    Set oXl = Nothing
   
End Sub

I also stepped through it and it looked fine from that point of view.  Any ideas?

Rodman.
0
 
LVL 35

Expert Comment

by:mvidas
Comment Utility
Hi Rodman,

I apparently missed the "Excel VBA" right at the beginning of the question, as you're creating another excel object I assumed you were doing this from VB or another app.  Now that I see you use "ThisWorkbook" I can tell it is from excel itself.  This does change everything, for the better.  The MSDN examples usually are for vbscript or vb, not VBA itself, so we don't have to do as much this way.  You won't need to open a workbook to install it (since there already is a workbook--the one you're running it from), and you won't have to create a second instance of excel to do this.
Also, I changed your line
 Application.AlertBeforeOverwriting = False

To just turn off alerts before saving, and turn them back on after.  The line you're using will prevent you from dragging and dropping cells over non-blank cells. Say you have A1:A3 with data in it, and B2 has data but not B1 or B3.  If you highlight A1:A3, then hover the mouse around the dark selection border, you can click/drag those cells anywhere else.  The .AlertBeforeOverwriting option controls whether you want excel to say "Do you want excel to replace the contents of the destination cells?"  The VBA help should have some more information on this, much more useful than MSDN, in my opinion.

But anyways, enough babbling.  Here's what you're looking for, should work exactly as needed:

Sub Addin_Install()
    Dim strFileName As String
    strFileName = Environ$("appdata") & "\Microsoft\AddIns\Madrid_Add-in.xla"
    ThisWorkbook.IsAddin = True
    Application.DisplayAlerts = False
    ThisWorkbook.SaveAs strFileName, xlAddIn
    Application.DisplayAlerts = True
    AddIns.Add(strFileName).Installed = True
End Sub

Matt
0
 
LVL 2

Author Comment

by:Rerodrig
Comment Utility
Matt,

I tried your code but I got an error message: Run-time error '1004': Unable to get the Add property of the AddIns class.  On the debugger it references the AddIns.Add(strFileName.Installed = True line of the code.  It does that again if I run the code a second time.

Now, I tried several variations of the code including modifications to our problem line:

Sub First_Time_Save()

    Dim strFileName As String
    Dim oXL As Object

    strFileName = Environ$("appdata") & "\Microsoft\AddIns\Madrid_Add-in.xla"
    ThisWorkbook.IsAddin = True
   
    Application.DisplayAlerts = False
    ThisWorkbook.SaveAs strFileName, xlAddIn
    Application.DisplayAlerts = True

    Set oXL = CreateObject("Excel.Application")
    oXL.Workbooks.Add
    AddIns("Madrid_Add-in").Installed = True
    oXL.Quit

End sub


The first time around I get the message Run-time error '9': Subscript out of range and the debugger points to the AddIns("Madrid_Add-in").Installed = True.  The second time around it works!  Which makes me think that there is a problem in the list of add-ins that the Addins class uses when trying to install Madrid_Add-in.  So I found a little piece of code that reads the add-ins that are available to be added:

Public Sub Dump_AddIn_Information()

  Dim objAddIn                                          As AddIn
 
  For Each objAddIn In Application.AddIns
      Debug.Print objAddIn.Installed, objAddIn.Title, objAddIn.Name, , objAddIn.FullName
  Next objAddIn
 
End Sub

Lo and behold, the Add-in is not there the first time around and it is the second time.  After reading some MSDN documentation where it was recommended that you open a new workbook before registering the add-in, I´ve come the conclusion that the problem is in the refresh of the Addin class list of available add-ins and that a new workbook was supposed to take care of that by initializing the list.

I have a couple of solutions in mind but they are not elegant and would be retarted (this is a technical term) compared to just asking the user to install the add-in themselves through Tools>Add-ins on the menu bar.  Any ideas on your side?

Regards,

Rodman.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 2

Author Comment

by:Rerodrig
Comment Utility
In the first paragraph I meant: AddIns.Add(strFileName).Installed = True
not AddIns.Add(strFileName.Installed = True, oops
0
 
LVL 35

Accepted Solution

by:
mvidas earned 250 total points
Comment Utility
I figured as much, no biggie, I have many typos like that
Since it is failing on that line, try splitting it into the two lines as you originally had (though the add and install works for me on the same line).  That will at least narrow down if its the Add, the Installed, or the combination of the two that is throwing off the macro:

Sub Addin_Install()
    Dim strFileName As String
    strFileName = Environ$("appdata") & "\Microsoft\AddIns\Madrid_Add-in.xla"
    ThisWorkbook.IsAddin = True
    Application.DisplayAlerts = False
    ThisWorkbook.SaveAs strFileName, xlAddIn
    Application.DisplayAlerts = True
    AddIns.Add strFileName
    AddIns(strFileName).Installed = True
End Sub

If this doesn't work, try adding the   ,True   to the addins.add line.  That shouldn't be necessary unless the addin referenced is not on the local hard drive (per MS), but since the combination of the lines didn't work either, I suppose it couldn't hurt to be safe.

Also, just as an FYI, you got an error on this line     ("Madrid_Add-in").Installed = True     because you didn't have the .xla on there.  The full path may have been the issue, but as long as the add-in was added you shouldn't have to reference the full path.
0
 
LVL 35

Expert Comment

by:mvidas
Comment Utility
>>(this is a technical term)
Thanks for my morning laugh :)
0
 
LVL 2

Author Comment

by:Rerodrig
Comment Utility
Well, tried it and it gave me the now really dreaded Run-time error '1004': Unable to get the Add property of the AddIns class

So ... if it works on your machine it must something with the builds we have here (I tried it on other machines as well) ... I refuse to believe I'm the only one with this problem.

Thanks so much for your attention on this.  I ended up having the user starting the workbook twice, the first time it saves it to the add-ins directory and the second time it succesfully registers it <-this was the 'retarted' solution :)

Thanks again, regards,

Rodman.
0
 
LVL 35

Expert Comment

by:mvidas
Comment Utility
Hmmmm.. what does your VBA help say? I admit, I use excel 2000, which lets some things slide that aren't possible in other versions (usually security related, which I suppose this could be).  My help says it can be done, but you never know if they 'fixed' it for later versions
Truly odd that you'll have to run it twice..
Just a thought, but since you're distributing this file to people to run twice, what if you distributed this as well as an already-saved add-in version of it.  Then assuming they have both files in the same directory, use something like

Sub Addin_Install()
    Dim strFileName As String
    strFileName = Environ$("appdata") & "\Microsoft\AddIns\Madrid_Add-in.xla"
    FileCopy ThisWorkbook.Path & "\Madrid_Add-in.xla", strFileName
    AddIns.Add strFileName
    AddIns(strFileName).Installed = True
    Kill ThisWorkbook.Path & "\Madrid_Add-in.xla"
End Sub

That will copy the distributed .xla file into the AddIns directory, then delete the distributed one too.  I don't know if this will work, but if it does it seems at least a little easier
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now