Exporting Data to Excel from Access for Analysis

  • Thread starter Thread starter cjv123
  • Start date Start date
C

cjv123

I'm Trying export data from Access to Excel for data analysis, and the
code I have to open excel is not loading the Analysis ToolPak-VBA
addin. It opens Excel fine, and runs the proper sub, however when I
try to do a regression analysis on the data it give me the can't find
the ATPVBAEN.xla file error, however if I open Excel on my own, and
run the sub, it has no problem. The code I'm using to open Excel from
Access, and run the macro is as follows:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim xls, xlWB As Object
Dim strFile, strMacro As String

strFile = "New_FlowK_011.xls"
strMacro = "Button1_Click"

Set xls = CreateObject("Excel.Application")
Set xlWB = xls.Workbooks.Open("C:\Documents and Settings\ay266c
\Desktop\Coeffs Tool\" & strFile)
xls.AddIns.Add ("C:\Program Files\Microsoft Office\OFFICE11\Library
\Analysis\ATPVBAEN.XLA")
xls.Visible = True



'Run Macro
xls.Run strMacro
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Any suggestions would be greatly appreciated, Thanks!
 
Addins are not loaded when using automation to start Excel.

You have to write code to load the addin(s) you want after opening Excel.
Google groups has a ton of links on how to do it.
 
Back
Top