Charts

[Phil Stanton]

I appreciate that this is an Excel forum, and I am using a chart object in
Access, but probably I will be better off posting it to this newsgroup.

I am using a scatter chart on a form to show a map and points of interest
that I want labelled.

After a struggle I have loaded the map, loaded the point and from a
recordset, copied and pasted the label information into column 3 of the
chart's Datasheet.

Set DtaSheet = Cht.Application.DataSheet
For Each ChtLabel In ChtLabels
ChtLabel.Caption = DtaSheet.Cells(lCount + 2, 3)
lCount = lCount + 1
Next ChtLabel

This works OK but takes 20 seconds to label 68 points. I thought it might be
quicker to do it this way rather than getting the label information straight
from the recordset, but there is little difference in time.

What can I do to speed it up? Is there a way of loading all the labels at
once

Thanks

Phil

 

[strive4peace]

Hi Phil,

the king of charting with Excel is Jon Peltier and he has excellent help
on his website ... perhaps you can find something to help ...

Peltier Technical Services, by Jon Peltier
http://peltiertech.com/

~~~

Like you, I much prefer using Excel for charts than the MSGraph applet
that Access uses -- and I have found great stuff on Jon's site...


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Phil Stanton]

Hi Crystal

Thanks for coming back.

I assumed that the Microsoft Graph 2000 Chart was the same graph as used in
Excel. Am I wrong?

Have been to Jon's site frequently, and not coming up with any solutions.

At the moment I am getting away with it by getting Excel to produce the
information by getting the data from the database, creating the chart and
exporting it as a GIF file, then showing thid GIF file as an image on my
form. It works, but is messy. I hoped to dynamically update the graph on my
form as the data was changed.

Phil

 

[Peter T]

It does seem too long, not sure if the time is getting the data from cells
or writing to the data labels. Normally it would be the latter but I don't
quite follow this Sheet reference
Cht.Application.DataSheet

It might help a little to temporarily disable screenupdating of the Excel
application.

Would it be viable for you to link the data labels directly to cells. Do it
once then forget about them.

Regards,
Peter T

 

[Phil Stanton]

Thanks for coming pack, Peter

My definitions are

Dim Cht As Chart
Dim DtaSheet As DataSheet

I am struggling to differentiate between a DataSheet and a DataTable so
could be wrong here. Where is the data that "drives" the chart held
(remembering I am using Access not Excel, so there is no Worksheet.

Equally I am not sure of the difference between ChtLabel.Caption &
ChtLabel.Text

Unfortunately there is no Access equivalent of ScreenUpdating, but I have
tried making the chart invisible while the process is going on, but that
doesn't change the time taken.

I am surprised that as the process is so slow, I do not see the labels
appearing over the 20 seconds or so that it takes.

Have speeded things up a bit by deleting the DataLabels before running the
piece of code below

Phil

 

[Peter T]

I see now from your other post you are using the MS Graph. I have never used
it but at a glance it's object model does indeed appear to be remarkably
similar to Excel's chart. However I guess it is not possible to link cells
of the Graph's datasheet in the same way as is possible in Excel.

Is the long time mainly due to writing text to each of the datalables, or is
it in part due to reading from cells. To test simply write "abc" to each
data label in a loop.

Regards,
Peter T

 

[Phil Stanton]

Hi Peter

That reduces the time from about 20 seconds to about 12 seconds.

What is most curious is that it takes about 25 second to write
"ABCDEFGHIJKLMNOPQRSTUVWXYZ". Possibly more problems with the label going
over the edge of the chart

Am also playing around with the orientation and that also is taking time

Revised relevent bit of code is

Set DtaSheet = Cht.Application.DataSheet

With ChtLabels
.Position = xlLabelPositionCenter
.VerticalAlignment = xlVAlignCenter
.HorizontalAlignment = xlHAlignCenter
.Type = xlDataLabelsShowLabel
.Shadow = False
.Font.Size = 7
.Font.Name = "Ariel"
.Font.Bold = False
.AutoScaleFont = False
.Orientation = CLng(DtaSheet.Cells(2, 8)) ' Standard Angle
End With

Call SysCmd(acSysCmdInitMeter, "Adding " & NoPoints & " Labels",
NoPoints)


For Each ChtLabel In ChtLabels
ChtLabel.Caption = DtaSheet.Cells(lCount + 2, 3)
'ChtLabel.Text = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
'ChtLabel.Text = "ABC"
If DtaSheet.Cells(lCount + 2, 11) <> 0 Then ' Standard
Angle
ChtLabel.Orientation = CLng(DtaSheet.Cells(lCount + 2, 11))
End If
lCount = lCount + 1
Call SysCmd(acSysCmdUpdateMeter, lCount)
Next ChtLabel

Cheers

Phil

 

[Andy Pope]

Hi,

Not sure if this will help but I posted some code on how to set the data
label text.
http://www.andypope.info/ngs/ng16.htm

The example is actually for coding MSGraph within PowerPoint but the
technique and coding should still work in in Access.


Cheers
Andy

 

[Phil Stanton]

Hi Andy

Thanks for feedback. Have used your example and it certainly seems faster

code is now

With Cht
With ChtSeries
.HasDataLabels = True
Call SysCmd(acSysCmdInitMeter, "Adding " & .Points.Count & "
Labels", NoPoints)
For lCount = 1 To .Points.Count
.Points(lCount).DataLabel.Text =
CStr(SpaceAllocationSet!SpaceAndName)
If SpaceAllocationSet!LabelAngle <> 0 Then
.Points(lCount).DataLabel.Orientation =
SpaceAllocationSet!LabelAngle
End If
DoEvents
SpaceAllocationSet.MoveNext
Call SysCmd(acSysCmdUpdateMeter, lCount)
Next lCount
End With
End With

Interestingly the counter at the bottom is almost instantaneous saying it
has added the 68 points. Adding the DoEvents shows each label as it is
added. Takes just under 20 seconds to label the 68 points. Without the
DoEvents nothing happens for 20 seconds, then all the labels appear at once.
Hiding the control makes no difference.

Seems like a delay in showing the information rather than doing it. Same
sort of routine in Excel is instantaneous. Why does the Access environment
slow things down so much?

Thanks

Phil