Install addin through code

[Djblois]

I am trying to create a way for my users to upgrade the progrm through code. I already close the add-in and now I am trying to reopen the newest version. This is what I am trying:

Workbooks.Open ("H:\@Business_Reporting_Today\Current Version\Business Reporting Today.xla")

I guess open doesn't work for add-ins???

[lucas]

I don't think closing it would uninstall it....

[Djblois]

true but it is sufficient to reinstall it. Now I figured out I would have to create a seperate add-in to do this because when I close this add-in the code stops. So I am creating a seperate add-in to close this one and then reopen a new one from the folder I keep the newest version. The only problem I am having is that I added a menu option to the toolbar to upgrade. How do I set the code to call code in a different add-in. Here is the code I am using to add the menu item:

Set MenuItem = menuObject.Controls.Add(Type:=msoControlButton)
MenuItem.OnAction = "AddinUpgrade"
MenuItem.Caption = "Upgrade"

and the new addins name is Upgrade Program. So how do I call "AddinUpgrade" from within "Upgrade Program.xla"

[jtrowbridge]

Does this help you at all? Totally different approach to what youre doing. It'll reinstall a library if its already loaded.


Application.RegisterXLL Filename:="C:\WINDOWS\system32\SomeFileName.xla"

[Djblois]

Thanks I tried that but it says the "The application or DLL isn't a valid windows image. Please check this against your installation diskette."

[tpoynton]

Johnske's article (http://vbaexpress.com/forum/showthread.php?t=10855) might help. I am not suggesting you create an xla from an xls file, but rather that the article provides some code for adding/removing add ins.

by upgrade, do you mean replacing an older version? If so, I think that simply uninstalling the old one and installing the new one would work (in the 'addinupgrade' sub) - as long as the file is in a predictable location.

I suspect 'H' is a network drive? I've read posts here over the years on doing this very thing on a network. In fact, I'm pretty sure that if you save the file on a network drive with updated code with the same name, and users close and re-open excel, it works...I'm not 100% sure, but pretty sure.

[Bob Phillips]

That should work Tim. You could also add versions and the addin could check the version against some version fiel and see if excel should be closed and restarted.

[Djblois]

xld,

I was looking through that article and was thinking about doing an automatic install when someone opens the workbook. Do you have a way to test the version number???

[Bob Phillips]

There are many ways, but the simplest is probably just to have a text file in the same directory as the addin that has the version number in it, and the version number coded into the addin itself. The addin would then poll the text file (read it) periodically to ensure that they are in sync, otherwise it would throw out a message asking the user to quit Excel and restart it.

[tpoynton]

Thanks Bob - that would be my solution (updating the file without changing names)...no code needed! I dont work in what one would call a dynamic office environment though :)

[Bob Phillips]

That is the sort of approach I have used even in a so-called dynamic office environment. The KISS principle is always best.

[Djblois]

I have found some code using VBscript in one of my books to install an add-in. I know I need to type it into a text editor like notepad but how do I run it? all it says is to run the script.

[Bob Phillips]

Try posting it.

[Djblois]

Give me some time then I need to type it in slow to make sure I don't make any errors when typing it into the text editor.

[lucas]

Microsoft has an inovative idea, tool......copy & paste

[Djblois]

I know that but first I have to type it into the text editor before I can copy & paste it. I didn't find it on the Internet, I found it in a book.

'InstallAddin.vbs
'Get the Objects used by this script.
Dim oXL, oAddin, fso, wsh, sicPath, destPath, addin
addin = "\Business Reporting Today.xla"
Set oXL = CreateObject("Excel.Application")
Set fso = CreateObject("Scripting.FileSystemObject")
Set wsh = WScript.CreateObject("WScript.Shell")
'Make Excel visible in case something goes wrong.
oXL.Visible = True
'Create a temporary workbook (required to access add-in)
oXL.Workbooks.Add
'Get the Current Folder.
srcpath = fso.GetFolder(".")
destpath = wsh.Environment("PROCESS")("HOMEDRIVE") & _
wsh.Environment("PROCESS")("HOMEPATH") & _
"\Application Data\Microsoft\Addins"
'Copy the file to a template folder.
fso.CopyFile srcpath & addin, destpath & addin
'Add the add-in to Excel.
Set oAddin = oXL.Addins.Add(destpath & addin, true)
'Mark the add-in as installed so Excel loads it.
oAddin.Installed = True
'Close Excel.
oXL.Quit
Set oXL = Nothing

I figured out how to run it. First I have to save it with the file extension of .xla then I need to double click it. However I want to see if I can make two changes and I know nothing about VBscript and I don't have a book on it (that code was in a VBA book)

First, can I run the VBscript from VBA code? The VBscript file name is InstallAddin.vbs



Second, Can I install the addin with the Excel still open??? Right now if the add-in was installed already and Excel is open It gives me an error that says "The requested operation cannot be performed on a file with a user-mapped section open." If I am able to close the add-in, it should work.

[Djblois]

I have made one change to the VBscript that I found in the same book:
now it will remove the add-in before it adds it. However, it will still only do this if Excel was closed.

'InstallAddin.vbs
'Get the Objects used by this script.
Dim oXL, oAddin, fso, wsh, sicPath, destPath, addin
addin = "\Business Reporting Today.xla"
Set oXL = CreateObject("Excel.Application")
Set fso = CreateObject("Scripting.FileSystemObject")
Set wsh = WScript.CreateObject("WScript.Shell")
'Make Excel visible in case something goes wrong.
'oXL.Visible = True
'Create a temporary workbook (required to access add-in)
oXL.Workbooks.Add
'Mark the add-in as not installed
oXL.Addins("Business Reporting Today").Installed = False
'Get the Current Folder.
srcpath = fso.GetFolder(".")
destpath = wsh.Environment("PROCESS")("HOMEDRIVE") & _
wsh.Environment("PROCESS")("HOMEPATH") & _
"\Application Data\Microsoft\Addins"
'Delete The Previous Version
fso.GetFile(destpath & "\Business Reporting Today.xla").Delete
'Copy the file to a template folder.
fso.CopyFile srcpath & addin, destpath & addin
'Add the add-in to Excel.
Set oAddin = oXL.Addins.Add(destpath & addin, true)
'Mark the add-in as installed so Excel loads it.
oAddin.Installed = True
'Close Excel.
'oXL.Quit
Set oXL = Nothing

I want to correct something I put in my last post. I don't need to save it with a file extension of .vba but a file extension of .vbs .

[Djblois]

Does anyone here know VBscript?? I bought a book and I can't find what I need yet.

[david000]

http://www.jkp-ads.com/articles/AddinsAndSetupFactory.asp

http://www.cpearson.com/excel/InstallingAnXLA.aspx