PDA

View Full Version : problem with VBA when I open Excel automatically



russellr
05-20-2014, 08:08 AM
I am trying to automate a process that
(1) opens a SPECIFIC excel spreadsheet from a website (this spreadsheet has VBA code in it)
(2) fills that spreadsheet with data from two different websites
(3) then, when the user clicks on a button and the button uses VBA code to runs regressions


Parts (1) and (2) go fine

I am having trouble with part (3), the regression part---it blows up on this line

With Application
.Run "ATPVBAEN.XLAM!Regress",


For some reason, VBA is not "seeing" Analysis ToolPak - VBA.

These commands (below) appear not to be sufficient----what is missing? (do I need to "select" this add in or "register" this add -in? or do these addin commands need to be in a SPECIFIC location??

AddIns("Analysis ToolPak").Installed = False
AddIns("Analysis ToolPak - VBA").Installed = False

AddIns("Analysis ToolPak").Installed = True
AddIns("Analysis ToolPak - VBA").Installed = True


if I do this all manually (go to options--go to add-ins--click on data analysis vba) and THEN click on the button, everything works---but when I open the file automatically--and then CLICK on the button----it blows up :(

any light you can shed would be greatly appreciated

thanks

Russ

Jan Karel Pieterse
05-20-2014, 09:43 AM
Can you set a reference to atpvbaen.xls from the project?

russellr
05-20-2014, 10:07 AM
Jan...thanks for the interest

It is true that atpvbaen.xls is NOT referenced. But when I add-in data analysis -vba manually, everything works fine (without atpvbean.xls being referenced (thru Tools)---however in the project explorer window, I see atpvbaen.xls(ATPVBAEN.XLAM).

When I add in data analysis vba automatically, it crashes

how would you reference atpvbaen.xls from VBA?