Problems when pasting 100's of pics into MS Word 2

[JensKKK]

I have written a program that generates a report that can be viewed in MS Excel and MS Word. Whereas MS Excel is happy to display a couple of 1000 graphs, MS Word goes mad when pasting in more than 100 graphs.
MS Word also becomes pretty slow when pumping in so many data. Is that a limitation of MS Word or do I have a programming error in my code?
Has anybody of you written a script that outputs a couple of 1000 jpg picture in MS Word?

Code

Sub open_Word()
Dim Exclusion_Limit, Y_distance, NoG, oldj, Number_of_Drugs, max_hits, i, Replicate, j, ii, msg As Integer
Dim Start, graphcount, number_of_hits, teiltabelle, Number_hits_pointer(3), upperlimits(3) As Integer
Dim Zeilen, Var, Var2, X_graph, y_graph, Header_or_Footer, List_y_pos(4) As Integer
Dim found_MS_Wurg, weiter, Show_Graph, date_YON As Boolean
Dim dummy, body_font, headline_font, cmpd_name, disclaimer, error_string As String
Dim Wordobj As Word.Application
Dim WordDoc As Word.Document
Dim kinaselist(4), kinasegroup(3), histogram(3), neue_seite, three_d_plot, rfu_plot, pie_chart, array_graph, word_out As Boolean

word_out = Sheets("3d rotate").Cells(73, 23)
'Teste wieviele graphiken vorhanden sind.
If word_out Then
disclaimer = "Please don't activate any Word document by clicking onto them. This will interfere with the report generation. "

On Error Resume Next
Application.ScreenUpdating = False
Sheets("3d rotate").Visible = True
Var2 = 1
Set Wordobj = GetObject(, "Word.Application")
If err <> 0 Then
Set Wordobj = CreateObject("Word.Application")
err.Clear
End If

With Wordobj
.Documents.Add DocumentType:=wdNewBlankDocument
.ActiveDocument.ShowSpellingErrors = False
.ActiveDocument.LineNumbering.Active = False
.ActiveDocument.PageSetup.Orientation = wdOrientLandscape 'wdOrientPortrait
'.ActiveDocument.PageSetup.Orientation = wdOrientPortrait 'wdOrientPortrait
.ActiveWindow.ActivePane.View.Zoom.Percentage = 25
.Application.StatusBar = disclaimer
End With


i = 1
found_MS_Wurg = False
Do Until i > 50
dummy = "Document" & i
If dummy = Wordobj.ActiveDocument.name Then
Wordobj.Windows(i).Activate
Wordobj.Visible = True
Application.StatusBar = disclaimer & "Transfering information into MS Word"
found_MS_Wurg = True
i = 51
End If
i = i + 1
Loop
With Wordobj.ActiveWindow
With .View
.ShowAnimation = True
.Draft = False
.WrapToWindow = False
.ShowPicturePlaceHolders = False
.ShowFieldCodes = False
.ShowBookmarks = False
.FieldShading = wdFieldShadingWhenSelected
.ShowTabs = False
.ShowSpaces = False
.ShowParagraphs = False
.ShowHyphens = False
.ShowHiddenText = False
.ShowAll = False
.ShowDrawings = True
.ShowObjectAnchors = False
.ShowTextBoundaries = False
.ShowHighlight = True
.ShowOptionalBreaks = False
.DisplayPageBoundaries = True
.DisplaySmartTags = True
End With
End With
If found_MS_Wurg = False Then
msg = MsgBox("Error while trying to open MS Word")
End If

End If 'word_out



'***************************************
'***************************************
'***************************************
'Read important parameters
Replicate = Sheets("3d rotate").Cells(16, 20)
three_d_plot = Sheets("3d rotate").Cells(168, 22)
rfu_plot = Sheets("3d rotate").Cells(170, 22)
pie_chart = Sheets("3d rotate").Cells(172, 22)
array_graph = Sheets("3d rotate").Cells(174, 22)
Number_hits_pointer(1) = 996
Number_hits_pointer(2) = 994
Number_hits_pointer(3) = 993
number_of_hits = 0
For i = 1 To 3
histogram(i) = Sheets("3d rotate").Cells(154 + i * 2, 22)
kinasegroup(i) = Sheets("3d rotate").Cells(160 + i * 2, 22)
upperlimits(i) = Sheets("3d rotate").Cells(148 + i * 2, 22)
kinaselist(i) = Sheets("3d rotate").Cells(174 + i * 2, 22)
If upperlimits(i) = 341 Then
If histogram(i) Then
number_of_hits = number_of_hits + Sheets("3d rotate").Cells(Number_hits_pointer(i), 1)
End If
Else
number_of_hits = number_of_hits + (Number_of_Drugs * max_hits / Replicate)
End If
Next

Y_distance = 15

max_hits = Sheets("3d rotate").Cells(32, 22)
Exclusion_Limit = Sheets("3d rotate").Cells(34, 22)
Number_of_Drugs = Sheets("3d rotate").Cells(16, 22)
'Graph_Options = Sheets("3d rotate").Cells(37, 22)
headline_font = Sheets("3d rotate").Cells(76, 23)
body_font = Sheets("3d rotate").Cells(77, 23)
Header_or_Footer = Sheets("3d rotate").Cells(101, 22)
date_YON = Sheets("3d rotate").Cells(130, 24)
'Variabeln mit denen die Bildgrosse in MS Word gesteuert wird
X_graph = Sheets("3d rotate").Cells(146, 23)
y_graph = Sheets("3d rotate").Cells(147, 23)
'***************************************
'***************************************
'***************************************

If number_of_hits > 3400 Then
msg = MsgBox("You are trying to create a report with " & number_of_hits & " graphs." & _
vbNewLine & "The number of graphs is limited to 3400 due to memory restriction." _
& vbNewLine & "Please reduce the number of graphs in Report Settings Form." _
& vbNewLine & vbNewLine & "This routine aborts now.")
Exit Sub
End If

'Losche alten Report
Application.Run "clear_detailed_results", "Detailed Results", body_font


If word_out Then
'Create a header for the experiment in MS Word
With Wordobj
.ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
.Selection.TypeText text:="Procognia Kinome 2.0 Selectivity Assay " & Sheets("3d rotate").Cells(128, 22)
.NormalTemplate.AutoTextEntries("Confidential, Page #, Date").Insert Where _
:=Selection.Range, RichText:=True
.NormalTemplate.AutoTextEntries("Page X of Y").Insert Where:=Selection. _
Range, RichText:=True
.ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument
.Application.StatusBar = disclaimer & " Transfering data."
End With
End If 'word out
Application.Run "results_header", date_YON, body_font
j = j + 10
Sheets("3d rotate").Cells(80, 22) = j
If word_out Then
Sheets("detailed results").Cells(5, 2).Resize(8, 1).Select
Application.Run "word_output", Wordobj, disclaimer, 1
End If 'word out

For i = 1 To Number_of_Drugs
neue_seite = False
cmpd_name = Sheets("3d rotate").Cells(8999 + i, 1)
Start = 4 + j + i
Application.Run "Table_text", i, j, Start, headline_font
Sheets("detailed results").Select
Sheets("detailed results").Cells(i + j + 4, 2).Resize(5, 14).Select
'Application.Run "wrap_my_txt", True, body_font

Sheets("detailed results").Select
If word_out Then
Sheets("detailed results").Cells(i + j + 4, 2).Resize(5, 14).Select
Application.Run "word_output", Wordobj, disclaimer, 1
End If
j = j + 4
weiter = True
Show_Graph = True
Sheets("3d rotate").Cells(84, 22) = "true"
'**********************
'determine how many hits are present in one set of results
'**********************
If Sheets("3d rotate").Cells(996, 1 + i) > (Exclusion_Limit - 1) Then
Sheets("detailed results").Cells(5 + i + j, 2) = "Number of Hits above the limit " & Exclusion_Limit
j = j + 3
weiter = False
Show_Graph = False
End If
If Sheets("3d rotate").Cells(84, 22) = False Then
Show_Graph = False
End If

If Show_Graph Then
Application.StatusBar = "Populate graphs with data: " & i
Application.ScreenUpdating = False
Application.Run "copy_graph_data", i
graphcount = 0
If array_graph Then
NoG = NoG + 1
neue_seite = True
Application.Run "array_graph", NoG, i, j, headline_font, Wordobj, word_out, disclaimer
End If
If three_d_plot Then
NoG = NoG + 1
neue_seite = True
graphcount = graphcount + 1
' msg = MsgBox("i j " & i & " " & j)
Application.Run "plot_DDD_graph", NoG, i, j
Sheets("detailed results").Select
Sheets("detailed results").Cells(5 + i + j, 2).Select
j = j + Y_distance
If word_out Then
Application.Run "word_output", Wordobj, disclaimer, 2
End If 'word out
End If 'three d plot
'Application.ScreenUpdating = True
' msg = MsgBox("ddd plot")
If rfu_plot Then
NoG = NoG + 1
neue_seite = True
graphcount = graphcount + 1
' msg = MsgBox("i j " & i & " " & j)
Application.Run "rfu_plot", NoG, i, j
Sheets("detailed results").Select
Sheets("detailed results").Cells(5 + i + j, 2).Select
j = j + Y_distance
If word_out Then
Application.Run "word_output", Wordobj, disclaimer, 2
End If 'word out
End If 'rfu plot

If pie_chart Then
NoG = NoG + 1
neue_seite = True
graphcount = graphcount + 1

Application.Run "pie_chart", NoG, i, j
Sheets("detailed results").Select
Sheets("detailed results").Cells(5 + i + j, 2).Select
j = j + Y_distance
If word_out Then
Application.Run "word_output", Wordobj, disclaimer, 2
End If 'word out
End If 'pie chart
If word_out Then
If neue_seite Then
Application.Run "word_output", Wordobj, disclaimer, 3
End If ' neue seite
End If 'word out
'Feststellen um wieviel Zeilenvorschub gebtaucht wird um fur das Arrayimage zu kompensierren
If graphcount = 1 Then
j = j + 12
End If
'Hier den text ausgeben
For teiltabelle = 1 To 3
Application.Run "kinase_bodytext", teiltabelle, i, j, upperlimits(teiltabelle), Start, NoG, Replicate, Y_distance, X_graph, y_graph, Wordobj, disclaimer, body_font, word_out, kinasegroup(teiltabelle), histogram(teiltabelle)
If kinasegroup(teiltabelle) Then
j = Sheets("3d rotate").Cells(80, 22)
NoG = Sheets("3d rotate").Cells(82, 22)
If kinaselist(teiltabelle) Then
Application.Run "kinaselist", teiltabelle, j, i, Wordobj, disclaimer, cmpd_name, word_out
j = Sheets("3d rotate").Cells(80, 22)
End If
End If
Next

End If 'show graph
j = j + 5


Next
Sheets("3d rotate").Visible = xlVeryHidden
Application.ScreenUpdating = True
If word_out Then
Wordobj.Visible = True
End If
Application.StatusBar = ""
End Sub

 

[simian101]

My past experience has been that word can not handle that large of a memory load. Word is memory intensive program just from start up.

Simi

 

[JensKKK]

Thanks simian,

this is also my experience.

Jens

 

[fumei]

Word certainly can get messed up with intensive memory usage. However, I am having a hard time figuring out what is exactly going on.

There are a number of Application.Run calls that I don't quite follow what that call is doing.

It may be (but only may be) possible) that someof the code in those procedures can be optimized for Word.

For example, I have no real idea what:

Application.Run "kinaselist", teiltabelle, j, i, Wordobj, disclaimer, cmpd_name, word_out

is actually doing.

Nor:

Application.Run "kinase_bodytext", teiltabelle, i, j, upperlimits(teiltabelle), Start, NoG, Replicate, Y_distance, X_graph, y_graph, Wordobj, disclaimer, body_font, word_out, kinasegroup(teiltabelle), histogram(teiltabelle)

THAT looks like a heck of a lot of parameters to me. It seems to be using the Word instance...but what it is REALLY doing...I have no idea.

In your procedures dealing with actions IN Word, are you using Selection at all? If you are...that will affect memory quite a bit. If you are using Selection do anything - at all - try rewriting the code to use Range instead.

I can not say it will "fix" it, but it most certainly will improve memory usage.

faq219-2884

Gerry
My paintings and sculpture

 

[JensKKK]

Gerry,

I did not copy the whole code in my post (it would have been very long).

I am using selection very often and I don't use range at all.

Is there an explanation why range is better than selection?

Do Until weiter = False
'Text in kleinen Portionen in Word drucken
If word_out Then
Sheets("detailed results").Cells(5 + i + oldj + vorschub, 2).Resize(5, 8).Select
Application.Run "word_output", Wordobj, disclaimer, 1
End If 'word out
If vorschub + 5 < content_count Then
vorschub = vorschub + 5
Else
weiter = False
End If
Loop



this is the routine that transport the selected text or graphic into MS word.

Sub word_output(Wordobj As Word.Application, disclaimer As String, sub_option As Integer)
Dim X_graph, y_graph As Integer
Dim error_string As String
On Error GoTo errorhandler

X_graph = Sheets("3d rotate").Cells(146, 23)
y_graph = Sheets("3d rotate").Cells(147, 23)
X_graph = 136 + 14
y_graph = 190 + 19
Select Case sub_option
Case 1
Selection.Copy
With Wordobj
.Selection.Paste
.Application.StatusBar = disclaimer & " Writing data table."
End With
Case 2
With Wordobj
.Selection.PasteAndFormat (wdPasteDefault)
.Application.StatusBar = disclaimer & " Writing 3D graph."
' .Selection.ShapeRange.IncrementRotation -90#
With Wordobj.ActiveDocument.InlineShapes(ActiveDocument.InlineShapes.Count)
.Fill.Visible = msoFalse
.Fill.Solid
.Fill.Transparency = 0#
.Line.Weight = 0.75
.Line.Transparency = 0#
.Line.Visible = msoFalse
.LockAspectRatio = msoTrue
.PictureFormat.Brightness = 0.5
.PictureFormat.Contrast = 0.5
.PictureFormat.ColorType = msoPictureAutomatic
.PictureFormat.CropLeft = 0#
.PictureFormat.CropRight = 0#
.PictureFormat.CropTop = 0#
.PictureFormat.CropBottom = 0#
.LockAspectRatio = msoTrue
.Height = X_graph
.Width = y_graph
' .ShapeRange.IncrementRotation -90#
End With
End With
Case 3
With Wordobj
.Selection.InsertBreak Type:=wdPageBreak
End With

Case 4
With Wordobj
.Selection.PasteAndFormat (wdPasteDefault)
With Wordobj.ActiveDocument.InlineShapes(ActiveDocument.InlineShapes.Count)
.Fill.Visible = msoFalse
.Fill.Solid
.Fill.Transparency = 0#
.Line.Weight = 0.75
.Line.Transparency = 0#
.Line.Visible = msoFalse
.LockAspectRatio = msoTrue
.PictureFormat.Brightness = 0.5
.PictureFormat.Contrast = 0.5
.PictureFormat.ColorType = msoPictureAutomatic
.PictureFormat.CropLeft = 0#
.PictureFormat.CropRight = 0#
.PictureFormat.CropTop = 0#
.PictureFormat.CropBottom = 0#
.LockAspectRatio = msoTrue
.Height = 136 + 14
.Width = 45 + 5
End With
End With
End Select
Exit Sub
errorhandler:
error_string = err.number & " " & err.Description
Application.Run "Procognia_ErrorHandler", 138, error_string

End Sub
here is an example on how I select the text that is then pasted into word

 

[fumei]

I am using selection very often and I don't use range at all.

Is there an explanation why range is better than selection?"


YES there is!

I will flatly state that if you are using Selection like that, you (or rather, Word will) will suck up resources like a mad pig.

What is the difference?

EVERY action/instruction using Selection uses GUI resources. Selection is the object that makes the screen DO STUFF. It a sense, it IS the screen. EVERY action/instruction using Selection makes Word do all the video stuff to figure out where everything is, change whatever it is, and refresh the screen.

Yes, you can turn ScreenRefresh off. However, using Selection will still use GUI resources. And if you are doing hundred of these...yup, yup, yup, Word will suck up resources and "lose" some. Word is much better than it used to be - when if you simply opened a Word document and waited long enough it would literally suck up ALL system memory.

BUT, even as a general rule, avoid using Selection for any VBA processing....at all.

For procedures that are doing a LOT of processing....again, I will flatly state that using Selection will honk Word until it is lying on the floor gasping its last.

To repeat, I certainly can not guarantee that using Range, rather than Selection, will completely fix your issue. However, I would bet good money that it would improve things by a few orders of magnitude.

It is still hard to really follow what is going on.

If these things are being putting at the end of a document, you can make a Range object of the document, and simply tack them on the end. Look at the method for Range - a Word range.

Be VERY care, as you running this from Excel, that when you declare a range to use in Word, that it is explicitly declared as Word.Range.

Otherwise - as you ARE running this from Excel - VBA will assume (properly) that it is an Excel Range object. An Excel Range object, and a Word Range object are two very very different beasties.

As for Selection vs. Range, here is an example from your code. It is going into the header and putting stuff in.

         .ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
            .Selection.TypeText text:="Procognia Kinome 2.0 Selectivity Assay   " & Sheets("3d rotate").Cells(128, 22)
            .NormalTemplate.AutoTextEntries("Confidential, Page #, Date").Insert Where _
            :=Selection.Range, RichText:=True
            .NormalTemplate.AutoTextEntries("Page X of Y").Insert Where:=Selection. _
            Range, RichText:=True
            .ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument

What actually happens. The VIEW goes into HeaderView. The screen changes. Word does this:

1. gets data from the headerfooter object
2. switches to headerview - thus actually changing the screen and using GUI resources
3. types text
4. insert an AutoText
5. inserts an AutoText
6. writes data to the header object
7. changes the View - thus using GUI resources

First of all, I notice - I think - that while you declare a WordDoc document object...you never use it. I will use it, plus a Range object, to do the VERY same thing as the code above. So the assumption is that this is used:

Set WordDoc = Wordobj.Documents.Add DocumentType:=wdNewBlankDocument

before the following.

' declared at the start as Word object
Dim r As Word.Range
' also assuming the WordDoc object is actually set

' set the Range as an object
' if this is not the correct Section header
' then obviously that would have to be changed
Set r = WordDoc.Sections(1).Headers(wdHeaderFooterPrimary).Range

With r
   .Text = "Procognia Kinome 2.0 Selectivity Assay   " & _
            Sheets("3d rotate").Cells(128, 22)
   .Collapse Direction:=wdCollapseEnd
   NormalTemplate.AutoTextEntries("Confidential, Page #, Date").Insert _
      Where:=r, RichText:=True
   .Collapse Direction:=wdCollapseEnd
   NormalTemplate.AutoTextEntries("Page X of Y").Insert Where:=r, RichText:=True
End With
Set r = Nothing

Notes:

1. the r (Word Range object) can be re-used as much as you like
2. there is NO change in View, and NO screen activity, and NO use of GUI resources

In other words, Word does not change to HeaderView, do stuff, change to MainView.

Word just makes the header be what you want....directly.

Selection is what happens ON SCREEN.

Ranges just happen.

That is a very general way of looking at things, and some may argue the fine details, but essentially, that is it.

Using Selection means instructions to change what is on screen.

Using Ranges means insructions to just do something.

BTW: aside from the Selection vs Range thing, do you really need the StatusBar messages? They use resources as well. Not much it is true, and I can certainly see that it may be a good idea to give the user a message.

BTW2: of course the Word.Range object may also be passed to procedures as a parameter. So you could declare it, and pass it, along with the Wordobj to procedures.

faq219-2884

Gerry
My paintings and sculpture

 

[PHV]

Gerry, I'd use a full qualified NormalTemplate ...

 

[jensAThome]

Gerry,

Thanks for the lecture. I appreciate that you invest so much time to explain the matter to me.
I will work my way through your answer.

Kind regards

Jens

 

[macropod]

Hi Jens,

If it correct to assume these charts are in another application (Excel?), have you thought about simply inserting links to them, then formatting the linked objects to the size you want?

If you do that, you'd only ever need to create the links once, then simply update them whenever you need to. If you need to send the reports on to someone else, you could make a copy of the linked document, then lock or unlink the charts in the copy before fowarding that.

Cheers

[MS MVP - Word]

 

[JensKKK]

Thanks for your suggestion macropod,

It is correct that I create the graphs in MS Excel and then send them to MS Word.

I have not considered to create link, because I was not aware of them. Would you mind to give me a code example on how to make it.

Cheers

 

[macropod]

Hi Jens,

The simplest way to create a link is to copy the chart or table in Excel, then paste it into Word using Edit|Paste Special and checking the 'Link' radio button, then choosing the paste format you want.

You can also create the links via vba using the Fields.Add method.

Cheers

[MS MVP - Word]

 

[fumei]

HI Jens,

I am with macropod on this. It would most likely be a better idea to link, rather than embed.

There may be a valid reason to put actual objects into the Word document, but unless there is, why bother? Using links will:

1. reduce the Word file size
2. likely eliminate your memory issue
3. as macropod points out, leaves a easily updatable source

PHV is of course quite correct. My instruction re: .NormalTemplate should be fully qualified. As a general rule, any use of an external application object should be fully qualified. I was being lazy....me bad.

faq219-2884

Gerry
My paintings and sculpture