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

x
?
Solved

Word Mailmerge

Posted on 2001-06-29
8
Medium Priority
?
746 Views
Last Modified: 2012-08-13
I'm creating a Word Data Document from VB for a mailmerge.  My problem is that I have over 30 fields and I get an automation error on the following line that it is longer than 255 chars.

wrdDoc.MailMerge.CreateDataSource Name:="C:\Datadoc.doc", HeaderRecord:="Firstname, LastName, and the rest ...."

I can't use short field names - I must keep the original fieldnames.  I tried using temporary short names and then trying to modify the FieldNames and/or DataFields properties but I get an error.  It appears that you cannot modify these properties in runtime.
0
Comment
Question by:emezei
[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
  • 4
  • 3
8 Comments
 
LVL 17

Accepted Solution

by:
inthedark earned 1200 total points
ID: 6237654
You could try creating a document without using the mail merge. Print your database fields into the document. You can then have any number of fields you like.

In the following example a pipe delimited file contains your address fields. But you can open your data using other methods too.  You may need some help with this.

You will end up with a very felixble solutuion.

Best of luck, inthedark.


Untested Example:

Dim WordApp As Word.Application
Dim Doc As Word.Document
Dim DSel As Word.Selection
Dim Range As Word.Range
Dim datafile As Long
Dim fc As Integer ' field count
Dim wrd
ReDim flds(0)


' create an instance to word
Set WordApp = New Word.Application

' give word some time
DoEvents
DoEvents
DoEvents
DoEvents

' show word (optional)
WordApp.Visible = True
DoEvents

' create a new document
Set Doc = WordApp.Documents.Add ' can set a template ("C:\templates\yourtemplate.dot", False)

' but its best to setup a template and open like:
'Set Doc = WordApp.Documents.Add("C:\templates\yourtemplate.dot", False)

' create a link to the current typing area (known as the selection)
Set DSel = WordApp.Selection

' open your data file
' theformatof your data is another subject but here is an example using a pipe delimter:

delimiter="|"


datafile = FreeFile
Open "C:\MyData.txt" For Input As #datafile

Dim DataLine$

Do While Not EOF(datafile)
   Line Input #datafile, DataLine$
   
   ' turn the single line into an array of fields
   flds = Split(DataLine, Delimiter)
       
   ' The name and address are in fields
   ' fld(0), fld(1), etc.

   GoSub CreatePage
Loop

Close datafile

' save the document
Doc.SaveAs "C:\windows\Temp\test.doc"
   
WordApp.Quit
Set DSel = Nothing
Set Range = Nothing

MsgBox "Done"

Exit Sub

CreatePage:


' setup a style
DSel.Style = Doc.Styles("Normal")

' put the addresses into the page

For fc = 0 To UBound(flds)
   DSel.TypeText flds(fc) + vbCrLf
Next fc

' movedown

DSel.TypeText vbCrLf
DSel.TypeText vbCrLf
DSel.TypeText vbCrLf

DSel.TypeText "Dear Chummy," ' or even "Dear " + fdl(25) +","

DSel.TypeText vbCrLf

' type some words
DSel.TypeText "Mary had a little lamb" + vbCrLf

' now close of the data this record
' now move to the next page

' Now go to end of document
DSel.Collapse Direction:=wdCollapseEnd '0

' insert a pagebreak
DSel.InsertBreak wdPageBreak

Return
0
 
LVL 6

Expert Comment

by:devtha
ID: 6237951
Just declare
ReDim flds(0) as string and Delimiter as string and this works....I tested this.

0
 

Author Comment

by:emezei
ID: 6238184
Thanks for your response.  This works but I can not use it.  We have to use mailmerge because this need is based on hundreds of existing mailmerge templates utilizing these 30+ fields.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Expert Comment

by:inthedark
ID: 6238214
Acutally now I think of it I have seen a similar problem. What version of Word and service packs are you using?

0
 
LVL 17

Expert Comment

by:inthedark
ID: 6238230
You can use a templaple.

You can open your template scan the document and replace your tags with the required fields. Then print it page by page if you wish.




0
 

Author Comment

by:emezei
ID: 6238292
inthedark,

I'm not at work today,  but I believe we are using 97 - definately not 2000.  Word has a similar restriction of 255 chars on the '.OpenDataSource SQLStatement:= ...' but there they have a 'SQLStatement1:= ...' as well, so you can have 2 * 255 chars.  
Thanks again.
0
 

Author Comment

by:emezei
ID: 6239092
See MS Site - Q298418 - PRB: The HeaderRecord String for Word's CreateDataSource Method is Limited in Size to 255 Characters
0
 

Author Comment

by:emezei
ID: 6239677
inthedark,

I'm going to give you the points because your code worked and you put in the extra effort of writing a complete example.

For those that payed points for "Questions already asked" -I played around with the original idea of originally setting up with short names to meet the 255 limit than I did the following in runtime which worked.


Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document  '-- FOR TEMPLATE DOC
Dim wrdDataDoc  As Word.Document  '-- FOR DATA DOC

Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True
   
'--- NEW DOC FOR GENERATING TEMPLATE
Set wrdDoc = wrdApp.Documents.Add
   

'--- CREATE DATA SOURCE WITH SHORT NAMES
wrdDoc.MailMerge.CreateDataSource Name:="C:\Datadoc.doc", HeaderRecord:="FN, LN, and the rest ...."
 
'--- THIS HERE IS WHAT DID IT  
wrdDoc.MailMerge.EditDataSource

'--- OPEN FILE TO INSERT DATA
Set wrdDataDoc = wrdApp.Documents.Open("C:\DataDoc.doc")

'--- MODIFY THE FIELD NAMES
wrdDataDoc.Tables(1).Cell(1, 1).Range.Text = "FirstName"
wrdDataDoc.Tables(1).Cell(1, 2).Range.Text = "LastName"
wrdDataDoc.Tables(1).Cell(1, 3).Range.Text = "Address"
....
....
   
' Fill in the data
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

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