Solved: XLAM file replacement

[mikke3141]

Hello,

I have created a xlam file containing different useful macros at our office. I sometimes send a new updated file to the macro users and I would like the file attached to the mail automatically to repleace the old one when the xlam file has been opened. The issues is that when the user opens the file with excel, the old xlam is automatically opened.

The file that should be replaces is C:\Documents and Settings\z412341\Application Data\Microsoft\Excel\XLSTART\work_macros.xlam

We are not allowed to run any own exe files on our computers.



Sub replace_file()
Dim xlobj As Object
Set xlobj = CreateObject("Scripting.FileSystemObject")
abu = Application.VBE.ActiveVBProject.Filename
xlobj.CopyFile abu, "C:\Documents and Settings\z412341\Application Data\Microsoft\Excel\XLSTART\work_macros.xlam"
Set xlobj = Nothing
End Sub

I have tried the code above, but it does not work as the macrofile is already in use when excel is opened.

Thank you for your help.

[lucas]

I'm not familiar with xlam files and assume that it's an addin file for Excel and would show in a list of addins.

You would have to disable it in excel via your code and/or close it before you could overwrite it.

[mikke3141]

How can I disable/close the xlam(xla) file with the code?

[lucas]

I don't have 2007 so I'm at a loss but this blog article seems to address it. They had trouble even after uninstalling the addin because it remains checked in the dialog but you just want to uninstall it and overwrite it so it may be just what you need:

Microsoft.Office.Interop.Excel.AddIn addin = Globals.ThisAddIn.Application.AddIns.Add(fullPathToAddin, false);
addin.Installed = true;
change installed = true to false.

http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/53a8da13-520e-41f7-accb-74211cda7299

[mikke3141]

Hello,

I found some code that will solve the problem.



AddIns("Makros").Installed = False
AddIns.Add Filename:=ThisWorkbook.Path & "\Custom Functions.xla"
AddIns("Makros").Installed = True

Thank you for the help :cool:

[Bob Phillips]

You can do it manually,

Office Button>Excel Options, select Addins in the left hand pane, click the Go button and then uninstall.

[Jan Karel Pieterse]

Check out this article on my site:

www.jkp-ads.com/articles/updateanaddin.asp (http://www.jkp-ads.com/articles/updateanaddin.asp)

Alternatively, make sure your add-in file is located on a server folder everyone has access to. Set it's read-only property to True. When installing the addin, make sure they do NOT copy the file to their local addins folder.
This way, all users use the same add-in file from the same location and because you set it to read-only, you can simply replace the add-in file anytime you like. Next time each user re-opens Excel, they get the new copy of your add-in.

[mikke3141]

Hello,

Attached my macro that worked for me. Thank you for your help.



Private Sub Workbook_Open()
'Checks if the file is an attachment to a email
If InStr(1, ThisWorkbook.FullName, "Temporary") > 0 Then
update_macro
End If
End Sub



Sub update_macro()

Set xlobj = CreateObject("Scripting.FileSystemObject")
UserName = Environ("USERNAME")

path_addin = "C:\Documents and Settings\" & UserName & "\Application Data\Microsoft\AddIns\"
addin_file = Application.VBE.ActiveVBProject.Filename

AddIns("Makros").Installed = False
On Error Resume Next
Kill (path_addin & "MyMacro.xlam")
On Error GoTo 0
xlobj.CopyFile addin_file, path_addin & "MyMacro.xlam"
AddIns("Makros").Installed = True
MsgBox "New macro installed in " & Polku
ActiveWorkbook.Saved = True
Application.Quit
End Sub