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
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