How can I stop MS Word from updating fields? 1

[JensKKK]

I have just written a routine tht inserts tables from MS Excel into MS Word.

When I transfer text and number from Excel to Word things are working OK. But as soon as I transfer grpahics the speed goes down a lot.

MS Word is giving a message in the status bar: Word is updating the fields in this document. Is there any chace to suppress this updating process?

Anything that will accelerate the copy paste process is appreciated.

Any comments welcome.

Below is the code example that opens ms word.

Sub open_Word()
Dim Last_line, Exclusion_Limit, Number_of_Drugs, Max_Hits, Decoded_Link, i, j, ii, msg, current_record As Integer
Dim weiter As Boolean
Dim dummy As String
Dim WordObj As Word.Application
Dim WordDoc As Word.Document
Dim fleNme As String
Dim Fldnme As String
Dim MyPath2 As String
Dim Mypath As String
Dim wordRange As Word.Range

Dim Var, Var2

Application.StatusBar = "Copy Detailed Results Table: "
Application.ScreenUpdating = False
fleNme = "Selectivity Report"
Fldnme = "C:\Documents and Settings\jkoopmann\My Documents"
Mypath = Fldnme
MyPath2 = Mypath & "\" & fleNme & ".doc"
On Error Resume Next
Var2 = 1
Set WordObj = GetObject(, "Word.Application")
If err <> 0 Then
' msg = MsgBox("1 test Error " & err)
Set WordObj = CreateObject("Word.Application")
err.Clear
End If
'msg = MsgBox("1 test Error " & err)
Var = WordObj.Windows.Count
Application.StatusBar = "Opening MS Word"
If Var <> 0 Then
Do Until Var2 = Var + 1
WordObj.Windows(Var2).Activate
If MyPath2 = WordObj.ActiveDocument.Name Then
GoTo line1:
End If
Var2 = Var2 + 1
Loop
End If
'msg = MsgBox("2 test vor dem offnen des Documents")
Set WordDoc = WordObj.Documents.Open(MyPath2)
Application.StatusBar = "Transfering Information into MS Word"
WordObj.Visible = True
With WordObj
'.Documents.Add DocumentType:=wdNewBlankDocument
.ActiveDocument.Fields.Update = False
.ActiveDocument.ShowSpellingErrors = False
.ActiveDocument.LineNumbering.Active = False
.ActiveDocument.PageSetup.Orientation = wdOrientLandscape
'.Selection.Paste
End With

This is the part of the code where a graph on a Excel spreadsheet is copied and pasted into MS Word.
Sheets("results").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy
With WordObj
'.Documents.Add DocumentType:=wdNewBlankDocument
'.ActiveDocument.PageSetup.Orientation = wdOrientLandscape
'.Selection.Paste
.Selection.PasteAndFormat (wdChartPicture)
End With

 

[CautionMP]

JensKKK,
One thought, and this is just a stab in the dark.

I wonder if the message doesn't represent the time Windows takes to convert the graph to a picture. You might try (notice I eliminated the [tt].Select[/tt] and [tt].Copy[/tt] statements)

[tt] ActiveSheet.ChartObjects("Chart 1").CopyPicture[/tt]

this should force the data loaded to the clipboard to be a picture and will hopefully speed things up.

If that doesn't work you could try

[tt] ActiveSheet.ChartObjects("Chart 1").SaveAs...[/tt]

and then
[tt].Selection.InsertFile...[/tt]

to load the picture into the document.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[JensKKK]

CMP,

I love this forum and I get really good tips. Thanks for your advice.

The program works now much better and faster.

I adapted your code a little bit and I past the picture in to MS Word using the
.Selection.PasteAndFormat (wdPasteDefault)

command.

Thanks again.


If Graph_Options = 3 Then
Sheets("results").Select
Application.StatusBar = "Paste graph into MS Word " & i
ActiveSheet.ChartObjects("Chart 23").CopyPicture
With WordObj
.Selection.PasteAndFormat (wdPasteDefault)
End With
Sheets("results").Select
ActiveSheet.ChartObjects("Chart 1").CopyPicture

With WordObj
.Selection.PasteAndFormat (wdPasteDefault)
.Selection.InsertBreak Type:=wdPageBreak
End With

 

[CautionMP]

JensKKK,
I'm gald to hear it worked out.

As a passing thought, you should take a look at faq707-4105, it contains a couple of tips on how to make your Excel code run faster.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)