Rerodrig
asked on
Excel VBA: Registering an add-in automatically
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\Madr
Set oXl = CreateObject("Excel.Applic
oXl.Workbooks.Add
Set oXl = CreateObject("Excel.Applic
Set oAddin = oXl.AddIns.Add(strFileName
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!!!
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\Madr
Set oXl = CreateObject("Excel.Applic
oXl.Workbooks.Add
Set oXl = CreateObject("Excel.Applic
Set oAddin = oXl.AddIns.Add(strFileName
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!!!
ASKER
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.AlertBeforeOve
strFileName = Environ$("appdata") & "\Microsoft\AddIns\Madrid_
ThisWorkbook.IsAddin = True
ThisWorkbook.SaveAs strFileName, xlAddIn
Set oXl = CreateObject("Excel.Applic
With oXl.Workbooks.Add
oXl.AddIns.Add(strFileName
.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.
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.AlertBeforeOve
strFileName = Environ$("appdata") & "\Microsoft\AddIns\Madrid_
ThisWorkbook.IsAddin = True
ThisWorkbook.SaveAs strFileName, xlAddIn
Set oXl = CreateObject("Excel.Applic
With oXl.Workbooks.Add
oXl.AddIns.Add(strFileName
.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.
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.AlertBeforeOve
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_
ThisWorkbook.IsAddin = True
Application.DisplayAlerts = False
ThisWorkbook.SaveAs strFileName, xlAddIn
Application.DisplayAlerts = True
AddIns.Add(strFileName).In
End Sub
Matt
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.AlertBeforeOve
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_
ThisWorkbook.IsAddin = True
Application.DisplayAlerts = False
ThisWorkbook.SaveAs strFileName, xlAddIn
Application.DisplayAlerts = True
AddIns.Add(strFileName).In
End Sub
Matt
ASKER
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.Ins
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_
ThisWorkbook.IsAddin = True
Application.DisplayAlerts = False
ThisWorkbook.SaveAs strFileName, xlAddIn
Application.DisplayAlerts = True
Set oXL = CreateObject("Excel.Applic
oXL.Workbooks.Add
AddIns("Madrid_Add-in").In
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").In
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.
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.Ins
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_
ThisWorkbook.IsAddin = True
Application.DisplayAlerts = False
ThisWorkbook.SaveAs strFileName, xlAddIn
Application.DisplayAlerts = True
Set oXL = CreateObject("Excel.Applic
oXL.Workbooks.Add
AddIns("Madrid_Add-in").In
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").In
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.
ASKER
In the first paragraph I meant: AddIns.Add(strFileName).In
not AddIns.Add(strFileName.Ins
not AddIns.Add(strFileName.Ins
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>(this is a technical term)
Thanks for my morning laugh :)
Thanks for my morning laugh :)
ASKER
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.
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.
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_
FileCopy ThisWorkbook.Path & "\Madrid_Add-in.xla", strFileName
AddIns.Add strFileName
AddIns(strFileName).Instal
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
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_
FileCopy ThisWorkbook.Path & "\Madrid_Add-in.xla", strFileName
AddIns.Add strFileName
AddIns(strFileName).Instal
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
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_
Set oXl = CreateObject("Excel.Applic
With oXl.Workbooks.Add
oXl.AddIns.Add(strFileName
.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