mailmerge with txtfile

[bilisa]

Any suggestions how to execute a a direct mailmerge with the following data
from a textfile?

TABLE
0,1
""
VECTORS
0,6
""
TUPLES
0,10000
""
LABEL
1,0
"MDLNR"
LABEL
2,0
"NAVN"
LABEL
3,0
"ADRESSE1"
LABEL
4,0
"ADRESSE2"
LABEL
5,0
"POSTNR"
LABEL
6,0
"BYEN"
DATA
0,0
""
-1,0
BOT
1,0
"12321323434"
1,0
"some name"
1,0
""
1,0
"some road"
1,0
"some zipcode"
1,0
"some city"
-1,0
BOT
1,0
"123213213"
1,0
"some name"
1,0
""
1,0
"some address"
1,0
"some zipcode"
1,0
"some city"
-1,0
BOT
1,0

 

[Peter Jamieson]

I can't think of a way to do it directly - you will need to use code to
transform the data into something that Word can use. In this case, you
really need to know the structure. Doing it in code would take some effort,
but it would not be all that hard to do it by hand as long as the file is
not huge, e.g. if I make a few guesses about the format,
a. open the file in Word
b. remove the "header", including the heading labels and everything else
down to the first piece of data ("12321323434").
c. use Edit Replace to
- replace ^p1,0 by ^t
- replace ^p-1,0^pBOT^p1,0 by ^p
d. you should now have one row per record and a tab between each field.
e. optionally, use Table|Convert text to table to convert the data to a
Word table
f. add your header row (in table format if you did (e)
g. save the file as a Word .doc
h. use that as the data source.

Peter Jamieson

 

[bilisa]

Thank you for your effort, but im dealing with users who need to mailmerge
directly with the txtfile in question, because they are novice users who
recently started using MS Word and because the advantage of using vba to
automize the routine would be lost if they had to go through so many steps to
execute a mailmerge. One way to do it is to open the textfile with Excel,
copy the data into an Excel sheet and merge with it which works fine, but
even though this solves the problem I still think it would be too difficult
for my users because of their low IT level. I still think its possible to do
it directly with vba because im converting templates from WordPerfect to Word
and it works in WordPerfect. Any suggestions are welcome..

 

[Peter Jamieson]

OK, I did not recognise the format as DIF. Can the software used to produce
these files generate a format that Word can use more easily?

My assumption is that Excel does not know what to do with the LABEL
structure and throws it away, but as long as you always know what columns
/should/ be there then it may be possible to deal with that problem by using
a separate header source file in Word.

Some questions:
a. which version(s) of Word do your users have?
b. do they all also have Excel (and are they using the same version(s) as
Word?
c. do the users need to be able to select the file themselves, or can the
merge document be created for them with the data source etc. already
attached?

If the merge document can be created for them, you can try the following
1. create a header file, e.g. called myheader.txt, containing:
"MDLNR","NAVN","ADRESSE1","ADRESSE2","POSTNR","BYEN"
"MDLNR","NAVN","ADRESSE1","ADRESSE2","POSTNR","BYEN"

(Yes, duplicate the line - Word generally recognises header labels more
easily if you do)
2. in Word 2003, use Tools|Customize|Commands to drag the MailMergeHelper
comand from the All Commands category to a toolbar. Check
Tools|Options|General|"Confirm conversion at open"
3. use MailMergeHelper to set up the mail merge type and select the header
source. Then go into the Open Data Source dialog (Select Data Source in Word
2002/2003. Select All files (*.*), then locate and select the DIF file. In
Word 2000/97, check the "Select method". Then, in any version of Word, you
should be asked to confirm the format. You will probably have to select the
Show All checkbox, then select the Excel files via DDE option. Excel should
start and open the DIF file.
4. If that works, save and close the mail merge main document and try
re-opening it.

I have not checked all of this, but the main elements of it work here with
Word 2003. Obviously it is not a simple process that could easily be
followed by a user, which is why I asked question (c) above. It may be
possible to automate the process using a VBA OpenDataSource call and put
that inside an AutoOpen macro. It may also be possible to open the data
source using OLEDB, which would eliminate the additional complexity of
opening Excel, but I have not checked that yet, partly because if it does
work, it can only work in Word 2002/2003.

Also, in earlier versions of Word, there was an Excel Converter which may be
able to read the file directly - if you see this option, try it. However,
this converter has been removed from the current set of Office Text file
converters so it is not a viable long-term option.

Peter Jamieson

 

[bilisa]

answers:

a. Word 2003
b. Yes, Excel 2003
c. I have created a Word template and attached a source to it. This
works because the member-system that produces the source-output file
gives it a standard name and saves it to the c-drive.

Here is a sample of my code:

'merge button
Private Sub flet_knap_Click()

Dim filnavn As String

filnavn = "C:\Microsoft Excel Worksheet.xls" 'source

'this attaches the source file to the active document
With ActiveDocument.MailMerge
.MainDocumentType = wdLetter
.OpenDataSource Name:=filnavn, ReadOnly:=True, Connection:=""
End With

' executes mailmerge
Set myMerge = ActiveDocument.MailMerge
If myMerge.State = wdMainAndDataSource Then myMerge.Execute

 

[bilisa]

I followed the instructions you gave by creating a header file and I selected
the header source with this command: .OpenHeaderSource Name:=myheader
(myheader is a string that contains the path to myheader.txt). The result was
the source filed opened in excel but the document was not merged with the
data!!??
My source code now looks like this (if added comments to make it easy to
understand):

With ActiveDocument.MailMerge
.MainDocumentType = wdLetter 'type of mailmerge
.OpenHeaderSource Name:=myheader 'select the header source
.OpenDataSource Name:=filnavn, ReadOnly:=True, Connection:="" 'open data
'source
End With

' executes mailmerge
Set myMerge = ThisDocument.MailMerge 'ActiveDocument.MailMerge
If myMerge.State = wdMainAndDataSource Then myMerge.Execute


:
A

 

[Peter Jamieson]

The main problem is that although you can open the data source manually and
specify "Excel via DDE", when you open the data source file using VBA code,
there is no way to specify "use Excel via DDE" /unless/ the file extension
of the filename you are opening (filnavn in your code) uniquely identifies
the file as an Excel file. Neither .txt nor .dif will do. But in fact it is
enough to rename the text file to a .xls file. e.g., here I used something
like:

Dim myheader As String
Dim filnavn As String
myheader="C:\w\myheader.txt"
filnavn="C:\w\mydifdata.xls"
With ActiveDocument.MailMerge
' Unlink the header and data source
.MainDocumentType = wdNotAMergeDocument

.MainDocumentType = wdFormLetters

.OpenHeaderSource _
Name:=myheader

' You probably do not need the SQLStatement parameter.
' wdMergeSubType2000 makes the method follow the old
' Word 2000 code path - in that case the file extension .xls
' says "use Excel", the lack of an ODBC connection string in
' "Connection" says "use DDE", and "Entire Spreadsheet"
' specifies the range. It is possible that if you are not using
' an English Language version of Office that you may need to
' change "Entire Spreadsheet" to match the language version you
' are using

.OpenDataSource _
Name:=filnavn, _
Connection:="Entire Spreadsheet", _
SubType:=wdMergeSubTypeWord2000

' Do it
.Destination = wdSendToNewDocument
.Execute
End With

I may post a bit more, but that will have to do for now.

Peter Jamieson

 

[bilisa]

It worked - thanks a lot

The main problem is that although you can open the data source manually and
specify "Excel via DDE", when you open the data source file using VBA code,
there is no way to specify "use Excel via DDE" /unless/ the file extension
of the filename you are opening (filnavn in your code) uniquely identifies
the file as an Excel file. Neither .txt nor .dif will do. But in fact it is
enough to rename the text file to a .xls file. e.g., here I used something
like:

Dim myheader As String
Dim filnavn As String
myheader="C:\w\myheader.txt"
filnavn="C:\w\mydifdata.xls"
With ActiveDocument.MailMerge
' Unlink the header and data source
.MainDocumentType = wdNotAMergeDocument

.MainDocumentType = wdFormLetters

.OpenHeaderSource _
Name:=myheader

' You probably do not need the SQLStatement parameter.
' wdMergeSubType2000 makes the method follow the old
' Word 2000 code path - in that case the file extension .xls
' says "use Excel", the lack of an ODBC connection string in
' "Connection" says "use DDE", and "Entire Spreadsheet"
' specifies the range. It is possible that if you are not using
' an English Language version of Office that you may need to
' change "Entire Spreadsheet" to match the language version you
' are using

.OpenDataSource _
Name:=filnavn, _
Connection:="Entire Spreadsheet", _
SubType:=wdMergeSubTypeWord2000

' Do it
.Destination = wdSendToNewDocument
.Execute
End With

I may post a bit more, but that will have to do for now.

Peter Jamieson