Charting - update datasheet with just an hourglass

[ray]

Hi folks,

I have seen this question raised before but cannot find an answer. I am
creating and refreshing data in a graph datasheet within Word. The code
opens a connection to SQLServer and throws the data into the datasheet.
It all works fine, does zackly what its supposed to ....

EXCEPT ...!

Even with ScreenUpdating = False, and a number of other variations that
I have tried, I cannot stop the datasheet appearing and updating in
front of the user. I just want to show an hourglass and then bing! the
updates show on the graph.

Any hope, ideas, commiserations?

Thanks in advance,

Ray



The code is:

Sub FillIndexGraph()
Dim strConn As String, dbConn As ADODB.Connection
Dim PW As ADODB.Recordset, strDisp As String
Dim cmdSQL As ADODB.Command
Dim BMRange As Range, n As Integer

Dim LE As Word.OLEFormat
Dim oMSGraphWrapper As Word.Shape
Dim oMSGraphObject As Object
Dim oDataSheet As Graph.DataSheet
Dim oChart As Graph.Chart

strConn = "Provider=SQLOLEDB;Data Source=SQL66;Initial
Catalog=SPA_Data;uid=usp_User;password=public"
Set dbConn = New ADODB.Connection
dbConn.Mode = adModeRead
dbConn.Open strConn

If dbConn.State = adStateOpen Then
Set cmdSQL = New ADODB.Command
Set cmdSQL.ActiveConnection = dbConn
cmdSQL.CommandTimeout = 0
Application.ScreenUpdating = False
Else
MsgBox Err.Number & ": " & Err.Description
Exit Sub
End If

Set PW = New ADODB.Recordset
PW.Open "usp_EquitiesReportTemplate_TimeSeries_StockVsIndex", dbConn,
adOpenForwardOnly, adLockReadOnly
PW.MoveFirst

Set oMSGraphWrapper = ActiveDocument.Shapes("SGraph")
oMSGraphWrapper.OLEFormat.Edit
Set oMSGraphObject = oMSGraphWrapper.OLEFormat.Object
Set oDataSheet = oMSGraphObject.Application.DataSheet

ActiveDocument.Application.ScreenUpdating = False

With oDataSheet
Do Until PW.EOF
n = n + 1
.Cells(3, n + 1).Value = PW![Share Price ($)]
etc
etc etc

 

[Jezebel]

Application.visible = false

 

[ray]

Hey Jezebel! Wild solution! First Word disappeared entirely, so after
the line
Set oMSGraphObject = oMSGraphWrapper.OLEFormat.Object

I tried
oMSGraphObject.Application.Visible = False

That gave me error 1004: Unable to set the Visible property of the
Application Class.

Have I put it in the wrong spot, or used the wrong object?

Thanks,

Ray

 

[Jezebel]

Of course Word disappears entirely. That's the point. (Display the process
list to find the orphaned process.)

You need to a) make sure you make it visible again on completion or on
error, and b) provide a userform or some such to give the user something to
watch.

 

[ray]

Jezebel,

Thanks for your suggestion. I have shown it to the users, and they want
the document to stay visible while the process is executing.

I tried a meter to show them that all is well, but they would prefer to
see the datasheet appear and update dynamically rather than the whole
thing disappear entirely, no matter how quickly it reappears. One
tester of the new process rang and said that his PC appeared to have
become unstable and should he reboot?

The middle ground remains their preferred option ie to let the document
remain visible, but not show the rather clunky looking datasheet update
process while the new data is being put into the graph. Do you know if
there is a way to do this?

Thanks again,

Ray

 

[Jezebel]

Application.ScreenUpdating = FALSE

Jezebel,

Thanks for your suggestion. I have shown it to the users, and they want
the document to stay visible while the process is executing.

I tried a meter to show them that all is well, but they would prefer to
see the datasheet appear and update dynamically rather than the whole
thing disappear entirely, no matter how quickly it reappears. One
tester of the new process rang and said that his PC appeared to have
become unstable and should he reboot?

The middle ground remains their preferred option ie to let the document
remain visible, but not show the rather clunky looking datasheet update
process while the new data is being put into the graph. Do you know if
there is a way to do this?

Thanks again,

Ray