Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation and Add-Ins
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") |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation and Add-Ins
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. -- Jim Rech Excel MVP |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation and Add-Ins
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 "Forrest" wrote in message ... 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") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automation | Excel Worksheet Functions | |||
Automation?? | Excel Discussion (Misc queries) | |||
automation | New Users to Excel | |||
Automation | Excel Programming | |||
OLE Automation | Excel Programming |