word change data in embedded graphs

[binko]

hi,

I have the following problem

I want to automatically change the data in embedded graphs.
I have been able to do it. the problem is that any time I call

Set sh = ThisDocument.InlineShapes(i)
Set ch = sh.Chart

ch.ChartData.Activate

the activate method opens the embedded excel worksheet.
As I process several graphs and do not want to let the user with 4 open Excel windows at the end of processing, I close the excel applications with

chartWorksheet.Application.Quit

at the end of processing of each chart

However when I go to a next chart , I often have problems with methods as activate (from above) or other worksheet related methods and properties.

When I execute the macro step by step in the debugger, it runs without any problems. When however I execute it at one run, it hasalways some different problem from the above - activate or worksheet related.

Also sometimes the link of the graph to its embedded worksheet is lost, so that data cannot be processed (even manually).

As it seems, it may be a problem of synchronisation. that is the closing of the application at end of processing of a graph may sill not be finished when I try to activate it for the next one.

I would be really thnakful to any suggestions related to a solution fo the above problems.

Thank you for your answers,
Binko

[Frosty]

A couple thoughts...

If you're rapidly activating and deactivating OLE objects, you definitely have the potential to break the hooks between the main app and the app used by the embedded objects.

1st suggestion, don't quit the app as a way to exit the object. Just go to your next object and do "garbage collection" at the end of all processing.

2nd suggestion... Try what I call "jiggling the handle" to see what Word needs. Slow down your code a bit to identify the actual problem via do events, timer functions, periodic msgboxes, debug.print, etc. Ways to throw your code off it's game without break points and/or stepping through the code.

I hate troubleshooting bugs that don't happen when stepping through code... But the first step is to actually find out what is breaking. It's not always what you think

[binko]

Hi Frosty,

I have this code

Sub setCharts()
Excel.Application.Visible = False
Excel.Application.Height = 0
Excel.Application.Width = 0
' process charts
setLoadShareTOHoursChart
setLoadShareLastMonthChart
setSteeringAngleTOHoursChart
setSteeringAngleLastMonthChart
Excel.Application.Quit
End Sub

In each set...Chart sub I activate a chart to modify its data

Previously I closed the excel application for each chart with

chartWorksheet.Application.Quit

Not i close excel at the end with Excel.application.quit

it functioned without problems the first time.

I wonder whether there is a way not to open the excel worksheets at all.
but if I do not activate the chart.Chartdata I have no access to the workbook and worksheet of the chart so I cannot modify its data

the charts are manually inserted in the doc template and their InlineShape has OleFormat=Nothing

I kind of arrived at this solution but it is suboptimal as the excel windows still flacker for a short time ( and I would like not to see them at all ) and the application is not really stable due to this excel matter

All I want is to modify the charts with data that I get from an xml file in an array. I have done it. But for this excel problems.

Thank you for the answer

[Frosty]

I see from the other thread that you're working in 2010. Whether the object was inserted into word or not, it's still using another application to access itself.

There is no easy way to adjust the object (that I know of) without activating it. With XML format documents, there is probably a way, but I don't know it yet.

Maybe someone else has some recent code in this area, I haven't played with embedded charts in 2010 really. But I think the above code is not quite right. If you run that code when you also have excel open (for another reason), I think you may close it too.

The flickering can probably take care of itself when the code is better, but a couple things to try...
Application.ScreenUpdating
Not using the Selection object (you didn't post your subroutines, but if you are activating your charts by selecting them, that will cause a flicker)
Try moving .top and .left of the app off the screen.

[binko]

Hi frosty,

thank yo for the answer

I tried application.top = -1000, application.left = -1000, and application.screenupdating = false

the püroblem still stays

The matter is that I can only call these methods when I have already activated the embedded excel worksheet with chart.ChartData.Activate
Then the excel fenster immediately appears though minimized, with application.visible = false, even its icon does not appear any more. It just flickers for milliseconds in the upper left corner of the screen.
As it seems it is a problem to live with , at least for now.

What is more problöematic is that I have to execute these method for several charts , and then the excel crashes. And I cannot offer such a solution to a customer.

So any ideas in this area?

here is the code I use to work with the charts



Sub setChart(ByRef iShape As InlineShape, sNames() As String, legendNames() As String)
Dim ch As Chart
Dim i As Integer
Dim v() As Variant
Dim values() As Variant
Dim j As Integer
Dim chartWorksheet As worksheet
Dim sValue As String

Set ch = iShape.Chart
ch.ChartData.Activate
Set chartWorksheet = ch.ChartData.Workbook.Worksheets(1)
chartWorksheet.Application.Visible = False
chartWorksheet.Application.Top = -1000
chartWorksheet.Application.Left = -1000
For i = 0 To UBound(sNames)
' put names of series collection in the table
' otherwise the legend names are updated to the content of the table
' so they are put there first and later insrted again in the table
If (i <= UBound(legendNames)) Then
chartWorksheet.Cells(1, i + 2) = legendNames(i)
ch.SeriesCollection(i + 1).Name = legendNames(i)
End If
sValue = "'"
v = getValueFromCollection(sNames(i))
j = UBound(v) - 1
ReDim values(j) As Variant
For j = LBound(v) + 1 To UBound(v) - 1
' if values are of type string then add apostrophe before the value
' so strings are interpreted correctly and not as date for example
If (v(0) = "string") Then
chartWorksheet.Cells(j + 1, i + 1) = sValue & v(j)
' other wise leave just the value as adding svalue leads to misinterpretation of values as strings
' instead of as doubles or integers are they are not represented in the chart
Else
chartWorksheet.Cells(j + 1, i + 1) = v(j)
End If
Next j
Next i
ch.Refresh
chartWorksheet.Application.Quit
Set ch = Nothing
Set chartWorksheet = Nothing
End Sub

[binko]

The program runs without any problems in the debugger when I set a breakpoint in the

ch.ChartData.Activate
line.
But if I do not set a breakpoint, it crashes with message "method Activate of object ChartData failed".
I also added a 10 seconds loop before the method, to give it some time for any necessary preparations. But it still crashes there. or at another point.

Really strange for me

[Frosty]

Binko,

Two things...
1) I think you should use .Chart.ChartData.Workbook.Close instead of .Application.Quit

2) If the following code does *not* contain DoEvents, it will generate an error... but only when I am not stepping through the code.

I suspect you need DoEvents rather than a generic 10 seconds in order to give the external application (Excel, in this case) the permission to do some stuff before handing control back to Word.

Try the following code, comment DoEvents out, and then comment it back in.

Then I suspect you can probably put the DoEvents right after your line of closing the Workbook (rather than quitting the application), and maybe your problem will be solved.

Sub CreateTestDoc()
Dim oNewDoc As Document
Dim rngWhere As Range
Dim i As Integer
Dim oNewShape As InlineShape

Set oNewDoc = Documents.Add
'oNewDoc.Content.Delete

Set rngWhere = oNewDoc.Content

For i = 1 To 10
rngWhere.Collapse wdCollapseEnd
Set oNewShape = rngWhere.InlineShapes.AddChart(xlPie, rngWhere)
oNewShape.Chart.ChartData.Workbook.Close
DoEvents
oNewShape.Range.InsertAfter vbCr
Set oNewShape = Nothing
Set rngWhere = ActiveDocument.Paragraphs.Last.Range
rngWhere.Select
Next

oNewDoc.Saved = True
oNewDoc.Close
End Sub

[Frosty]

Hmm, scratch that... DoEvents may not be enough...

[binko]

Hi, Thank you for the answers.

I solved the problem by creating a dummy excel application before the activate line.


Dim ExcelObjekt As Excel.Application

On Error Resume Next
Set ExcelObjekt = GetObject(, "Excel.Application")
If err.Number = 429 Then
'-->Fehler bei ActiveX-Erstellung, vermutlich ist Excel nicht gestartet!
err.Clear
Set ExcelObjekt = CreateObject("Excel.Application")
If err.Number = 429 Then
err.Clear
End If
End If
excelObject.Visible = False
excelObject.Top = -1000
excelObject.Left = -1000

Set ch = iShape.Chart

ch.ChartData.Activate


Now he problem that I have is how to make a word document know where it is.

In a standalone it is set with ActiveDocument.path. But I have the document open in a java OleControlSite object and there ActiveDocument.path = "".

so i have to make the document know where it is ;)

But this is may be not for this forum.

anyways, thank you a lot for your kind help.:thumb

[Frosty]

Yes, that was the solution I was trying to post and the server was too busy. Using getobject/CreateObject worked for me as well.

As for the other... I'm no java expert... But instead of ActiveDocument, try using the documents collection available from the word.application object