Automation and Add-Ins

[Guest]

When opening Excel with the following code snippet (located in an Access module) and dropping the XIRR function (an Excel add-in in the Analysis Tool pak) into a cell; the cell shows "#NAME?". The current version of Excel does not recognize this add-in function. But, if I open Excel and then run the code the function works! Do I need to explicitly reference Excel's ad-in when opening Excel in automation? Thanks for thoughts or solutions.

Forrest

'Open or create an instance of Excel and add a workbook
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application") 'Activate current Excel object
If Err.Number <> 0 Then
Err.Clear ' Clear Err object in case error occurred.
Set xlApp = CreateObject("Excel.Application") 'Create an instance of Excel
End If
Set xlWb = xlApp.Workbooks.Add
Set xlWs = xlWb.Worksheets("Sheet1")

 

[Jim Rech]

When you open Excel via automation no auto-loading files are opened,
including add-ins. You could enhance your script to open the add-in though.
You can use Application.LibraryPath to find the path.

 

[Tom Ogilvy]

When you open excel from an external program, addins are not loaded. You
need to load them specifically with your code.

You can use
the addin object to load them.

This extract from help seems to spell out the
options pretty clearly:
=============>
The Add method adds an add-in to the list of available add-ins but doesn't
install the add-in. Set the Installed property of the add-in to True to
install the add-in. To install an add-in that doesn't appear in the list of
available add-ins, you must first use the Add method and then set the
Installed property. This can be done in a single step, as shown in the
following example (note that you use the name of the add-in, not its title,
with the Add method).

AddIns.Add("generic.xll").Installed = True

Use Workbooks(index) where index is the add-in filename (not title) to
return a reference to the workbook corresponding to a loaded add-in. You
must use the file name because loaded add-ins don't normally appear in the
Workbooks collection. This example sets the wb variable to the workbook for
Myaddin.xla.

Set wb = Workbooks("myaddin.xla")

The following example sets the wb variable to the workbook for the Analysis
Toolpak add-in.

Set wb = Workbooks(AddIns("analysis toolpak").Name)

If the Installed property returns True, but calls to functions in the add-in
still fail, the add-in may not actually be loaded. This is because the Addin
object represents the existence and installed state of the add-in but
doesn't represent the actual contents of the add-in workbook.To guarantee
that an installed add-in is loaded, you should open the add-in workbook. The
following example opens the workbook for the add-in named "My Addin" if the
add-in isn't already present in the Workbooks collection.

On Error Resume Next ' turn off error checking
Set wbMyAddin = Workbooks(Addins("My Addin").Name)
lastError = Err
On Error Goto 0 ' restore error checking
If lastError <> 0 Then
' the add-in workbook isn't currently open. Manually open it.
Set wbMyAddin = Workbooks.Open(Addins("My Addin").FullName)
End If

==========>

--
Regards,
Tom Ogilvy

When opening Excel with the following code snippet (located in an Access

module) and dropping the XIRR function (an Excel add-in in the Analysis Tool
pak) into a cell; the cell shows "#NAME?". The current version of Excel does
not recognize this add-in function. But, if I open Excel and then run the
code the function works! Do I need to explicitly reference Excel's ad-in
when opening Excel in automation? Thanks for thoughts or solutions.

 

[Guest]

Thank-you gentlemen for your research. I added two lines of code: one to close the ad-in, and another to open or install the add-in. It works.