Analysis Toolkit does not work when automating EXCEL workbook
 

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

Analysis Toolkit does not work when automating EXCEL workbook
 

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

Analysis Toolkit does not work when automating EXCEL workbook
 

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

Analysis Toolkit does not work when automating EXCEL workbook
 

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

Analysis Toolkit does not work when automating EXCEL workbook
 

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

Analysis Toolkit does not work when automating EXCEL workbook
 

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

Analysis Toolkit does not work when automating EXCEL workbook
 

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/2...-addin-part-2/

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

Dave Gruzewski wrote:
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