Using the AddIns Collection

 < Day Day Up > 



Using the AddIns Collection

The AddIns collection contains the set of add-ins available whether or not they have been installed. The entries in this collection correspond to the add-ins listed in the Add-Ins dialog box. Through the AddIns collection, you can browse the add-ins available to Excel, add new add-ins, and install and remove add-ins from your program.

AddIns Collection

The AddIns collection is a typical collection object having the properties shown in Table 11-2.

Table 11-2: Properties

Property

Type

Description

Application

Object

Returns the Application object representing the creator of the add-in.

Count

Long

Returns the total number of AddIn objects in the collection.

Creator

Long

Returns a 32-bit integer containing the binary value XCEL.

Item(Index)

String

Returns the AddIn object associated with Index. If Index is numeric, it refers to the relative position of the object in the collection. If Index is a string, the Item property returns an object reference to the AddIn object whose Name property matches the value in Index.

Parent

Object

Returns the parent object associated with the add-in.

The AddIns collection includes a single method, Add. The Add method takes one or two parameters and returns an object reference to the new AddIn object. The syntax of the Add method as it applies to the AddIns object is

 expression.Add(FileName, CopyFile)

The FileName parameter is required and specifies the full path and file name of the add-in. The CopyFile parameter is optional and applies only when the file is stored on a removable drive (that is, a floppy or a CD-ROM drive). When the second parameter is True, the add-in is copied to a hard disk, whereas False means that the file remains on the removable drive. If the second parameter isn't specified and the file resides on a removable drive, Excel will prompt the user to choose whether the file should be copied or not.

This code fragment shows you how you can use the Add method to include a new add-in workbook in the AddIns collection:

NewAddIn = Application.AddIns.Add("c:\Chapter11.xla") 

AddIn Object

The AddIn object represents a single add-in that's available to Excel. Table 11-3 contains a list of the properties associated with a single add-in.

Table 11-3: AddIn Properties

Property

Type

Description

Application

Object

Returns the Application object representing the creator of the add-in

CLSID

String

Returns the CLSID of the add-in

Creator

Long

Returns a 32-bit integer containing the binary value XCEL

FullName

String

Returns the full path and file name to the workbook containing the add-in

Installed

Boolean

When True, means that the add-in is installed

Name

String

Returns the file name of the add-in

Parent

Object

Returns the parent object associated with the add-in

Path

String

Returns the path to the directory containing the add-in

ProgId

String

Returns the program identifier associated with the object

The Application and Parent properties can simplify referencing the application object that created the add-in and the parent object of the add-in.

The Creator property returns a Long value corresponding to the four characters XCEL. This property is useful when you wish to verify that the add-in was created for Excel.

The ProgId and CLSID properties only apply when using COM or automation-based add-ins. These properties will return an empty string for Excel add-ins.

The Name, Path, and FullName properties contain information about the file name of the add-in's workbook. Name contains only the file name, whereas Path indicates which directory contains the add-in file. As you might expect, FullName combines the Path and Name properties with a directory separator.

The Installed property controls whether that add-in is currently installed into Excel. Setting this property to True installs the add-in and triggers the Workbook_AddinInstall event. Setting this property to False removes the add-in and fires the Workbook_AddinUninstall event.

start sidebar
Auto Routines

In versions of Excel prior to Excel 97, special macros such as Auto_Add and Auto_Remove were called when an add-in was installed or removed. These macros are stored in a module associated with the workbook rather than in the ThisWorkbook object. Although these macros continue to work even in this version of Excel, you should use the corresponding Workbook events.

Other auto routines you might encounter when working with older Excel applications are Auto_Open and Auto_Close, which correspond to Workbook_Open and Workbook_Close, and Auto_Activate and Auto_Deactivate, which correspond to Workbook_Activate and Workbook_Deactivate.

end sidebar

You can see the values stored in the AddIn object by writing a macro similar to the one shown in the following program listing. This macro uses a For Each statement to loop through each AddIn object in the AddIns collection and then insert the value into the current worksheet.

Sub GetAddIns()

Dim a As AddIn
Dim i As Long

i = 1
For Each a In Application.AddIns
i = i + 1
Cells(i, 1) = i - 1
Cells(i, 2) = a.Application.Name
Cells(i, 3) = a.CLSID
Cells(i, 4) = a.Creator
Cells(i, 5) = a.FullName
Cells(i, 6) = a.Installed
Cells(i, 7) = a.Name
Cells(i, 8) = a.Parent.Name
Cells(i, 9) = a.Path
Cells(i, 10) = a.progID

Next a

End Sub

Because the Application and Parent properties return object references that can't be displayed in a cell, the Name property associated with each of those objects is displayed. Figure 11-5 shows the worksheet after running the GetAddIns macro.

click to expand
Figure 11-5: The AddIns collection contains information about each add-in known to Excel.



 < Day Day Up > 



Microsoft Excel 2003 Programming Inside Out
Microsoft Office Excel 2003 Programming Inside Out (Inside Out (Microsoft))
ISBN: 0735619859
EAN: 2147483647
Year: 2006
Pages: 161

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net