Update Graph via VBA - Changes Not Saved

[kinggi]

Hi,

I use the following code to update a graph in MS Word. When it runs you can
see the data being updated, but when it quits the object the changes are lost
and the old data is restored.

However, If i break into code and execute the function line-by-line, the
changes are saved.

Any help would be appreciated.
-Steve

Code:

Public Sub UpdateRFSResponsivenessChart(strPath As String)

'Check if the bookmark exists
If Bookmark_Exists(ActiveDocument, "GRAPH_RFS_RESPONSIVENESSYTD") = False
Then
MsgBox "The bookmark '" & "GRAPH_RFS_RESPONSIVENESSYTD" & "' does not
exist. The RFS Movement Chart was not updated."
Exit Sub
End If

Dim oMSGraphWrapper As Word.InlineShape
Dim oDoc As Word.Document
Dim oMSGraphObject As Object
Dim oDataSheet As Graph.DataSheet
Selection.GoTo What:=wdGoToBookmark, Name:="GRAPH_RFS_RESPONSIVENESSYTD"
Set oDoc = ActiveDocument
Set oMSGraphWrapper = Selection.InlineShapes(1)
oMSGraphWrapper.OLEFormat.Edit
Set oMSGraphObject = oMSGraphWrapper.OLEFormat.Object
Set oDataSheet = oMSGraphObject.Application.DataSheet
With oDataSheet
Dim myDataBase As Database
Dim myActiveRecord As Recordset
Set myDataBase = OpenDatabase(strPath)
Set myActiveRecord =
myDataBase.OpenRecordset("tmpCPR_RFS_ResponsivenessGraph", dbOpenForwardOnly)
Dim intA As Integer, intB As Integer
For intA = 15 To 1 Step -1
.Columns(intA).Delete
Next intA
intA = 1
'Loop through all the records in the table until the end-of-file marker is
reached
.Cells(intA, 1).Value = "Responsiveness"
.Cells(intA, 2).Value = "NumberRFS"
intA = intA + 1
Do Until myActiveRecord.EOF = True
.Cells(intA, 1).Value = (myActiveRecord.Fields("Category").Value)
.Cells(intA, 2).Value = (myActiveRecord.Fields("NumberRFS").Value)
'access the next record
myActiveRecord.MoveNext
intA = intA + 1
Loop
'.Update
'Then close the database
myActiveRecord.Close
myDataBase.Close
.Application.Chart.Refresh
End With
With oMSGraphObject.Application
.Update
.Quit
End With
Selection.GoTo What:=wdGoToBookmark, Name:="GRAPH_RFS_RESPONSIVENESSYTD"
Set oDoc = ActiveDocument
Set oMSGraphWrapper = Selection.InlineShapes(1)
oMSGraphWrapper.OLEFormat.Edit
Set oMSGraphObject = oMSGraphWrapper.OLEFormat.Object
oMSGraphObject.Application.Update
oMSGraphObject.Application.Quit

Set oDataSheet = Nothing
Set oMSGraphWrapper = Nothing
Set oDoc = Nothing
Set oMSGraphObject = Nothing

End Sub

 

[Doug Robbins - Word MVP]

If I have interpreted the code correctly, the graph is in oDoc and you are
setting that to nothing without having saved it. Does it work OK if you
insert

oDoc.Save

before the

Set oDoc = Nothing

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[kinggi]

Hi Doug,
oDoc refers to the active document. This code resides in a template, and
oDoc refers to the document that is being created based on the template. The
code executes once the document / template is opened.

Setting oDoc = Nothing does not lose changes to the document. Other lines of
code update data tables and other text in the document, and once it has
finished running the document stays open in an unsaved state for the user to
modify, print, or save.

 

[Russ]

Is it a timing thing between your various Updates and Quits, where it
doesn't wait until fully updating before quitting? If you put a MsgBox
"Delay" between the Updates and Quits, does it work then?

 

[kinggi]

Hi Russ,
I tried placing a message box, and also placing various delay mechanisms and
using DoEvents, to no avail. While the message box is present, I can see the
updated graph, but when it continues after, the updates are replaced with the
original values. If I break into code from the message box and then execute
one line at a time, the update gets saved.

-Steve

 

[Russ]

You might also try:
Application.ScreenRefresh
Immediately after changing various data and before closing various parts of
your subroutine.
and:
Application.ScreenUpdating = True
At the end of your operation.