Open Excel from Access no *.xla

[jon.ingram]

Here is my issue:

I run an access data bases that cuts up our sales by distributor, and
delivers that information to various excel workbooks prints and emails
to each distributor. This is all buttoned down in a nice macro that
runs in access than opens each excel wkbk and runs a macro in the
excel file. For 2008 we have some distributors that want weekly
updates so I get to designing these reports using weeknum() function
which is part of the Analysis Toolpak add-in and I find out that when
the excel sheet opens from vba that the analysis tool pak is not
recognized (I get #name!).

I tried just recording a macro that selects the add in from excels
menu first and then embedding the recorded code into my code. No dice

I tried opening the .xla similar to how I would open a file in the
vba. No dice

Any one out there have any suggestions? Thanks in advance.

JI

 

[Ken Snell \(MVP\)]

You can get the WeekNum value from the DatePart function in VBA code. Is
that what you seek?

If not, have you enabled the Analysis ToolPak add-in for the EXCEL
application that is on the computer where the ACCESS database is doing the
EXCEL work?