Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MailMerge

Posted on 2000-04-21
12
Medium Priority
?
232 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
[X]
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
  • 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
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
New style of hardware planning for Microsoft Exchange server.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

721 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