+ Reply to Thread
Results 1 to 7 of 7

Analysis Toolkit does not work when automating EXCEL workbook

  1. #1
    Dave Gruzewski
    Guest

    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

  2. #2
    Tom Ogilvy
    Guest

    Re: 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" <DaveGruzewski@discussions.microsoft.com> wrote in message
    news:11B679AC-12CD-49CE-9460-33CA05B818CE@microsoft.com...
    > 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




  3. #3
    Dave Gruzewski
    Guest

    Re: 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" <DaveGruzewski@discussions.microsoft.com> wrote in message
    > news:11B679AC-12CD-49CE-9460-33CA05B818CE@microsoft.com...
    > > 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

    >
    >
    >


  4. #4
    Tom Ogilvy
    Guest

    Re: 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" <DaveGruzewski@discussions.microsoft.com> wrote in message
    news:D47AB9B1-10E4-49EE-804E-A93E1B24CA26@microsoft.com...
    > 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" <DaveGruzewski@discussions.microsoft.com> wrote in

    message
    > > news:11B679AC-12CD-49CE-9460-33CA05B818CE@microsoft.com...
    > > > 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

    > >
    > >
    > >




  5. #5
    Dave Gruzewski
    Guest

    Re: 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" <DaveGruzewski@discussions.microsoft.com> wrote in message
    > news:D47AB9B1-10E4-49EE-804E-A93E1B24CA26@microsoft.com...
    > > 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" <DaveGruzewski@discussions.microsoft.com> wrote in

    > message
    > > > news:11B679AC-12CD-49CE-9460-33CA05B818CE@microsoft.com...
    > > > > 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
    > > >
    > > >
    > > >

    >
    >
    >


  6. #6
    Tom Ogilvy
    Guest

    Re: 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" <DaveGruzewski@discussions.microsoft.com> wrote in message
    news:F7F8227B-47D2-4A47-8E7E-8F983A522BC1@microsoft.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?
    >
    > "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" <DaveGruzewski@discussions.microsoft.com> wrote in

    message
    > > news:D47AB9B1-10E4-49EE-804E-A93E1B24CA26@microsoft.com...
    > > > 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" <DaveGruzewski@discussions.microsoft.com> wrote in

    > > message
    > > > > news:11B679AC-12CD-49CE-9460-33CA05B818CE@microsoft.com...
    > > > > > 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
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >




  7. #7
    Dick Kusleika
    Guest

    Re: 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.*****-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" <DaveGruzewski@discussions.microsoft.com> wrote in
    >> message news:D47AB9B1-10E4-49EE-804E-A93E1B24CA26@microsoft.com...
    >>> 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" <DaveGruzewski@discussions.microsoft.com> wrote
    >>>> in message
    >>>> news:11B679AC-12CD-49CE-9460-33CA05B818CE@microsoft.com...
    >>>>> 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




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1