Problem Inserting graph

[North Wales]

Hi,
I use the code below which I got from here to insert a line graph, which
works. But shows a bar chart before changing to a line graph which can take
some time to change and seems to be messing things up. So I have place the
following code after it to to pause things to see if it helps.

Dim StartTime

StartTime = Timer
Do While Timer < StartTime + 60
DoEvents ' Yield to other processes.
Loop

While this does seem to help, sometimes, it’s a bit slow and takes a bit to
long.
Is there a way of refreshing things so that the change is immediate?
Also do I have “diag.ChartType = xlLine†in the right place?

I would like to point out I have changed it so it now takes the data from a
file and not a table.

Why does this change the num lock?
Is there anything else I shold know?

Sorry i may have to many questions in one message


Sub Test()

'You need to set a reference to "Microsoft Graph 11.0 Object Library" in
'Tools > References (VBA Editor menu bar).

'Declare some variables:
Dim LE As Word.OLEFormat
Dim diag As Graph.Chart
Dim docCurrent As Document
Dim rngInsertGraph As Range
Dim docSourceData As Document
Dim tblData As Table

'Then, set the variables, like:
Set docCurrent = ActiveDocument
Set docSourceData = Application.Documents.Open("C:\My Documents\test.doc")
Set tblData = docSourceData.Tables(1)
'Inserting graph at beginning of paragraph 2 in document. _
You could use the current selection instead...
Set rngInsertGraph = docCurrent.Content.Paragraphs(2).Range
rngInsertGraph.Collapse wdCollapseStart

rngInsertGraph.InlineShapes.AddOLEObject ClassType:="MSGraph.Chart.8", _
FileName:="", LinkToFile:=False, DisplayAsIcon:=False

Set LE = rngInsertGraph.Paragraphs(1).Range.InlineShapes(1).OLEFormat
LE.DoVerb wdOLEVerbShow
Set diag = LE.Object

'Manipulate the object like this:
With diag
diag.ChartType = xlLine
With .Application.DataSheet
'Assuming row one and column one contain headers _
which means that .Cells(1,1) is empty
'Get header from docSourceData:
.Cells(1, 2).Value = Left(tblData.Cell(1, 2).Range.Text, _
Len(tblData.Cell(1, 2).Range.Text) - 2)
.Cells(1, 3).Value = Left(tblData.Cell(1, 3).Range.Text, _
Len(tblData.Cell(1, 3).Range.Text) - 2)
.Cells(1, 4).Value = Left(tblData.Cell(1, 4).Range.Text, _
Len(tblData.Cell(1, 4).Range.Text) - 2)
.Cells(2, 1).Value = Left(tblData.Cell(2, 1).Range.Text, _
Len(tblData.Cell(2, 1).Range.Text) - 2)
' If you have more than two rows...
' .Cells(3, 1).Value = Left(tblData.Cell(3, 1).Range.Text, _
' Len(tblData.Cell(3, 1).Range.Text) - 2)
' .Cells(4, 1).Value = Left(tblData.Cell(4, 1).Range.Text, _
' Len(tblData.Cell(4, 1).Range.Text) - 1)

.Cells(2, 2).Value = Left(tblData.Cell(2, 2).Range.Text, _
Len(tblData.Cell(2, 2).Range.Text) - 2)
.Cells(2, 3).Value = Left(tblData.Cell(2, 3).Range.Text, _
Len(tblData.Cell(2, 3).Range.Text) - 2)
.Cells(2, 4).Value = Left(tblData.Cell(2, 4).Range.Text, _
Len(tblData.Cell(2, 4).Range.Text) - 2)
' If you have more than two rows...
' .Cells(3, 2).Value = Left(tblData.Cell(3, 2).Range.Text, _
' Len(tblData.Cell(3, 2).Range.Text) - 2)
' .Cells(3, 3).Value = Left(tblData.Cell(3, 3).Range.Text, _
' Len(tblData.Cell(3, 3).Range.Text) - 2)
' .Cells(3, 4).Value = Left(tblData.Cell(3, 4).Range.Text, _
' Len(tblData.Cell(3, 4).Range.Text) - 1)
'Etc.
'By default, a newly inserted chart is 4x4. _
So, if you have 3 columns and only 2 rows, delete extra stuff
.Columns(5).ClearContents
.Rows(3).ClearContents
.Rows(4).ClearContents
End With
'Sample code to manipulate the chart istself
.HasLegend = False
' .Application.PlotBy = xlColumns
' .Width = CentimetersToPoints(15)
' .PlotArea.Width = CentimetersToPoints(8)
' .Axes(xlCategory, xlPrimary).TickLabelSpacing = 10
' .Axes(xlCategory, xlPrimary).TickLabels.Orientation = 90
End With

'And do not forget to destroy the objects:

'Deactivate the graph object
diag.Application.Quit

'Clear objects
Set diag = Nothing
Set LE = Nothing

SendKeys "{ESC}"

docSourceData.Close wdDoNotSaveChanges

docCurrent.Range.InsertAfter "New text inserted after chart was created."

End Sub

 

[Cindy M.]

Hi Rich,

I use the code below which I got from here to insert a line graph, which
works. But shows a bar chart before changing to a line graph which can take
some time to change and seems to be messing things up. So I have place the
following code after it to to pause things to see if it helps.

Dim StartTime

StartTime = Timer
Do While Timer < StartTime + 60
DoEvents ' Yield to other processes.
Loop

While this does seem to help, sometimes, it’s a bit slow and takes a bit to
long.
Is there a way of refreshing things so that the change is immediate?
Also do I have “diag.ChartType = xlLine†in the right place?

The column chart is the default, that's why you always see it, first. The way
these things work, it's doubtful you can do much to speed it up - it will be a
question of resources.

One possibility would be to save an AutoText entry containing the graph with
your own "defaults". Insert, then modify this, rather than creating a new one.

The only other alternative I know of would be to create the graph in Excel -
because you can automate Excel's application window with .Visible = False. The
result can then be inserted into Word. The code is a bit more complex, and the
end-user would have to have Excel installed on the machine. And it probably
would be slower than using MS Graph, just not visible.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[Jesper F]

The only other alternative I know of would be to create the graph in

Excel -
because you can automate Excel's application window with .Visible = False.
The
result can then be inserted into Word. The code is a bit more complex, and
the
end-user would have to have Excel installed on the machine. And it
probably
would be slower than using MS Graph, just not visible.

Do you by any chance have a bit of example code that automates inserting the
excel-chart into word?

Thanks a bunch.

Jesper

 

[North Wales]

Hi Jesper,

When the graph is inserted, before its changed to a line graph, its
bigger. It then moves on to the next bit of code which places another graph.
After a bit more code they change to a line graph which is smaller and starts
to mess things up. So i put the pause in but it can take over 60 seconds
before they change, which is a bit long.

The code for the graph is


If Ratings = "JockeyRatings" Then
For Counter = 1 To 500
If TopRatedProfitLoss(Counter).JockeyRatingsTrainer <>
999999 Then
.Cells(2, (Counter + 1)).Value =
TopRatedProfitLoss(Counter).JockeyRatingsTrainer
End If
If TopRatedProfitLoss(Counter).JockeyRatingsJockey <> 999999
Then
.Cells(3, (Counter + 1)).Value =
TopRatedProfitLoss(Counter).JockeyRatingsJockey
End If
Next
End If

Dont know how the above looks. Would i be right in thinking i can only go
accros 256 cells?