Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Chart Control Probelem - Any Alternative to this control?

Posted on 2002-06-10
8
Medium Priority
?
251 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 70

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 70

Expert Comment

by:Éric Moreau
ID: 7068815
do you have illegal values (like NULLs) ?
0
 
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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses

704 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