MailMerge

Hi, i am using Word to generate reports. I wrote this code to populate word document with values. It takes forever to laod. If i am using 500 records it takes like 6 minutes or so and the resaurces are drained. Here is the code. Will mail merge be faster then this ? I just want to use this for print out. The user will never have to see the document on screen. Print method is not there yet.

Public Function PrintReport(msFlex As MSFlexGrid)
Dim i As Integer, J As Integer
Set wApp = CreateObject("Word.Application")
wapp.Visible = False
wapp.Documents.Add
With msFlex
     For i = 0 To .Rows - 1
         For J = 0 To .Cols - 2
       wApp.Selection.InsertAfter .TextMatrix(i, J) & vbTab
            Next
        Next
End With
wApp.Selection.ConvertToTable Separator:=wdSeparateByTabs, NumColumns:=7, _
Format:=wdTableFormatSimple1, ApplyBorders:=False, _
ApplyLastRow:=False, ApplyFirstColumn:=True, ApplyLastColumn:=False
wApp.Quit False
Set wApp = Nothing
End Function
LVL 6
MarineAsked:
Who is Participating?
 
MarineConnect With a Mentor Author Commented:
I solved the problem by concatenatiting the string instead of filling the cells one by one. Instead of taking 6 or 8 minutes to fill data of 500 records it now takes about 30 seconds to do it. It works great now.I'll be deleting this question now.
0
 
Anne TroyEast Coast ManagerCommented:
Though PC specs may alter the actual time either way, Word should process this mailmerge to 500 records within 2 to 3 minutes.
0
 
MarineAuthor Commented:
I'd like to see other responses before i make a decision. Perhaps this code needs modification and someone else has a suggestion on how i can modify it.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
slink9Commented:
I use mailmerge in all of my merging processes under Word and it works well and is fast.  All you need to do is select mailmerge under tools and go from there.  You can also embed the print routine so that all they have to do is select the printer it is going to (or embed that in the macro code along with an EXIT function.  I am not at my computer at the moment but can upload some sample macros Tuesday morning (or so).  I write out a temp file from VFP and use that as my merge file.  The autostart macro in the word document then merges the data, prints it, and exits Word.  Except for selecting the printer the user doesn't even know it is in Word.
0
 
MarineAuthor Commented:
The print routine sounds good. I am not doing this via macro. I need to this all from the vb. I generate the report on the user command.
0
 
hhamsterCommented:
Whats with the ScreenUpdating property. Even if you dont show the Application, it is done (i think).

Put this line after yours:
wapp.Visible = False
Application.ScreenUpdating = False,

and after yours:
ApplyLastColumn:=False
Application.ScreenUpdating = True.

But the time is relative. This can be not too slow depending on your comupter processing power.

If you want do some acceleration, then you have to find the slowest place in your code and accelerate this place. In this case its the line:
wApp.Selection.InsertAfter .TextMatrix(i, J) & vbTab
Try to create the insert string and then do only one .InsertAfter. This is maybe faster. test it on a large amount of data. Get the time before and after the outer for..next loop in your old code and in the new one. Think about the cache, so run every test twice in a row and use (the 2nd) lower time in the conparation.

The new code could be something like:
Dim insertString As String
With msFlex
  For i = 0 To .Rows - 1
    insertString = ""
    For J = 0 To .Cols - 2
      insertString = insertString .TextMatrix(i, J) & vbTab
    Next
    App.Selection.InsertAfter insertString
  Next
End With

Beware of the last vbTab. If you put an if .. then in the middle for..next it can slow it down. Better short the string by 1 character before insertion.

Best whishes.

Btw: you could be more generous by giving more points for such questions :)

0
 
calacucciaCommented:
Marine, havn't you forgotten something ?

Calacuccia
0
 
MarineAuthor Commented:
calacuccia what do you mean ? I don't think forgotten anything. Problem has been solved by me a while back. I thinkeveryone though for their input.
0
 
calacucciaCommented:
Deleting the question..

You said in your last comment you were about to delete it.

Or you could post a question in Community Support (http://www.experts-exchange.com/Customer_Service/Experts_Exchange/) asking to save this question to the PAQ by accepting your own comment as an answer. They can do that.

Calacuccia
0
 
MarineAuthor Commented:
opps i didn't know that. I will do that now. Thanks.
0
 
darinwCommented:
Hello everyone,

I am refunding the points on this question and moving it to the PAQ.

-- I am accepting one of Marine's comments as an answer --

darinw
Customer Service
0
 
darinwCommented:
Comment accepted as answer
0
All Courses

From novice to tech pro — start learning today.