Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I install the Data Analysis Toolkit from Office Basic CD? | Excel Discussion (Misc queries) | |||
need help to install analysis toolkit | Excel Discussion (Misc queries) | |||
How do i get Add Ins/Analysis Toolkit VBA for Excel Mac X version | Excel Discussion (Misc queries) | |||
Automating Excel WorkBook With C# Suppress Excel Message Boxes Possible? | Excel Programming | |||
Problems Loading Analysis ToolPak when automating from Visual Basic | Excel Programming |