Excel Data Analysis Macro Error

Question

Hello,

I am trying to use the record macro feature using the regression tool in the data analysis toolpack. However, every time I try to run the macro I get the error message:

Run-time error '1004':

Sorry, we couldn't find

C:\Users\myaccountname\Documents\ATPVBAEN.XLAM. Is it possible it was moved, renamed or deleted?

The code itself looks like this:

Sub Macro13()

'

' Macro13 Macro

'

'

     Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("$N$4:$N$18"), _

        ActiveSheet.Range("$O$4:$O$18"), False, False, 95, ActiveSheet.Range( _

        "$Q$3:$AD$33"), False, False, False, False, , True

End Sub

I tried replacing ATPVBAEN.XLAM with 'Analysis Toolpak - VBA' as described in this link:

https://support.microsoft.com/en-us/kb/192642

However it does not work. Does anyone know how to solve this issue?

Answer 1

Worked ok for me using Excel 2016.

Make sure you go to File | Options | Add-ins | 

Under Manage "Excel Add-ins", <Go ...>   make sure you have "Analysis Tookpak - vba"  selected.

Or:  In the vba editor, you can also go to Tools : Reference, and check "atpvbaen.xls"

Then, this code worked for me:  I just used the upper left corner reference for the output.

Sub Demo()

Regress Range("$N$4:$N$18"), Range("$O$4:$O$18"), False, False, 95, Range("$Q$3"), False, False, False, False, , True

End Sub

Answer 2

Hey Dana D_ 

That fixed it. Thank you!

Answer 3

Hi Dana,

I originally had the same error with my code (ATPVBAEN.XLAM!Fourier) and followed the following:

Under Manage "Excel Add-ins", <Go ...>   make sure you have "Analysis Tookpak - vba"  selected.

Or:  In the vba editor, you can also go to Tools : Reference, and check "atpvbaen.xls"

However, now I am getting the error ["Run-time error '1004': Cannot run the macro ". The macro may not be available in this workbook or all macros may be disabled." ]

The macros shouldn't be disabled because I use many other macros in this sheet and it is saved as a macro enabled sheet. 

Any suggestions on what the issue may be ?

Answer 4

The problem is that when you run a macro outside excel the add ins do not load. Here you are trying to run the regression analysis which requires the ATPVBAEN.XLAM add in. In this case you need to load add ins manually. An example in vb-script to manually activate add in and run macro is given below:

Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Open (objExcel.librarypath & "\Analysis\ATPVBAEN.XLAM")

objExcel.RegisterXLL "Analys32.xll"

objExcel.Workbooks("atpvbaen.xlam").RunAutoMacros 1

objExcel.Application.Run "'C:\specify your file path.xlsm'!your macro name"

objExcel.DisplayAlerts = False

objExcel.Application.Quit

Set objExcel = Nothing