Word Merge to Individual PDF with file name from data file

[oconnod]

About a year ago I used code like the one below to create individual pdf files from a merged Word document that had a unique file name for each of the output pdf files based on a field in the data file.

Can't get it to work. Any guidance would be appreciated.

Sub Merge_To_Individual_Files()
Application.ScreenUpdating = False
Dim strFolder As String, StrName As String, MainDoc As Document, i As Long, j As Long
Set MainDoc = ActiveDocument
With MainDoc
strFolder = .Path & Application.PathSeparator
For i = 1 To .MailMerge.DataSource.RecordCount
With .MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = i
.LastRecord = i
.ActiveRecord = i
If Trim(.DataFields("File_Name")) = "" Then Exit For
StrName = .DataFields("File_Name")
End With
.Execute Pause:=False
End With
For j = 1 To 255
Select Case j
Case 1 To 31, 33, 34, 37, 42, 44, 46, 47, 58 - 63, 91 - 93, 96, 124, 147, 148
StrName = Replace(StrName, Chr(j), "")
End Select
Next
StrName = Trim(StrName)
With ActiveDocument
'.SaveAs FileName:=\\houhome\~oconnod\My Documents\Test & Test 2 5 One Plan BUIP Template & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False '
.Close SaveChanges:=False
End With
Next i
End With
Application.ScreenUpdating = True
End Sub

 

[Macropod]

The code appears to have been copied from either:
Mailmerge Tips & Tricks
or:
Word Mailmerge Tips & Tricks | Windows Secrets Lounge
but simply telling us "Can't get it to work" is hardly likely to lead to a resolution of the problem. Besides which, you've comment-out the line that actually does the saving and the path you've specified on that line also looks unlikely to be a valid one and, even if it were, every file except the last would be overwritten as the merge executes - because you're saving them all with the same filename.

When posting code, please use the code tags, indicated by the # button on the posting menu.

 

[oconnod]

Hi,

Thank you for your response, sincerely appreciate your help. I'll try using the # for the code.

I have added back the SaveAs code but not sure if I should change the StrPat & StrName to the actual name of the Word document with the merged letter or leave as code. Hoping for the new pdf file to be named based on the content of the .DataFields (File_Name) on line 15. After I previewed the results in the Word template, I ran the macro and got an error "Can't execute code in break mode". The new content of the copied macro is:

Sub Merge_To_Individual_Files()
Application.ScreenUpdating = False
Dim strFolder As String, StrName As String, MainDoc As Document, i As Long, j As Long
Set MainDoc = ActiveDocument
With MainDoc
  strFolder = .Path & Application.PathSeparator
  For i = 1 To .MailMerge.DataSource.RecordCount
    With .MailMerge
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
        If Trim(.DataFields("File_Name")) = "" Then Exit For
        StrName = .DataFields("File_Name")
      End With
      .Execute Pause:=False
    End With
    For j = 1 To 255
      Select Case j
        Case 1 To 31, 33, 34, 37, 42, 44, 46, 47, 58 - 63, 91 - 93, 96, 124, 147, 148
        StrName = Replace(StrName, Chr(j), "")
      End Select
    Next
    StrName = Trim(StrName)
    With ActiveDocument
    '.SaveAs FileName:=StrPath & StrName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
      ' and/or:
.SaveAs FileName:=StrPath & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
.Close SaveChanges:=False
  End With
  Next i
End With
Application.ScreenUpdating = True
End Sub

Thank you in advance for your help.

 

[Macropod]

Try:

Sub Merge_To_Individual_Files()
Application.ScreenUpdating = False
Dim StrFolder As String, StrName As String, MainDoc As Document, i As Long, j As Long
Set MainDoc = ActiveDocument
With MainDoc
  StrFolder = .Path & Application.PathSeparator
  For i = 1 To .MailMerge.DataSource.RecordCount
    With .MailMerge
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
        If Trim(.DataFields("File_Name")) = "" Then Exit For
        StrName = .DataFields("File_Name")
      End With
      .Execute Pause:=False
    End With
    For j = 1 To 255
      Select Case j
        Case 1 To 31, 33, 34, 37, 42, 44, 46, 47, 58 - 63, 91 - 93, 96, 124, 147, 148
        StrName = Replace(StrName, Chr(j), "")
      End Select
    Next
    StrName = Trim(StrName)
    With ActiveDocument
      .SaveAs FileName:=StrFolder & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
      .Close SaveChanges:=False
    End With
  Next i
End With
Application.ScreenUpdating = True
End Sub

 

[oconnod]

Thank you very much for the response. I tried and some progress as at least the macro tried to run. I got an error message "Can't execute code in break mode"

 

[Macropod]

Why are you in break mode?

 

[oconnod]

Not sure how you get in break mode.

 

[Macropod]

Probably because you have another macro that has crashed. See: https://support.microsoft.com/en-us/kb/177828

 

[oconnod]

I'm not sure I'm running the macro at the right time. I have my Word document template with the inserted fields and my Excel file with the data. I select the preview in the Word document and then run the macro. Is that right?

 

[oconnod]

You are the best! It worked! I started over using my home computer instead of work computer and just used a few fields. Now to see if I can do it again.....

I'm very grateful for your help and I owe you!

Thank you for your time.