Generate Histogram

[robbywvut]

My question pertains to both MS Access and MS Excel. I understand that
this is an Excel newsgroup, but I can't seem to find any solutions in
the Access programming newsgroups so I though I would try here for
someone who has used these programs in tandem.

I am using Access to query for information related to project work
activities. Because there are multiple activities for each project, I
want to generate a histogram of the production rates for each activity.


To do this, I am exporting my data from Access to Excel (b/c I find
Excel much better at manipulating data, and I'm trying to stay
consistent with a few other sets of charts I'm generating).

Unfortunately, I'm running into problems with the Analysis Toolpak. My
code gets hungup on the following line:

xlApp.Run "C:\Program Files\Microsoft
Office\OFFICE11\Library\Analysis\ATPVBAEN.XLA!Histogram", ItemRange,
"", False, True, True, True

The error says the macro cannot be found. Does anyone have any
experience with this? Any and all help is much appreciated. Thanks in
advance!

Rob

 

[Leith Ross]

Hello Rob,

The Analysis ToolPak is an Excel Add-In. First you must start the Excel
application and then load the Analysis ToolPak. Then the histogram
function will be available.

*Check if Add-In is Installed: *
Dim A1, A2

Set A1 = xlobj.Addins("Analysis ToolPak")
Set A2 = xlobj.Addins("Analysis ToolPaK-VBA")

If A1.Installed = FALSE Then A1.Installed = TRUE
If A2.Installed = FALSE Then A1.Installed = TRUE

To Uninstall before your quit Excel, set A1 and A2 to FALSE.

Sincerely,
Leith Ross

 

[robbywvut]

Leith,

Thanks for your response and your help. Unfortunately, that's not
doing the trick either. I'm still getting a "Run Time '1004' Error -
ATPVBAEN.XLA could not be found". I am opening Excel with previous
macro, but not making it "visible". Do you think that could be the
problem?
I'm posting my code below if you'd like to take a look at it. Thanks
again for your help!

Sub PlotFrequencyData()
'
Dim A1, A2 As AddIn

ItemNumber = xlSheet.Cells(StartRow, 1).Value
Set ItemRange = xlApp.Range(xlSheet.Cells(StartRow, 5),
xlSheet.Cells(EndRow, 5))

Set A1 = xlApp.AddIns("Analysis ToolPak")
Set A2 = xlApp.AddIns("Analysis ToolPak - VBA")

If A1.Installed = False Then A1.Installed = True
If A2.Installed = False Then A1.Installed = True

xlApp.Run "ATPVBAEN.XLA!Histogram", ItemRange, "", , False, True, True,
True
xlApp.ActiveSheet.ChartObjects("Chart1").Activate
xlApp.ActiveChart.Location where:=xlLocationAsNewSheet,
Name:=ItemNumber

End Sub

 

[jaf]

http://support.microsoft.com/default.aspx?scid=kb;en-us;270844&Product=vbb

 

[robbywvut]

Leith & Jaf,

Thanks for your responses. I found a MS Help and Support article
similar to the one Jaf referenced that I had looked at before but
missed the key step. xlApp.Workbooks.Open
(xlApp.Application.LibraryPath & "\Analysis\ATPVBAEN.XLA").

Everything's working fine now. Thanks again!!! Have a great day!

 

[Ian B]

Almost everything relating to automating histograms in excel points bac
to a technical Microsoft infomation sheet. Its address is
How to create an Excel histogram by using Automation and Analysi
ToolPak
http://support.microsoft.com/default.aspx?scid=kb;en-us;270844

Their article hints at a solution but doesn't produce a practica
answer for me.

I use Excel 2000 To extract numbers from a database. The numbers o
lines of data and the values vary day by day. All other charts an
graphs automatically update themselves as the data changes. I presum
this doesn't happen with the Histogram tool because it is an add-in.
have tried to use the macro record facility using relative addressing t
automate the process with the histogram utility in the Analysis Toolpa
add-in. While the histogram utility window is open, keyboar
keystrokes are not recorded.

I would like a macro that refreshes the histogram insitu on th
original worksheet, ie not adding a new sheet each time. Their worke
example creates a new application every time

 

[Mike Middleton]

Ian B -

Instead of a macro, perhaps you could use a named range for the data, the
array-entered FREQUENCY worksheet function to get a frequency distribution,
and an Excel Column chart type dependent on the frequency distribution. For
a simple solution, you could use the same bin array for all data sets. When
the contents of the named range change, the frequencies and chart are
dynamically updated. And, of course, you could automate some of the process
using VBA.

My free Better Histogram add-in (unprotected VBA, available for download
from www.treeplan.com) includes some of these techniques.

- Mike
www.mikemiddleton.com

 

[Ian B]

Mike

Thanks for such a prompt reply. I know what spreadsheets should b
able to do but can never find help text (in books or online) to solv
what must be a common task. Your FREQUENCY function and array-formul
is a new one for me and solves my problem completely. I'll be usin
this again in other projects