Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

MS Chart Control Probelem - Any Alternative to this control?

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
dmontgom
Asked:
dmontgom
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
>>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
 
dmontgomAuthor Commented:
Everything using a set statment has been set to nothing.
0
 
dmontgomAuthor Commented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Éric MoreauSenior .Net ConsultantCommented:
do you have illegal values (like NULLs) ?
0
 
mdouganCommented:
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
 
dmontgomAuthor Commented:
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
 
twalgraveCommented:
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
 
moduloCommented:
Finalized as proposed

modulo

Community Support Moderator
Experts Exchange
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now