asked on

VBA for MS Word 2007 function to convert semicolon separated string in mail merge field to numbered list.

In recent question experts-exchange on similar question for MS access report, I got the following solution to create numbered list from a field with string containing semicolon separated statements.
====================================================================
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim arrRemarks() As String, strRemark As String

arrRemarks() = Split(Me.CollegeRemark, ";")
For i = 0 To UBound(arrRemarks)
strRemark = strRemark & Trim(i + 1) & ". " & Trim(arrRemarks(i)) & vbCrLf
Next

Me.txtremark = strRemark
End Sub
=================================================================
If intend to mail merge the field to MS word, is there a way to create such numbered list.
Can we write a similar code in VBA for MS word?

Jeffrey Coachman

This is why many mail merge source documents are Excel or Access files.
Then this is easy.

Please wait for an expert to help with the Word code.

If it were me, (and I am understanding your issue here, and your merge needs are fairly basic...) I would simply use Excel to create the Source Doc, and just autofill in the sequential numbers...

But perhaps more info on the Merge is needed...

JeffCoachman

GrahamSkan

Some very similar code would work in Word, however you could use Word's automatic list numbering facility instead of creating the numbers if you want the list to appear in a document.

It isn't clear how this would tie in with Mail Merge

Srinivas Mantha

ASKER

The merge field from access database in the word document is

<<collegeremark>>

when merged, the merged field for a particular record is for e.g.
statement1;statement3;statement5;statement8.
For another record, for e.g. it could be
statement2;statement3;statement4.
For another record, for e.g. it could be
statement1;statement3;statement5;statement2.

What I want in the final finished word document is these semicolon separated statements should be listed one below the orther and numbered.
I have already presented the the solution code I got for access report
I want to know whether we can use similar code in VBA for word to get what I desired

GrahamSkan

Here is some VBA code that works in a UserForm.. It can be in any application that hosts VBA, including Word.

 Private Sub CommandButton1_Click()
    Dim arrRemarks() As String
    Dim strRemark As String
    Dim i As Integer

    arrRemarks() = Split(Me.TextBox1, ";")
    
    For i = 0 To UBound(arrRemarks)
          strRemark = strRemark & Trim(i + 1) & ". " & Trim(arrRemarks(i)) & vbCrLf
    Next
    
    Me.TextBox2 = strRemark
   
End Sub

Open in new window

Word's Mail Merge requires the DataSource to have a table-like structure. It can be an database table or flat query, a spreadsheet, a CSV file or even a table in a separate Word document.

Experiment with the Mail Merge wizard (Mailings tab, Start Mail Merge group, Start Mail Merge button, Step by Step Mail Merge Wizard...) to get a good understanding of how it works.

Srinivas Mantha

ASKER

I have working knowledge with word mailmerge with data obtained from MS access database. I have routinely done for over years. In the present, I have more than 500 records to mail merge. The word user form is not taking merge field. It is difficult to press the command button 500 times.
What is the solution

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

Srinivas Mantha

ASKER

Thank you for the excellent and accurate solution for the problem