VBA TUTOR NEWSLETTER ~ TUTORIAL FIFTEEN: AUTOMATICALLY INSTALLING AN 'XLA' ADD-IN FILE

>GRSoftware >VBA Tutor >Newsletter Tutorials >Tutorial 14 >Tutorial 16

VBA TUTOR NEWSLETTER ~ TUTORIAL FIFTEEN:

AUTOMATICALLY INSTALLING AN 'XLA' ADD-IN FILE.

Last month we looked at the creation of 'xla' Add-Ins to distribute complete applications, or custom functions and utilities. This month we will look at how to allow the end users to 'automatically' install and uninstall the Add-in by running an 'installation' workbook. The code demonstrates the basic steps in file management in VBA that revolves around building and using the correct file path.

STEP ONE: Using the 'Open' event to ensure the correct 'CurDir'.

As I am sure you realize, MS Office programs can be a real pain to get the correct directory (folder) right.
To ensure that the 'CurDir' function returns the correct directory, we use the 'Open' event to automatically save the file when it is opened.
Private Sub Workbook_Open()
ActiveWorkbook.Save
End Sub
If we do not do this, we will find that the 'CurDir' function can return a path such as 'C:\Windows\Application Data\Excel'. As there is nothing we can do about Windows doing what it wants, using the 'Open' event to save the intallation workbook as it is opened it a simple 'work around' that works.

STEP TWO: Creating the Install Button Code.

The installation of an Add-In has three steps.

Copying of the '.xla' file to the MS Office Library folder.
Adding the Add-in to the 'AddIns Collection'.
Installing the Add-In in the 'AddIns Collection'.

Copying of the '.xla' file to the MS Office Library folder is achieved by using the VBA 'FileCopy' statement.

FileCopy SourceFile, DestinationFile

To build the 'SourceFile' variable, we use the 'CurDir' function to return a string variant representing the current path. To build the 'DestinationFile' variable we use the 'LibraryPath' property to return a string variant representing the path of Library folder. To each of these we add a string containing the name of the '.xla' file to be copied, in this case, "\favDate.xla". Notice that the file name is preceded by a path seperator, which is not included in the path strings returned by CurDir' and 'LibraryPath'.

MyFile = "\favDate.xla"
SourceFile = CurDir + MyFile
DestinationFile = Application.LibraryPath + MyFile

Adding the Add-in to the 'AddIns Collection' is achieved by using the 'AddIns.Add Filename:=DestinationFile' statement.

Installing the Add-In in the 'AddIns Collection' is achieved by using the 'AddIns("Favorite Date Format").Installed = True' statement.

The complete code, listed below, should be fairly easy to follow. The simple error checking is used to inform the user of any problems such as 'File not found!'. For further explanation of the code, use 'Help' in the VB Editor.

Private Sub CommandButton1_Click()
Dim SourceFile, DestinationFile, MyFile
On Error GoTo errMessage
MyFile = "\favDate.xla"
' Define source file name.
SourceFile = CurDir + MyFile
' Define target file name.
DestinationFile = Application.LibraryPath + MyFile
' Copy source to target.
FileCopy SourceFile, DestinationFile
' Add the Add-In to the AddIns Collection
AddIns.Add Filename:=DestinationFile
' Install the Add-In in the AddIns Collection. NOTE: Must use exact description.
AddIns("Favorite Date Format").Installed = True
errMessage:
If Error > "" Then
MsgBox Error
End If
End Sub

STEP THREE: Creating the Uninstall Button Code.

The uninstall of an Add-In has two steps.

Uninstall the Add-in from the 'AddIns Collection' by setting the 'Installed' value to 'False'.
Delete the '.xla' file in the MS Office Library folder using the 'Kill' statement.

Once again the complete code should be fairly easy to follow.

Private Sub CommandButton2_Click()
Dim MyFile
on Error GoTo errMessage
MyFile = "\favDate.xla"
' Uninstall the Add-In in the AddIns Collection
AddIns("Favorite Date Format").Installed = False
' Delete the Add-In file from the Library
Kill Application.LibraryPath + MyFile
errMessage:
If Error > "" Then
MsgBox Error
End If
End Sub

STEP FOUR: Using the 'install' workbook.

Download the demo install file, install_favDate.zip, which includes both the install file and the '.xla' file. The advantage of distributing the 'Add-In' as a zip file is that it ensures that both files end up in the same folder.

NOTE: NOTE: NOTE: NOTE: NOTE: NOTE: NOTE: NOTE:

You should extend upon the basics of working with files, folders and drives covered in this tutorial by looking at an excellent discussion @ MSDN Online Library.

HOPEFULLY, THIS CODE EXAMPLE WILL ADD TO YOUR ABILITY TO BECOME A VBA POWER USER!