Automatically update the Histogram

You can update your data (right-click on data and choose "Refresh data"). But (IMHO) your Histogram, Bin and Frequency values are results of Add-ins "Analysis tool pack" macros, and you have to re-run it again to get new values.
Only way - use VBA and create your macros (delete sheet with old Histogram, Bin and Frequency and create new one), that you can run every time when you update your Access, or insert this code in worksheet OnChange event to run it automatically.
Is it convenient way for your?

Hi vboukhar

That sounds like a convenient way of doing it. Only problem is that I'm not that clued up with VBA. I take it I'll have to code this in MS Excel.

Would you be able to provide me with this code. I'll really appreciate it.

I'll increase the points to 100 if it works!

Thanks so far. In the mean time I'll try to do it.

Regards,
LAP

LAP,
I mean VBA (Visual Basic for Applicatiion), that is a built-in feature of Excel. You can run Macrorecorder (Service|Macros|Begin record) and make manually full cycle - refresh data, delete sheet with old histogram, run Data Analysis (that creates new sheet), format this sheet for your needs and stop macrorecorder. Then you'll press Alt-F11 (skip to VBA editor) and you'll be able to see recorded VBA code (and edit it, if you want). Now every time, when you need to update your histogram, just run this macros (Alt-F8 or Service|Macros|Run).
Hope it helps.

Hi vboukhar

That sounds like a convenient way of doing it. Only problem is that I'm not that clued up with VBA. I take it I'll have to code this in MS Excel.

Would you be able to provide me with this code. I'll really appreciate it.

I'll increase the points to 100 if it works!

Thanks so far. In the mean time I'll try to do it.

Regards,
LAP

LAP,
VBA code very depends on your data. Look at my sample - I ran Data|Get external data| and got some data in range A1:A2105. In range C1:F1 I placed data for 'pockets'. Then I ran macrorecorder and refresh table, then ran Analysis-Histogram and ask to create histogram on new sheet ("Hist"), then turn recorder off. After that I skip to VBA editor (Alt-F11) and edit code (to avoid error and confirmation messages when I delete old sheet and create new). You have to change ranges in !Histogram call at least.
Sub RunUpdate()
    Dim c As Worksheet
' all above is result of macrorecording
    Range("A1").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
' below is my code
    Application.DisplayAlerts = False
    For Each c In ThisWorkbook.Worksheets
    If StrComp(c.Name, "Hist") = 0 Then
      c.Delete
      Exit For
    End If
    Next
    Application.DisplayAlerts = True
' end of my code
    Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$A$2:$B$5215") _
        , "Hist", ActiveSheet.Range("$C$1:$G$1"), False, False, True, False
End Sub

If you'll get error ATPVBAEN.XLA not found - simplest way - open it as ordinarly XLS file (it's hidden, so you don't see anything).
hope it helps.

I'm trying to automate most of the "process" in MS Excel to minimize the numbers of clicks the user has to perform.

The user will have to jump from the MS Access application to the MS Excel spreadsheet. Is there no way that the ATPVBAEN.XLA file can be automatically opened without giving an error msg? Do I have to include opening this file when I record the macro?

Am I correct to understand that your code above deletes and creates the histogram on the Worksheet "Hist".

Thanks so far.

Yes, you understood my code correctly. To automatically open file with Analysis library - add in VBA code (after Dim statement)
  tStr = Application.Path & "\LIBRARY\ANALYSIS\ATPVBAEN.XLA"
  Workbooks.Open tStr

Check it!

vboukher,

I've adjusted the code with what you have provided as follows:

Sub GenerateHistogram()

Dim c As Worksheet
tStr = Application.Path & "\Library\Analysis\ATPVBAEN.XLA"

Workbooks.Open tStr

Application.DisplayAlerts = False

For Each c In ThisWorkbook.Worksheets
    If StrComp(c.Name, "Hist") = 0 Then
       c.Delete
    Exit For
    End If
Next

Application.DisplayAlerts = True
   
     Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$D$2:$O$32"), _
        "Hist", ActiveSheet.Range("$A$34:$A$48"), False, False, True, False
End Sub

I get the following error msg:
Run-time error '1004'
'atpvbaen.xls' could not be found.

Why is it looking for the *.xls file?

Also, the very first time I generate the Histogram (remember, I'm also running the macro recorder at the same time)it generates a Worksheet named "Sheet1". How do I automatically name this Worksheet "Hist" without having to manually rename the Worksheet to "Hist" after I've stopped macro recorder? Or is it not necessary to rename the Worksheet that is, "Sheet 1"?

Regards,
LAP

1. Check in VBA editor, if ATPVBAEN.XLA really open after macros ran (in VBA project you'll see this file - by the way with the name ATPVBAEN.XLS - it's MS way!) - maybe you have it installed at another folder (strange).
2. In Histogram wizard you can rename sheet and my function call use this way. You can press F2 in VBA editor and look at all object in ATPVBAEN.XLA (if it's loaded)
3. You can try to add link to ATPVBAEN.XLA from project too: press Service, Links, then Browse and choose ATPVBAEN.XLA in proper folder. (it could help, though I wander, why simple load don't work properly.
Good luck!
P.S. It works fine on my PC!
P.P.S. I doubt in my presence here for further 5 days or more.. :-( Sorry

The atpvbaen.xla file is in the correct location on my PC. I also see the .xls file in the VBA editor.

Eveything appears to be correct. When I run the macro, I get the above-mentioned err msg. When I click on the Debug button it points to the following line of code:

Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$D$2:$O$32"), _
        "Hist", ActiveSheet.Range("$A$34:$A$48"), False, False, True, False

Could this give you a clue as to where the error is?

What I also did was click the checkbox next to atpvbaen.xls from the Tools Reference menu option from the VBA editor. Now when I run the macro The following err msg is displayed:

"The workbook is currently referenced by another workbook and cannot be closed."
I guess that wasn't the correct thing to do.

As a last resort, could I possibly e-mail the MS Excel file to you?

LAP

Adjusted points to 100

This works perfectly!

Thanks for your time and assistance.

100 pts on their way to you.

Regards,
LAP