Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 712
  • Last Modified:

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?
0
Srinivas Mantha
Asked:
Srinivas Mantha
  • 3
  • 3
1 Solution
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
 
GrahamSkanRetiredCommented:
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
0
 
Srinivas ManthaConsultant Anesthesiologist and Pain PhysicianAuthor Commented:
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
GrahamSkanRetiredCommented:
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.
0
 
Srinivas ManthaConsultant Anesthesiologist and Pain PhysicianAuthor Commented:
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
0
 
GrahamSkanRetiredCommented:
If you merge to a document, you can run some code like this on it after the merge
Sub PostMerge()
    Dim rngDoc As Range
    Dim rngPara As Range
    
    Do While True
        Set rngDoc = ActiveDocument.Range
        With rngDoc.Find
            .Text = ";"
            If .Execute Then
                Set rngPara = rngDoc.Paragraphs.First.Range
                    rngPara.ListFormat.ApplyListTemplateWithLevel ListTemplate:= _
                        ListGalleries(wdNumberGallery).ListTemplates(1), ContinuePreviousList:= _
                        False, ApplyTo:=wdListApplyToWholeList, DefaultListBehavior:= _
                        wdWord10ListBehavior
                With rngPara.Find
                    .Text = ";"
                    .Replacement.Text = "^p"
                    .Execute Replace:=wdReplaceAll
                End With
            Else
                Exit Do
            End If
        End With
    Loop
End Sub

Open in new window

It assumes that there are no other semicolons in the document.
0
 
Srinivas ManthaConsultant Anesthesiologist and Pain PhysicianAuthor Commented:
Thank you for the excellent and accurate  solution for the problem
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now