Getting custom filename using SplitMergedDocument()

excelenthusiast1 · #5 11-24-2024, 03:50 PM

Quote:

Originally Posted by macropod

As per the discussion on your now-deleted post at StackOverflow, you still haven't said where the 'custom filename' is supposed to come from.

Moreover, the code you've posted here isn't what I directed you to. The code I directed you to is in the Send Mailmerge Output to Individual Files topic in the Mailmerge Tips and Tricks 'Sticky' thread at the top of the Mailmerge forum: https://www.msofficeforums.com/mail-...ps-tricks.html
It is of fundamental importance to use the correct approach because, once your output document has been created, it lacks any connection to the Excel datafile to use for data retrieval.

Thanks so much Macropod, I deleted the Stackerflow because your code was better suited and thought it'd create confusion. I'm a bit new to this.

I didn't communicate this well and I apologise. I'm trying to split the document by page (Using section breaks) and then use the 'Afilename' column in the excel file to specify the name of each FileName of the doc and PDF outputs.

I have used the 'send mailmerge output to individual files' however it doesn't split the files, it creates a copy of the entire document for each export. I edited the file to suit my excel by commenting out the trim last name and datafields first name part. How do I get it to split the files and name them using the Afilename column (1st Page uses first row of 'Afilename', 2nd page uses second row of 'Afilename' etc.)?

Code:

Sub Merge_To_Individual_Files()
' Sourced from: https://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html
Application.ScreenUpdating = False
Dim StrFolder As String, StrName As String, MainDoc As Document, i As Long, j As Long
Const StrNoChr As String = """*./\:?|"
Set MainDoc = ActiveDocument
With MainDoc
  StrFolder = .Path & "\"
  With .MailMerge
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    On Error Resume Next
    For i = 1 To .DataSource.RecordCount
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
        'If Trim(.DataFields("Last_Name")) = "" Then Exit For
        'StrFolder = .DataFields("Folder") & "\"
        StrName = .DataFields("AFilename") & "_" '& .DataFields("First_Name")
      End With
      On Error GoTo NextRecord
      .Execute Pause:=False
      For j = 1 To Len(StrNoChr)
        StrName = Replace(StrName, Mid(StrNoChr, j, 1), "_")
      Next
      StrName = Trim(StrName)
      With ActiveDocument
        'Add the name to the footer
        '.Sections(1).Footers(wdHeaderFooterPrimary).Range.InsertBefore StrName
        .SaveAs FileName:=StrFolder & StrName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
        ' and/or:
        .SaveAs FileName:=StrFolder & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
        .Close SaveChanges:=False
      End With
NextRecord:
    Next i
  End With
End With
Application.ScreenUpdating = True
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Custom Style Sets Stopped Working on Custom Template after Recent Microsoft Update	CynthiaKPollard	Word	6	12-20-2021 08:37 PM
MS PUB: Create multiple JPGs with containing custom text and custom file names	Skyfawn	Mail Merge	7	08-02-2021 02:45 AM
filename field not displaying correct filename when that name starts with #	plrsmith	Word	1	07-06-2018 03:10 AM
Filename	UnlimitedPower	Word VBA	1	08-19-2016 12:22 AM
Filename in footer	Dixon	Word	3	09-24-2009 09:12 AM