Automatically update the Histogram


Hi there

I have the following problem...

I have developed an application in Microsoft Access 97. I then make use of a TransferSpreadsheet macro to transfer the data from MS Access 97 to MS Excel 97 to produce a histogram.

I use the data imported from MS Access to get the Bin and Frequency (using the Data Analysis menu option of MS Excel)to be plotted on the Histogram.

I have successfully managed to get the desired result except that when I update the data in MS Access, the Bin and Frequency values are not automatically refreshed and neither the Histogram to reflect the updated data.
The data in MS Excel is updated after the TransferSpreadsheet macro is executed again.

Is there anyway of automatically updating the Bin and Frequency values and the Histogram without having to go to the Data Analysis menu option of MS Excel?

I hope this explanation makes sense. Don't hesitate asking for further clarification of the problem.

Thanks and regards.
LAPAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vboukharCommented:
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?
0
LAPAuthor Commented:

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
0
vboukharCommented:
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.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

LAPAuthor Commented:

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
0
vboukharCommented:
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.
0
LAPAuthor Commented:

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.
0
vboukharCommented:
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!

0
LAPAuthor Commented:
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
0
vboukharCommented:
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
0
LAPAuthor Commented:

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
0
LAPAuthor Commented:
Adjusted points to 100
0
vboukharCommented:
LAP,
it's me again
It seems your problem is common bag of MS Excel - look at text from MS KB:
 
PSS ID Number: Q171027
Article last modified on 01-02-1999
 
WINDOWS:97
 
WINDOWS
 

======================================================================
---------------------------------------------------------------------
The information in this article applies to:
 
 - Microsoft Excel 97 for Windows
---------------------------------------------------------------------
 
SYMPTOMS
========
 
If you run a macro that uses a function from the Analysis ToolPak add-in,
you receive the following error message:
 
   Run-time error '1004':
   'ATPVBAEN.XLA' could not be found. Check the spelling of the file
   name, and verify that the file location is correct.
 
CAUSE
=====
 
This problem occurs if you run a Microsoft Visual Basic for Applications
macro that uses a function from the Analysis ToolPak add-in and the
Analysis ToolPak VBA add-in is not loaded.
 
WORKAROUND
==========
 
If you use recorded macro code that uses a function in the Analysis
ToolPak, select the Analysis ToolPak - VBA add-in before you run the macro
code.
 
MORE INFORMATION
================
 
In earlier versions of Microsoft Excel, recorded macro code that uses a
function in the Analysis ToolPak uses the Microsoft Excel 4.0
ExecuteExcel4Macro macro command. In Microsoft Excel 97, the recorded code
uses the Application.Run "Atpvbaen.xla!<function>" command. In Microsoft
Excel 97, the Analysis ToolPak - VBA add-in must be loaded to run the
recorded macro.


..........
So - you can remove strings
tStr = Application.Path & "\Library\Analysis\ATPVBAEN.XLA"
Workbooks.Open tStr
(it worked! though maybe I had checked analysis toolpack in Add-ins?)
and before run code check, if this add-ins is checked...
:-(

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LAPAuthor Commented:
This works perfectly!

Thanks for your time and assistance.

100 pts on their way to you.

Regards,
LAP
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.