Solved

MS Chart Control Probelem - Any Alternative to this control?

Posted on 2002-06-10
8
238 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 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

808 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