VBE References Missing from Exported File

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" <[email protected]> wrote in
message
news:[email protected]...
>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" <[email protected]> wrote in
> message
> news:[email protected]...
> >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?
>
>
>