From MS Word,
this version worked consistently:

Sub TesterAAAA()
Dim xlApp As New Excel.Application
Dim xlbk As Excel.Workbook
Dim xlbk1 As Excel.Workbook
Dim xlbk2 As Excel.Workbook
Dim LTI As Excel.Worksheet
Dim FileString As String
FileString = "C:\Data7\AAAA.xls"
xlApp.Application.Visible = True
Set xlbk = xlApp.Workbooks.Open(FileString) 'Focus is now on the workbook
Set LTI = xlbk.Worksheets(1)
sStr = "c:\Program Files\Microsoft Office\" & _
"Office\Library\Analysis\ATPVBAEN.XLA"
xlApp.AddIns.Add sStr
' xlApp.Workbooks.Open sStr
' Debug.Print xlbk1.Name
sStr1 = "c:\Program Files\Microsoft Office\" & _
"Office\Library\Analysis\FUNCRES.XLA"
' xlApp.Workbooks.Open sStr1
' Debug.Print xlbk2.Name
' xlApp.AddIns.Add sStr1
xlApp.AddIns("analysis toolpak - vba").Installed = False
xlApp.AddIns("analysis toolpak").Installed = False

xlApp.AddIns("analysis toolpak - vba").Installed = True
xlApp.AddIns("analysis toolpak").Installed = True

'- -
xlApp.GoTo Reference:=LTI.Range("TermName")
LTI.Range("ExpectedFirstPrincipalDate").Calculate
'LTI.Range("ExpectedMatDate").Calculate

LTI.Range("ExpectedMatDate").Value = ""
Debug.Print "ExpectedMatDate= " & LTI.Range("ExpectedMatDate").Text
LTI.Range("ExpectedMatDate").Formula = "=EDATE(ExpectedNextCoupon, 12)"
LTI.Range("ExpectedFirstPrincipalDate").Calculate
Debug.Print "ExpectedMatDate= " & LTI.Range("ExpectedMatDate").Text
Set LTI = Nothing
'Set xlbk2 = Nothing
'Set xlbk1 = Nothing
Set xlbk = Nothing
Set xlApp = Nothing
End Sub

--
Regards,
Tom Ogilvy

"Dave Gruzewski" wrote in message
...
This will not work for me.

Here is what my code looks like in the excel module:

sStr = "c:\Program Files\Microsoft Office\" & _
"Office\Library\Analysis\ATPVBAEN.XLA"
Workbooks.Open sStr

sStr = "c:\Program Files\Microsoft Office\" & _
"Office\Library\Analysis\FUNCRES.XLA"
Workbooks.Open sStr

'- -
Application.GoTo Reference:=LTI.Range("TermName")
LTI.Range("ExpectedFirstPrincipalDate").Calculate
'LTI.Range("ExpectedMatDate").Calculate

LTI.Range("ExpectedMatDate").Value = ""
MsgBox "ExpectedMatDate= " & LTI.Range("ExpectedMatDate")
'LTI.Range("ExpectedMatDate").Value =
[ATPVBAEN.XLA].EDATE(LTI.Range("ExpectedNextCoupon"), 12)
LTI.Range("ExpectedMatDate").Formula = "=EDATE(ExpectedNextCoupon,
12)"
LTI.Range("ExpectedFirstPrincipalDate").Calculate
MsgBox "ExpectedMatDate= " & LTI.Range("ExpectedMatDate")


It dies on the last message box function with a type mismatch- I tried
using
the immediate window also - it will not resolve the formula

any other ideas?

"Tom Ogilvy" wrote:

Just open it like a normal workbook

sStr = c:\Program Files\Microsoft Office\" & _
"Office\Library\Analysis\ATPVBAEN.XLA"
workbooks.Open sStr

You shouldn't need to do anything with the xll.

for the worksheet formulas that use the analysis toolpak you need to
load
funcres.xla
Again, open it like a regular workbook.

---
if you want to pursue the Addins.Add approach, I believe you need to
have a
workbook opened before you can actually add or load the addin.

If you manually open excel and close all workbooks, then select the
tools
menu, you see the Addins option is disabled - same thing when you do it
with
automation.

--
Regards,
Tom Ogilvy



"Dave Gruzewski" wrote in
message
...
Sorry - about the other postings , I kept getting an error when I
posted -
and I though I had to reports.

Thanks for the response, Thats what I thought - but I was unable to do
this.
here is the code I used(from VB.net):

pappXL.AddIns.Add("C:\Program Files\Microsoft
Office\Office\Library\Analysis\Analys32.xll")

pappXL.AddIns.Add("c:\Program Files\Microsoft
Office\Office\Library\Analysis\ATPVBAEN.XLA")

It did not work. I also tried using this code in the EXCEL module:

Application.AddIns.Add ("C:\Program Files\Microsoft
Office\Office\Library\Analysis\Analys32.xll")

Application.AddIns.Add ("c:\Program Files\Microsoft
Office\Office\Library\Analysis\ATPVBAEN.XLA")

It also did nothing. Where? and How? do I add the addins to my code?

Thanks



"Tom Ogilvy" wrote:

When you automate excel, addins are not loaded - you need to load
the
analysis toolpak yourself with your code.

--
Regards,
Tom Ogilvy

"Dave Gruzewski" wrote in
message
...
Hi,

I am trying to automate and excel workbook from vb.net code
calling a module with this command(vb.net):

pappXL.Run("ImportFromCPMonitoring")

This works totally fine - but when it gets to anywhere in the
spreadsheet
where EDATE is used in a formula - it fails. I am totally at my
wits
end
with
this. Is there any reason why this function would not work? - I
stop
the
code
while it is running and check the tools-addins and both analysis
packs
are
checked.

Please help

Thanks