Mail merge: Excel to Word: How to change individual Names when splitting merged file

MistaMista

New Member
Joined
May 18, 2021
Messages
14
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
Hello,
so I have a word docuement I have populated with names, dates, contract number etc. from excel using the following steps as described in this link , and its all good and merged to one massive word document.
I then used the VBA Code below to split each document into two pages. And it works.
Problem is: Each file saves as Form Letter11Form letters1 Form Letter12Form letters1 Form Letter11Form letters1 ......... (The tile name of the large merged one is Form Letters1 btw)

Is there any way I can change the VBA to return an individual name for each file?
I would like it to return Letter <<NAME>> <<CONTRACT NUMBER>>

Its 190 files, hence why it is a big job to do it manually.

Here is the VBA I used to split, and link to the second steps I followed:


Sub DocumentSplitter()
Dim xDoc As Document, xNewDoc As Document
Dim xSplit As String, xCount As Long, xLast As Long
Dim xRngSplit As Range, xDocName As String, xFileExt As String
Dim xRegEx As RegExp
Dim xPageCount As Integer
Dim xShell As Object, xFolder As Object, xFolderItem As Object
Dim xFilePath As String
On Error Resume Next
Set xDoc = Application.ActiveDocument
Set xShell = CreateObject("Shell.Application")
Set xFolder = xShell.BrowseforFolder(0, "Select a Folder:", 0, 0)
If TypeName(xFolder) = "Nothing" Then Exit Sub
Set xFolderItem = xFolder.Self
xFilePath = xFolderItem.Path & "\"
Application.ScreenUpdating = False
Set xNewDoc = Documents.Add(Visible:=False)
xDoc.Content.WholeStory
xDoc.Content.Copy
xNewDoc.Content.PasteAndFormat wdFormatOriginalFormatting
With xNewDoc
xPageCount = .ActiveWindow.Panes(1).Pages.Count
L1: xSplit = InputBox("The document contains " & xPageCount & " pages." & _
vbCrLf & vbCrLf & " Please enter the page count you want to split:", "Kutools for Word", xSplit)
If Len(Trim(xSplit)) = 0 Then Exit Sub
Set xRegEx = New RegExp
With xRegEx
.MultiLine = False
.Global = True
.IgnoreCase = True
.Pattern = "[^0-9]"
End With
If xRegEx.Test(xSplit) = True Then
MsgBox "Please enter the page number:", vbInformation, "Kutools for Word"
Exit Sub
End If
If VBA.Int(xSplit) >= xPageCount Then
MsgBox "The number is greater than the document number." & vbCrLf & "Please re-enter", vbInformation, "Kutools for Word"
GoTo L1
End If
xDocName = xDoc. Name
xFileExt = VBA.Right(xDocName, Len(xDocName) - InStrRev(xDocName, ".") + 1)
xDocName = Left(xDocName, InStrRev(xDocName, ".") - 1) & "_"
xFilePath = xFilePath & xDocName
For xCount = 0 To Int(xPageCount / xSplit)
xPageCount = .ActiveWindow.Panes(1).Pages.Count
If xPageCount > xSplit Then
xLast = xSplit
Else
xLast = xPageCount
End If
Set xRngSplit = .GoTo(What:=wdGoToPage, Name:=xLast)
Set xRngSplit = xRngSplit.GoTo(What:=wdGoToBookmark, Name:="\page")
xRngSplit.Start = .Range.Start
xRngSplit.Cut
Documents.Add
Selection.Paste
ActiveDocument.SaveAs FileName:=xFilePath & xCount + 1 & xFileExt, AddToRecentFiles:=False
ActiveWindow.Close
Next xCount
Set xRngSplit = Nothing
xNewDoc.Close wdDoNotSaveChanges
Set xNewDoc = Nothing
End With
Application.ScreenUpdating = True
End Sub
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top