Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

MailMerge

Posted on 2000-04-21
12
228 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:Marine
  • 5
  • 2
  • 2
  • +3
12 Comments
 
LVL 22

Expert Comment

by:Dreamboat
ID: 2738386
Though PC specs may alter the actual time either way, Word should process this mailmerge to 500 records within 2 to 3 minutes.
0
 
LVL 6

Author Comment

by:Marine
ID: 2738403
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
 
LVL 23

Expert Comment

by:slink9
ID: 2738508
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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 6

Author Comment

by:Marine
ID: 2738528
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
 
LVL 2

Expert Comment

by:hhamster
ID: 2740724
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
 
LVL 6

Accepted Solution

by:
Marine earned 15 total points
ID: 2740733
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
 
LVL 17

Expert Comment

by:calacuccia
ID: 2853952
Marine, havn't you forgotten something ?

Calacuccia
0
 
LVL 6

Author Comment

by:Marine
ID: 2853956
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
 
LVL 17

Expert Comment

by:calacuccia
ID: 2853961
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
 
LVL 6

Author Comment

by:Marine
ID: 2853964
opps i didn't know that. I will do that now. Thanks.
0
 
LVL 3

Expert Comment

by:darinw
ID: 2856089
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
 
LVL 3

Expert Comment

by:darinw
ID: 2856094
Comment accepted as answer
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Perhaps more familiar to developers who primarily use VBScript than to developers who tend to work only with Microsoft Office and Visual Basic for Applications (VBA), the Dictionary is a powerful and versatile class, and is useful …
My experience with Windows 10 over a one year period and suggestions for smooth operation
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question