Solved

Word Mailmerge

Posted on 2001-06-29
8
708 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
  • 4
  • 3
8 Comments
 
LVL 17

Accepted Solution

by:
inthedark earned 300 total points
Comment Utility
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
Comment Utility
Just declare
ReDim flds(0) as string and Delimiter as string and this works....I tested this.

0
 

Author Comment

by:emezei
Comment Utility
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
 
LVL 17

Expert Comment

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

0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 17

Expert Comment

by:inthedark
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

744 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

16 Experts available now in Live!

Get 1:1 Help Now