Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Macro mail merge in WORD 2013

Posted on 2015-02-02
19
Medium Priority
?
443 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:Ben
[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
  • 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:Ben
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 2000 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Ben
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:Ben
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:Ben
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:Ben
ID: 40585942
Sorry for the line

Set doc = Documents.Open("c:\wm\contracts\sched.DOC")
0
 
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:Ben
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:Ben
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:Ben
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:Ben
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
Suggested Courses

636 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