Solved

Macro mail merge in WORD 2013

Posted on 2015-02-02
19
360 Views
Last Modified: 2016-02-11
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
0
Comment
Question by:James_22b
  • 10
  • 9
19 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40583987
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?
0
 

Author Comment

by:James_22b
ID: 40584014
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
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 40585215
Sorry. I ignored the Shell command because I usually stick to VBA (for reasons like this). The problem is that Shell merely starts the process and does not wait for the process to finish before handing control back to the VBA program. There is therefore a chance that a quick answer to the MsgBox will allow the merge to start before the CSV file has been fully created.
A built-in delay might work, but the rigorous answer is to use Windows API functions, including WaitForSingleObject. This code is from this Microsoft article:
https://support.microsoft.com/kb/129796?wa=wsignin1.0
Option Explicit

Private Type STARTUPINFO
    cb As Long
    lpReserved As String
    lpDesktop As String
    lpTitle As String
    dwX As Long
    dwY As Long
    dwXSize As Long
    dwYSize As Long
    dwXCountChars As Long
    dwYCountChars As Long
    dwFillAttribute As Long
    dwFlags As Long
    wShowWindow As Integer
    cbReserved2 As Integer
    lpReserved2 As Long
    hStdInput As Long
    hStdOutput As Long
    hStdError As Long
End Type

Private Type PROCESS_INFORMATION
    hProcess As Long
    hThread As Long
    dwProcessID As Long
    dwThreadID As Long
End Type

Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal _
   hHandle As Long, ByVal dwMilliseconds As Long) As Long

Private Declare Function CreateProcessA Lib "kernel32" (ByVal _
   lpApplicationName As String, ByVal lpCommandLine As String, ByVal _
   lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, _
   ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _
   ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As String, _
   lpStartupInfo As STARTUPINFO, lpProcessInformation As _
   PROCESS_INFORMATION) As Long

Private Declare Function CloseHandle Lib "kernel32" _
   (ByVal hObject As Long) As Long

Private Declare Function GetExitCodeProcess Lib "kernel32" _
   (ByVal hProcess As Long, lpExitCode As Long) As Long

Private Const NORMAL_PRIORITY_CLASS = &H20&
Private Const INFINITE = -1&

Public Function ExecCmd(cmdline$) As Long
    Dim proc As PROCESS_INFORMATION
    Dim start As STARTUPINFO
    Dim ret&
    
    ' Initialize the STARTUPINFO structure:
    start.cb = Len(start)
    
    ' Start the shelled application:
    ret& = CreateProcessA(vbNullString, cmdline$, 0&, 0&, 1&, _
    NORMAL_PRIORITY_CLASS, 0&, vbNullString, start, proc)
    
    ' Wait for the shelled application to finish:
    ret& = WaitForSingleObject(proc.hProcess, INFINITE)
    Call GetExitCodeProcess(proc.hProcess, ret&)
    Call CloseHandle(proc.hThread)
    Call CloseHandle(proc.hProcess)
    ExecCmd = ret&
End Function

Sub bodnew()
    Dim retval As Long
    Dim doc As Document
    
    retval = ExecCmd("c:\WM\contracts\B-Clet.bat")
    
    If MsgBox("Merge Bod Contracts?", vbYesNo) = vbYes Then
        Set doc = Documents.Open("c:\wm\contracts\sched.DOC")
        With doc.MailMerge
            .OpenDataSource Name:="c:\WM\contracts\B-Clet.CSV"
            .Destination = wdSendToNewDocument
            .Execute
        End With
        doc.Close wdDoNotSaveChanges
    End If
End Sub

Open in new window

0
 

Author Comment

by:James_22b
ID: 40585805
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
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40585816
Does it work every time if you run the merge manually?
0
 

Author Comment

by:James_22b
ID: 40585875
It works absolutely fine if if run it through the mail merge wizard. That's what i don't understand.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40585890
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.
0
 

Author Comment

by:James_22b
ID: 40585917
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
0
 

Author Comment

by:James_22b
ID: 40585942
Sorry for the line

Set doc = Documents.Open("c:\wm\contracts\sched.DOC")
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40586004
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

0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40586010
Not sure what you mean by a 'shed' doc.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40586013
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.
0
 

Author Comment

by:James_22b
ID: 40586020
Hi Graham

Have messaged you the doc

Thanks

Jon
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40586040
Sorry Jon, That didn't work. You have to browse for the file and then upload it
0
 

Author Comment

by:James_22b
ID: 40586041
Should have it now appologies
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40586555
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.
0
 

Author Comment

by:James_22b
ID: 40586666
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
0
 

Author Closing Comment

by:James_22b
ID: 40588809
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
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40589060
I did a lot of head-scratching, so thanks for the explanation.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Shortcuts in Word Just the other day I had a training for Microsoft and they wanted me to show how well the new Windows and Office behaved on a touch device, which by the way is great, but it was only then that I realized that using keyboard shortc…
A few years ago I was very much a beginner at VBA, and that very much remains the case today.  I'll do my best to explain things as I go in the hope that other beginners can follow.  If you just want to check out a tool that creates a Select Case fu…
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.

758 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

21 Experts available now in Live!

Get 1:1 Help Now