I have a purchased database program that exports data to a blank Excel file.
When I go into Tools Addins, the exported Excel file has the right add-in
connections to Analysis Toolpak, but when I go into the VBE editor the
references to ATPVBAEN.xls are missing. I've tried to fix this by putting
the ATPVBAEN.xls reference into my Excel template, but that had no effect on
the export file. I've tried adding the Analysis ToolPak by using VBA code
(although it's already connected from the worksheet), but that didn't work
either. I can create the connection manually by just getting out of Excel
and opening the exported Excel file again. But how do I programmatically
impose VBE references on an Excel file created by the purchased database
program?

You need to set a reference to "Analysis ToolPak - VBA". You can
do this manually, or with code like


Application.AddIns("Analysis ToolPak - VBA").Installed = True
ThisWorkbook.VBProject.References.AddFromFile _
Application.AddIns("Analysis ToolPak - VBA").FullName


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Stratuser" wrote in
message
...
I have a purchased database program that exports data to a blank
Excel file.
When I go into Tools Addins, the exported Excel file has the
right add-in
connections to Analysis Toolpak, but when I go into the VBE
editor the
references to ATPVBAEN.xls are missing. I've tried to fix this
by putting
the ATPVBAEN.xls reference into my Excel template, but that had
no effect on
the export file. I've tried adding the Analysis ToolPak by
using VBA code
(although it's already connected from the worksheet), but that
didn't work
either. I can create the connection manually by just getting
out of Excel
and opening the exported Excel file again. But how do I
programmatically
impose VBE references on an Excel file created by the purchased
database
program?

I've tried this, and it adds the references, and the add-ins are there, but I
still get the message that ATPVBAEN.xla can't be found, even though it's
right there. It's as if the instance of Excel that is launched by the
purchased database program doesn't recognize the references and add-ins, no
matter what I do. What does work is just closing Excel and re-opening the
very same exported file. Then all the references are recognized. Very
mysterious...

"Chip Pearson" wrote:

You need to set a reference to "Analysis ToolPak - VBA". You can
do this manually, or with code like


Application.AddIns("Analysis ToolPak - VBA").Installed = True
ThisWorkbook.VBProject.References.AddFromFile _
Application.AddIns("Analysis ToolPak - VBA").FullName


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Stratuser" wrote in
message
...
I have a purchased database program that exports data to a blank
Excel file.
When I go into Tools Addins, the exported Excel file has the
right add-in
connections to Analysis Toolpak, but when I go into the VBE
editor the
references to ATPVBAEN.xls are missing. I've tried to fix this
by putting
the ATPVBAEN.xls reference into my Excel template, but that had
no effect on
the export file. I've tried adding the Analysis ToolPak by
using VBA code
(although it's already connected from the worksheet), but that
didn't work
either. I can create the connection manually by just getting
out of Excel
and opening the exported Excel file again. But how do I
programmatically
impose VBE references on an Excel file created by the purchased
database
program?