Solved

MS Chart Control Probelem - Any Alternative to this control?

Posted on 2002-06-10
8
235 Views
Last Modified: 2013-12-26
Hello,

I am using Office 2000 and have developed a word application using MS Word and VB Script to create customized reports (about 1,800 reports).  The 6 graphs in the report are charted using data from MS Access.  After about 400-500 iteration the MS Chart control konks out. It has nothing to do with the data becasue if I close word and reopen it and begin at the iteration where it left then everything works.  The time to konk out seems to be random but occurs anywhere between 400-500 iteration increments.

Any idea of what is going on?  Is their anyway in vb script to kill the msgraph.exe application and refresh it rather then having to close word?

Also, what is the best chart control on the market that is an alternative to the mschart conrol?
0
Comment
Question by:dmontgom
8 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7068780
>>what is the best chart control on the market that is an alternative to the mschart conrol

www.chartfx.com


>>The time to konk out seems to be random but occurs anywhere between 400-500 iteration increments.

Are there any objects that you left open. You should always set your objects to nothing before they get out of scope when you loop.
0
 

Author Comment

by:dmontgom
ID: 7068796
Everything using a set statment has been set to nothing.
0
 

Author Comment

by:dmontgom
ID: 7068808
Also, after the Chart program konks out, updating of tables and bookmarks still works.  The program will still run but only if I use on error resume next.  Usuall I get a type mismatch error on the chart.
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7068815
do you have illegal values (like NULLs) ?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 18

Expert Comment

by:mdougan
ID: 7070239
From the description, it is most likely as others have suggested, you are running into a resource problem.  We would need to look at your code to ensure that your clean-up procedures are actually releasing the resources or not.

Even if you are coding things perfectly, there are often bugs in the underlying office components that do not let the resources be deallocated.  There might be a way to stop and start the chart program to force it to release it's resources without any noticable difference to the users.  However, before we could say for sure, we'd have to see a sample of your code.
0
 

Author Comment

by:dmontgom
ID: 7071126
Here is a sample of my code:


For i = 1 To TotalPhys
    'On Error Resume Next
       
        Set objNewDoc = Documents.Add("D:\My Documents\Pharmacy Utilization Report\pur\Report Template\pur.dot")
                 
        Set objBookmark = objNewDoc.Bookmarks("period_1")
        Set objRange = objBookmark.Range
        objRange.Text = PeriodText
        Set objBookmark = Nothing
        Set objRange = Nothing
           
        Set objBookmark = objNewDoc.Bookmarks("period_2")
        Set objRange = objBookmark.Range
        objRange.Text = PeriodText
        Set objBookmark = Nothing
        Set objRange = Nothing
       
        Set objBookmark = objNewDoc.Bookmarks("period_3")
        Set objRange = objBookmark.Range
        objRange.Text = PeriodText
        Set objBookmark = Nothing
        Set objRange = Nothing
           
        sql = "select distinct keyroot, pname, paddr1, paddr2, pcity, pstate, pzip from latest_prvaddr_file where keyroot='" & DistinctPhysician(i) & "'"
        rs.Open sql, Conn, adOpenStatic, adLockReadOnly
           
           
            FirstName = Trim(Mid((rs("PNAME")), 16, 10))
            NameLength = Len(FirstName)
            FirstName = Left(FirstName, 1) & LCase(Mid(FirstName, 2, NameLength - 1))
           
            LastName = Trim(Mid(rs("PNAME"), 1, 15))
            NameLength = Len(LastName)
            LastName = Left(LastName, 1) & LCase(Mid(LastName, 2, NameLength - 1))
       
            MI = Mid(rs("PNAME"), 26, 1)
           
            If MI = "" Then
                PhysName = FirstName & " " & LastName
            Else
                PhysName = FirstName & " " & MI & " " & LastName
            End If
             
            For j = 1 To 4
                 NameInsert = "PhysName_" & j
                 Set objBookmark = objNewDoc.Bookmarks(NameInsert)
                 Set objRange = objBookmark.Range
                 objRange.Text = PhysName
            Next j
            Set objBookmark = Nothing
            Set objRange = Nothing
       
            If IsNull(rs("paddr2")) Then
                Address = rs("paddr1")
            Else
                Address = rs("paddr1") & vbCr & rs("paddr2")
            End If
            Set objBookmark = objNewDoc.Bookmarks("Address")
            Set objRange = objBookmark.Range
            objRange.Text = Address
            objRange.Case = wdTitleWord
            Set objBookmark = Nothing
            Set objRange = Nothing
           
            CityStateZip = rs("pcity") & ", " & rs("pstate") & " " & rs("pzip")
            Set objBookmark = objNewDoc.Bookmarks("CityStateZip")
            Set objRange = objBookmark.Range
            objRange.Text = CityStateZip
            objRange.Case = wdTitleWord
            Set objBookmark = Nothing
            Set objRange = Nothing
        rs.Close
                 
         
        '/////////////////////////////////////////////////////////////////////////////////
        '/////////////////////////////////////////////////////////////////////////////////
        '1. For physician, get the average cost of the drugs prescribed
        sql = "SELECT avg(elig) as avgelig FROM pur_data where keyroot='" & DistinctPhysician(i) & "'"
        rs.Open sql, Conn, adOpenStatic, adLockReadOnly
        PhysAvgCost = rs("avgelig")
        rs.Close
       
        sql = "SELECT Mkt_Desc FROM pur_data where keyroot='" & DistinctPhysician(i) & "'"
        rs.Open sql, Conn, adOpenStatic, adLockReadOnly
        PhysSpecialty = Trim(rs("Mkt_desc"))
        rs.Close
       
        Set objBookmark = objNewDoc.Bookmarks("Mktg_Splty_Desc_1")
        Set objRange = objBookmark.Range
        objRange.Text = PhysSpecialty
        Set objBookmark = Nothing
        Set objRange = Nothing
       
        Set objBookmark = objNewDoc.Bookmarks("Mktg_Splty_Desc_2")
        Set objRange = objBookmark.Range
        objRange.Text = PhysSpecialty
        Set objBookmark = Nothing
        Set objRange = Nothing

        sql = "select avg(elig) as AvgElig from pur_data where Mkt_Desc='" & PhysSpecialty & "'"
        rs.Open sql, Conn, adOpenStatic, adLockReadOnly
        SpcltyAvgCost = rs("AvgElig")

        rs.Close

        objNewDoc.InlineShapes(1).OLEFormat.Edit
        Set MyChart = objNewDoc.InlineShapes(1).OLEFormat.Object
        MyChart.Application.DataSheet.Cells(2, 2).Value = Round(PhysAvgCost, 2)
        MyChart.Application.DataSheet.Cells(2, 3).Value = Round(SpcltyAvgCost, 2)
        MyChart.Application.Update
        MyChart.Application.Quit
        Set MyChart = Nothing
        Selection.HomeKey Unit:=wdStory, Extend:=wdMove

        sql = "select keyroot, avg(elig) as AvgElig from pur_data where Mkt_Desc='" & PhysSpecialty & "' group by keyroot order by avg(Elig)"
        rs.Open sql, Conn, adOpenStatic, adLockReadOnly
        Set objBookmark = objNewDoc.Bookmarks("count")
        Set objRange = objBookmark.Range
        objRange.Text = rs.RecordCount
        Set objBookmark = Nothing
        Set objRange = Nothing

        objNewDoc.InlineShapes(2).OLEFormat.Edit
        Set MyChart = objNewDoc.InlineShapes(2).OLEFormat.Object
        For j = 1 To rs.RecordCount
            tempARC = rs("AvgElig")
            MyChart.Application.DataSheet.Cells(2, j + 1).Value = Round(tempARC, 2)
            If rs("keyroot") = DistinctPhysician(i) Then
                location = j
                MyChart.SeriesCollection(1).Points(j).Interior.ColorIndex = 5
                'MyChart.SeriesCollection(1).Points(j).Interior.Color = RGB(250, 0, 0)
                MyChart.SeriesCollection(1).Points(j).Border.Weight = xlThick
                MyChart.SeriesCollection(1).Points(j).Border.ColorIndex = 5
            End If
            rs.MoveNext
        Next j
 
        MyChart.Application.Update
        MyChart.Application.Quit
        Set MyChart = Nothing
        Selection.HomeKey Unit:=wdStory, Extend:=wdMove
        rs.Close
'
'        '/////////////////////////////////////////////////////////////////////////////////
'        '/////////////////////////////////////////////////////////////////////////////////
'        '2. Prescribing pattern

        sql = "SELECT * from pur_history where keyroot='" & DistinctPhysician(i) & "' and quarter='" & CurrentQuarter & "'"
        rs.Open sql, Conn, adOpenStatic, adLockReadOnly
        OtherCount = rs("other")
        PreferredCount = rs("Preferred")
        GenericCount = rs("Generic")
        TotalCount = rs("TotalCount")
        rs.Close

        sql = "SELECT sum(Generic) as GroupGenericCount, sum(Other) as GroupOtherCount, sum(Preferred) as GroupPreferredCount, sum(TotalCount) as GroupTotalCount FROM pur_history where Mkt_Desc='" & PhysSpecialty & "' and quarter='" & CurrentQuarter & "'"
        rs.Open sql, Conn, adOpenStatic, adLockReadOnly
        GroupOtherCount = rs("GroupOtherCount")
        GroupPreferredCount = rs("GroupPreferredCount")
        GroupGenericCount = rs("GroupGenericCount")
        GroupTotalCount = rs("GroupTotalCount")
        rs.Close

        objNewDoc.InlineShapes(3).OLEFormat.Edit
        Set MyChart = objNewDoc.InlineShapes(3).OLEFormat.Object
        MyChart.Application.DataSheet.Cells(2, 2).Value = Round(GenericCount / TotalCount, 2)
        MyChart.Application.DataSheet.Cells(2, 3).Value = Round(PreferredCount / TotalCount, 2)
        MyChart.Application.DataSheet.Cells(2, 4).Value = Round(OtherCount / TotalCount, 2)
        MyChart.Application.DataSheet.Cells(2, 1).Value = PhysName
        MyChart.Application.DataSheet.Cells(3, 2).Value = Round(GroupGenericCount / GroupTotalCount, 2)
        MyChart.Application.DataSheet.Cells(3, 3).Value = Round(GroupPreferredCount / GroupTotalCount, 2)
        MyChart.Application.DataSheet.Cells(3, 4).Value = Round(GroupOtherCount / GroupTotalCount, 2)
        MyChart.Application.Update
        MyChart.Application.Quit
        Set MyChart = Nothing
        Selection.HomeKey Unit:=wdStory, Extend:=wdMove
'
'        '/////////////////////////////////////////////////////////////////////////////////
'        '/////////////////////////////////////////////////////////////////////////////////
'        'Historical Charts - Physicians
'        '/////////////////////////////////////////////////////////////////////////////////
'        '/////////////////////////////////////////////////////////////////////////////////

           
        sql = "SELECT DISTINCT TOP 5 Quarter from pur_history order by quarter desc"
        rs.Open sql, Conn, adOpenStatic, adLockReadOnly
        ReDim QuarterArray(rs.RecordCount, 5)
        QuarterArrayCount = rs.RecordCount
        QuarterFilter = ""
        For j = 1 To rs.RecordCount
            QuarterArray(6 - j, 1) = rs("Quarter")
            QuarterFilter = QuarterFilter & "Quarter like '" & QuarterArray(6 - j, 1) & "'"
            If j <> rs.RecordCount Then
                QuarterFilter = QuarterFilter & " or "
            End If
            rs.MoveNext
        Next j
        rs.Close
0
 
LVL 16

Expert Comment

by:twalgrave
ID: 7744581
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
- refund/PAQ
Please leave any comments here within the
next seven days.
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 7826126
Finalized as proposed

modulo

Community Support Moderator
Experts Exchange
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now