RE: Insert graph with macro

"North Wales" wrote:

Hi,

I don’t mean to mess you about but can’t get my head around it and its
not doing what I expected, I just want the graph in the document that I’m
working on.

This is what the code I posted does.

I have had a play around and if record a macro and use Insert\Picture\Graph

You mean:
Insert\Picture\Chart

I can insert the graph. Can I change the data at run time using the macro
with something like you had in the code you sent eg .Cells(1, 2).Value = 25
and how would I deselect the graph so that the macro can continue when
finished inserting the graph.

In your first post, you wrote that the values were in a CSV file, and I
quote: "in a
file which looks something like this 25,26,15,8 and so on"

I suggested that instead of a CSV file, you use a Word file with a table
that could be used to create the chart from.

Just modify the code below to match your table.
More code could be added to check how many rows/column that table has. So,
instead of hard coding the table cell addresses, you could have code that
runs in a loop until all rows/columns were addressed.
But that is a little more advanced.
My code also assumes that the table in the data document has a header row.
If your does not, just remove the code addressing the second row and do use
the Cell(1,1) address.

By the way, I noticed a small mistake in my code regarding the "With"
blocks. I fixed it below. Also, I modified it to include a document variable
pointing to the original document. Finally, I added sample code to show you
how to return control to the macro so that the original document can be
manipulated after inserting the chart.


I would suggest that you try the code "as is" on an empty document (well at
least a few empty paragraphs...). Only modify the path and document name for
the data source document [.Documents.Open("C:\My Documents\test.doc")]. This
way you will see what the code does. After that, modify it so that you get
the values you want and also so that the charts gets positioned where you
want...


Option Explicit

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 o_OLE 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 o_OLE = rngInsertGraph.Paragraphs(1).Range.InlineShapes(1).OLEFormat
o_OLE.DoVerb wdOLEVerbShow
Set diag = o_OLE.Object

'Manipulate the object like this:
With diag
With .Application.Data***
'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 o_OLE = Nothing

SendKeys "{ESC}"

docSourceData.Close wdDoNotSaveChanges

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

End Sub
.

Quantcast