VBA and MSGRAPH charts in Excel

[thief1]

Does anyone have some code I could use as a starting point to select
an embedded MSGRAPH object (this type: EMBED("MSGraph.Chart.8",""))
and update the values on the charts datasheet with values from another
source? In this case I'll be updating with values from other Excel
workbooks.

The information I found at Microsoft gave an example using Excel and
updating an MSGRAPH in PowerPoint, but I failed to adapt it correctly.
I think some of the syntax for addressing the chart in PowerPoint is
different then for doing this to an Excel embedded chart.

 

[Jon Peltier]

Why are you inserting an MSGraph object in Excel?? Wouldn't an Excel
chart work as nicely, if not better?

Okay, there must be a good reason. I assume you're referring to article
267974. I ran through it once for a PowerPoint MSGraph Chart, and it
worked as advertised. Then I eliminated all references to PowerPoint
objects, inserted a MSGraph Chart into Excel, and the code choked on
this line:

oGraph.Application.DataSheet.Range("00").Paste True

I looked a little closer, and it turns out, when done in PowerPoint,
oGraph.Application.Name is "Microsoft Graph", but when done in Excel,
oGraph.Application.Name is "Microsoft Excel". Then I thought there might
be something different about how each app considers this line:

Set oGraph = oShape.OLEFormat.Object

In PowerPoint, TypeName(oGraph) is "Chart", but in Excel,
TypeName(oGraph) is "OLEObject".

I took a quick look at the OLEFormat class, and found minor differences
in the properties and methods exposed within them:

PowerPoint.OLEFormat

Application
FollowColors
Object
ObjectVerbs
Parent
ProgID
Activate
DoVerb

Excel.OLEFormat

Application
Creator
Object
Parent
ProgId
Activate
Verb

I don't know why the objects have different types.

I find myself returning to my original query: Why are you inserting an
MSGraph object in Excel?

- Jon

 

[thief1]

I inherited this situation and was told to be as non-intrusive as
possible about the existing look of it. An entire 'manual' was
written to quide the worker bees on how to update these via hand
keying in the data. This wouldn't have been my mess had I originated
it. I'm quite comfortable with chart automation using Excel charts.

I understood that MS Graph had an accessble object model, but I've yet
to find any information regarding it.

I'll follow your lead and prototype some things. If I find any that
work I will update this posting for the common good.

On a side note, I was able to copy/paste link the datasheet to Excel
and the chart could read it. However, the matter of updating the
chart destroys the 'include col/exclude col' settings of the chart.

 

[Andy Pope]

Hi thief1,

Here is some code that might help.
To test insert an msgraph object onto the worksheet.
In cells A1:C6 enter some data. The code below will transfer cell data
into the graph datasheet.

Although the example uses late binding if you use Tools > References to
load the MSGraph library you could the use the object browser to give
you info on properties and methods.

Sub x()
Dim objGraph As Object ' Use Late binding
Dim dsGraph As Object '
Dim lngRow As Long
Dim intCol As Integer

Set objGraph = ActiveSheet.Shapes("Object 1").OLEFormat.Object
Set dsGraph = objGraph.Object.Application.DataSheet

For lngRow = 1 To 6
For intCol = 1 To 3
dsGraph.Cells(lngRow, intCol) = ActiveSheet.Cells(lngRow,
intCol)
Next
Next
objGraph.Object.Application.Update
End Sub

Cheers
Andy

 

[thief1]

Thanks Andy,

I will be trying that out this morning at work and let you know how it works.

Regards,
Tim

 

[thief1]

Hey Andy, this is working. Of course the key was to be sure the
Microsoft Graph Library was loaded (well duh!). At least now I have
some stuff to poke around.

To fill in some addition tid bits here regarding this code and the
datasheet, I've discovered the following...

The datasheet has row names in column 1, column 2 (across the top)
starts with column header labels of A, B, C, etc.. Using this code
you will overwrite the row names if you assume that A is column 1, and
try to add only new data. IT IS NOT COLUMN 1 FROM THE CODING POINT OF
VIEW.

Again, thanks for the support, you guys are stars!

 

[thief1]

Thanks for all the assistance!

Here is what I ended the prototyping phase with. This seemed to work
consistantly too. It can modified to pass arguments for the source of
the data (sheet name), column ranges to Include/Exclude, and such.
The embedded object doesn't have to be in the form of "Object1", it
could be a name that makes more sense like "My Widget Sales".


Sub xUpdateEmbeddedGraph()
Dim objGraph As Object ' Use Late binding
Dim dsGraph As Object '
Dim lngRow As Long
Dim intCol As Integer
Dim nRowStart As Integer
Dim nRowEnd As Integer
Dim nColStart As Integer
Dim nColEnd As Integer


Application.ScreenUpdating = False

' Build arTemp for this data.
' DATA GATHERING -----------------------------------------------------
' I used a temporary array to hold the values I would be applying
later
Worksheets("Sheet1").Select
ReDim arTemp(10, 68) ' 10 rows by 68 columns.
nRowStart = 1
nRowEnd = 10
nColStart = 1
nColEnd = 68

Dim n, nn, k
k = 0
For n = nRowStart To nRowEnd
For nn = nColStart To nColEnd
arTemp(n, nn) = Cells(n, nn)
Next nn
Next n

' Sheet that contains the target embedded chart
--------------------------
Worksheets("Where My Chart Is").Activate
ActiveSheet.Shapes("Object1").Select ' object could be a name too,
like "My Widgets"

' Adding this line made the behavior consistant for effecting changes
in the datasheet
Selection.Verb Verb:=xlPrimary

Set objGraph = ActiveSheet.Shapes("Object1").OLEFormat.Object
Set dsGraph = objGraph.Object.Application.DataSheet

For lngRow = 1 To UBound(arTemp, 1)
For intCol = 1 To UBound(arTemp, 2)
dsGraph.Cells(lngRow, intCol) = arTemp(lngRow, intCol)
Next
Next

' When fresh data is written to the data sheet in this fashion, the
Include/Exlude formatting is lost.
' This is where I must reset the Included/Excluded columns for display
in Excel
n = 0
With dsGraph
For n = 2 To 15 ' These, of course, can be calculated values.
.Columns(n).Include = False
Next n
End With
n = 0
With dsGraph
For n = 30 To 68 ' These can be calculated values too.
.Columns(n).Include = False
Next n
End With

' The seemed to update without this, however, it's here just in case.
objGraph.Object.Application.Update

objGraph.Object.Application.Quit
Set objGraph = Nothing
Set dsGraph = Nothing

End Sub