asked on

Macro mail merge in WORD 2013

I am using an old macro to run a mail merge for a contract. Its a simple csv file merging in to a word doc with designated fields. Sometimes the mail merge works and others not, but I always get the same message of RECORD 1 CONTAINED TOO FEW DATA FIELDS

I am using a comma as the field delimiter. Below is the macro I am using and the actual data file attached. Please note all data is dummy

bodnew Macro
' Macro recorded 26/08/99 by Danielm
'
Dim ans
WordBasic.Shell "c:\WM\contracts\B-Clet.bat"
ans = WordBasic.MsgBox("Merge Bod Contracts?", 3)
If ans = 0 Or ans = 1 Then GoTo Bye

Documents.Open FileName:="c:\wm\contracts\sched.DOC", ConfirmConversions:=False, ReadOnly _
:=True, AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:= _
"", Revert:=False, WritePasswordDocument:="", WritePasswordTemplate:="", _
Format:=wdOpenFormatAuto
With ActiveDocument.MailMerge
.OpenDataSource Name:="c:\wm\contracts\b-Clet.CSV"
.Destination = wdSendToNewDocument
.Execute
End With
Windows(2).Activate
ActiveDocument.Close wdDoNotSaveChanges

Bye:

End Sub
b-clet.CSV

GrahamSkan

While the code does show its age, it should still work.

From what you have posted, we can't eliminate the batch file and/or its input data as the cause. You say that it only happens occasionally. When is fails, are you sure that the CSV file is properly formed?

Ben Campbell

ASKER

Thanks Graham

The batch file just runs a command that produces the data file in csv format. Therefore the batch file is irrelevant. I get the error message every time it runs, sometimes it produces the merged document and others it doesn't. I attached the csv file and have done again if you would like to look at it. Thanks for your help so far.
b-clet.CSV

ASKER CERTIFIED SOLUTION

GrahamSkan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ben Campbell

ASKER

Thanks again for your help Graham. I don't think its a timing issue because I can create the csv file in the folder without needing the batch file but I still get the same issue. I tried your code and it runs, first time it worked, second time it didn't but both times I still getting message Record 1 contained too few data fields. Doesn't this actually pop us at the CSV file ? I cant see anything wrong with it thought ?

Thanks

Jon

GrahamSkan

Does it work every time if you run the merge manually?

Ben Campbell

ASKER

It works absolutely fine if if run it through the mail merge wizard. That's what i don't understand.

GrahamSkan

I don't know why it is, either.

It might help to pin it down if the code is simplified as much as possible, to:

Sub bodnew1()
    Dim doc As Document
    
    Set doc = Documents.Open("c:\wm\contracts\sched.DOC")
    With doc.MailMerge
        .Destination = wdSendToNewDocument
        .Execute
    End With
    doc.Close wdDoNotSaveChanges
End Sub

Open in new window

This assumes that the main document is already set up with the correct datasource.

Ben Campbell

ASKER

Thanks Graham

Yes still get the same error with your simplified code above.

Debug points at this line ?

Would it help if you had a the shed.doc ?

Cheers

Jon

Ben Campbell

ASKER

Sorry for the line

Set doc = Documents.Open("c:\wm\contracts\sched.DOC")

GrahamSkan

Not quite sure that I understand that last comment. You can change the line to if the document is already open.

Sub bodnew2()
    Dim doc As Document
    
    Set doc = ActiveDocument
    With doc.MailMerge
        .Destination = wdSendToNewDocument
        .Execute
    End With
    doc.Close wdDoNotSaveChanges
End Sub

Open in new window

GrahamSkan

Not sure what you mean by a 'shed' doc.

GrahamSkan

Ah I see the document name is 'sched.doc'.

Yes it will. With it we should be able to distinguish between a system and a document fault.

If you are worried about it being generally viewable, you can attach it in a message to me.

Ben Campbell

ASKER

Hi Graham

Have messaged you the doc

Thanks

Jon

GrahamSkan

Sorry Jon, That didn't work. You have to browse for the file and then upload it

Ben Campbell

ASKER

Should have it now appologies

GrahamSkan

Just to keep you in the picture.
I cannot reproduce the problem, but I have been trying to use the document in other mail merge contexts and it doesn't work properly a far as I am concerned. It lets me browse for and select a datasource, but it then reports that it cannot find the datasource that has just been selected.

Ben Campbell

ASKER

Thanks Graham. When I use the mailmerge wizard I select the doc and then the data source and it works absolutely fine.

Please don't let me waste anymore of your time. I have no idea why I keep getting this error message in the macro

cheers

Jon

Ben Campbell

ASKER

Thanks Graham have found the problem. The data file being created didn't have as many field headers as the data being exported. A programming issue on our internal system. I would like to thank you for your time and the new macro which you provided which is working very well compared to the old one. I can;t thank you enough.

All the best

Jon

GrahamSkan

I did a lot of head-scratching, so thanks for the explanation.