[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
É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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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