Solved

MailMerge

Posted on 2000-04-21
12
225 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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel: Combine Columns Vertically 2 36
Excel 2013 read only 3 29
Excel file corrupted. 13 47
DISK utilization high 12 44
Some time ago I was asked to create a VBA function that would calculate a check digit for an input number, using the following procedure: First, sum up all the individual digits in the number If that sum value has more than one digit, then sum up …
Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now