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

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

--
Regards,
Tom Ogilvy

"Dave Gruzewski" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
message
> > news:[email protected]...
> > > 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
> >
> >
> >

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in
> message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in
> > message
> > > > news:[email protected]...
> > > > > 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
> > > >
> > > >
> > > >
> >
> >
> >

Dave

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

http://www.*****-blog.com/archives/2...-addin-part-2/

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-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" <[email protected]> wrote in
>> message news:[email protected]...
>>> 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" <[email protected]> wrote
>>>> in message
>>>> news:[email protected]...
>>>>> 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