Analysis Toolkit does not work when automating EXCEL workbook

[Guest]

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

 

[Tom Ogilvy]

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

 

[Guest]

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]

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.

 

[Guest]

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]

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

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?

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



wits
end stop
the packs
are

 

[Dick Kusleika]

Dave

You can take the coward's way out and not use the ATP (that's what I'd do)

http://www.dicks-blog.com/archives/2004/12/19/replacing-the-analysis-toolpak-addin-part-2/

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

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?

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.