Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-23-2024, 11:43 PM
excelenthusiast1 excelenthusiast1 is offline Getting custom filename using SplitMergedDocument() Windows 11 Getting custom filename using SplitMergedDocument() Office 2016
Novice
Getting custom filename using SplitMergedDocument()
 
Join Date: Nov 2024
Posts: 8
excelenthusiast1 is on a distinguished road
Default Getting custom filename using SplitMergedDocument()

Hi everyone,

Macropod linked me this site and people seem to be really friendly here.

I'm trying to use the SplitMergedDocument() function to split a bunch of word docs into pdf files however I wanted it to use a custom filename for each page either in some custom part of the MS word doc that doesn't appear on the page or referencing a specific column title name and in a specific sheet within an excel file. Can someone help? Thanks so much

Here's the code to make it easier for anyone wanting to help

Code:
Sub SplitMergedDocument()
' Sourced from: https://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html
Application.ScreenUpdating = False
Dim i As Long, j As Long, k As Long, StrTxt As String
Dim Rng As Range, Doc As Document, HdFt As HeaderFooter
Const StrNoChr As String = """*./\:?|"
j = InputBox("How many Section breaks are there per record?", "Split By Sections", 1)
With ActiveDocument
   ' Process each Section
  For i = 1 To .Sections.Count - 1 Step j
    With .Sections(i)
       '*****
       ' Get the 1st paragraph's text
      StrTxt = Split(.Range.Paragraphs(1).Range.Text, vbCr)(0)
      For k = 1 To Len(StrNoChr)
        StrTxt = Replace(StrTxt, Mid(StrNoChr, k, 1), "_")
      Next
       ' Construct the destination file path & name
      StrTxt = ActiveDocument.Path & "\" & StrTxt
       '*****
       ' Get the whole Section
      Set Rng = .Range
      With Rng
        If j > 1 Then .MoveEnd wdSection, j - 1
         'Contract the range to exclude the Section break
        .MoveEnd wdCharacter, -1
         ' Copy the range
        .Copy
      End With
    End With
     ' Create the output document
    Set Doc = Documents.Add(Template:=ActiveDocument.AttachedTemplate.FullName, Visible:=False)
    With Doc
       ' Paste contents into the output document, preserving the formatting
      .Range.PasteAndFormat (wdFormatOriginalFormatting)
       ' Delete trailing paragraph breaks & page breaks at the end
      While .Characters.Last.Previous = vbCr Or .Characters.Last.Previous = Chr(12)
        .Characters.Last.Previous = vbNullString
      Wend
       ' Replicate the headers & footers
      For Each HdFt In Rng.Sections(j).Headers
        .Sections(j).Headers(HdFt.Index).Range.FormattedText = HdFt.Range.FormattedText
      Next
      For Each HdFt In Rng.Sections(j).Footers
        .Sections(j).Footers(HdFt.Index).Range.FormattedText = HdFt.Range.FormattedText
      Next
       ' Save & close the output document
      .SaveAs FileName:=StrTxt & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
       ' and/or:
      .SaveAs FileName:=StrTxt & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
      .Close SaveChanges:=False
    End With
  Next
End With
Set Rng = Nothing: Set Doc = Nothing
Application.ScreenUpdating = True
End Sub

Reply With Quote
  #2  
Old 11-24-2024, 12:55 AM
gmayor's Avatar
gmayor gmayor is offline Getting custom filename using SplitMergedDocument() Windows 10 Getting custom filename using SplitMergedDocument() Office 2019
Expert
 
Join Date: Aug 2014
Posts: 4,144
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

You may find Merge and Split useful. This provides several ways of naming the documents.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 11-24-2024, 02:05 AM
macropod's Avatar
macropod macropod is offline Getting custom filename using SplitMergedDocument() Windows 10 Getting custom filename using SplitMergedDocument() Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,467
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #4  
Old 11-24-2024, 03:50 PM
excelenthusiast1 excelenthusiast1 is offline Getting custom filename using SplitMergedDocument() Windows 11 Getting custom filename using SplitMergedDocument() Office 2016
Novice
Getting custom filename using SplitMergedDocument()
 
Join Date: Nov 2024
Posts: 8
excelenthusiast1 is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
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
Reply With Quote
  #5  
Old 11-24-2024, 08:16 PM
macropod's Avatar
macropod macropod is offline Getting custom filename using SplitMergedDocument() Windows 10 Getting custom filename using SplitMergedDocument() Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,467
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by excelenthusiast1 View Post
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
That claim is pure nonsense. The Merge_To_Individual_Files macro produces a separate output file for each record. One has to wonder whether you've even added it to your mailmerge main document and run it on that document - as per the instructions in the linked thread.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #6  
Old 11-24-2024, 09:15 PM
excelenthusiast1 excelenthusiast1 is offline Getting custom filename using SplitMergedDocument() Windows 11 Getting custom filename using SplitMergedDocument() Office 2016
Novice
Getting custom filename using SplitMergedDocument()
 
Join Date: Nov 2024
Posts: 8
excelenthusiast1 is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
That claim is pure nonsense. The Merge_To_Individual_Files macro produces a separate output file for each record. One has to wonder whether you've even added it to your mailmerge main document and run it on that document - as per the instructions in the linked thread.
Apologies, I'm not explaining myself right I don't think. This is not a mailmerge, this is a document with 15 pages with section breaks between each page, I'm simply using the mailmerge feature for retrieving the filename outputs only.

15 pages in this document, need to split them into 15 PDF files (like the SplitMergedDocument() function) and use the mailmerge function to name the filenames.

Am I misunderstanding the purpose of Merge_to_Individual_Files?
Reply With Quote
  #7  
Old 11-24-2024, 09:17 PM
macropod's Avatar
macropod macropod is offline Getting custom filename using SplitMergedDocument() Windows 10 Getting custom filename using SplitMergedDocument() Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,467
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

The Merge_to_Individual_Files macro goes in your mailmerge main document and, when run, automatically names the individual output file for each record with the filename parameters you specify. No separate document is created to need splitting afterwards. Read the instructions.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #8  
Old 11-24-2024, 09:28 PM
excelenthusiast1 excelenthusiast1 is offline Getting custom filename using SplitMergedDocument() Windows 11 Getting custom filename using SplitMergedDocument() Office 2016
Novice
Getting custom filename using SplitMergedDocument()
 
Join Date: Nov 2024
Posts: 8
excelenthusiast1 is on a distinguished road
Default

Seems like it's no different to the code I was using for the other document. I'll have to pay someone to create a custom solution then. Thanks for your help. Although the splitting function alone takes one step out of it which is useful.
Reply With Quote
  #9  
Old 11-24-2024, 09:30 PM
excelenthusiast1 excelenthusiast1 is offline Getting custom filename using SplitMergedDocument() Windows 11 Getting custom filename using SplitMergedDocument() Office 2016
Novice
Getting custom filename using SplitMergedDocument()
 
Join Date: Nov 2024
Posts: 8
excelenthusiast1 is on a distinguished road
Default

Quote:
Originally Posted by gmayor View Post
You may find Merge and Split useful. This provides several ways of naming the documents.
Thanks Graham, I was looking for a macro that I can inspect without using an exe especially if I need to place it on other people's PCs. It also removes the need to train people on macros and another app to non-technical users.
Reply With Quote
  #10  
Old 11-24-2024, 09:34 PM
macropod's Avatar
macropod macropod is offline Getting custom filename using SplitMergedDocument() Windows 10 Getting custom filename using SplitMergedDocument() Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,467
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

The Merge_to_Individual_Files macro in the link is really simple to use - if you follow the instructions.

You are creating a problem for yourself if to expect to split a merged output file, because that file has no connection to the data source and no automatic way of identifying whatever records you expect to find the naming data in. You can't even store those data in a hidden form in bookmarks because any bookmarks get deleted in a mailmerge.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #11  
Old 11-24-2024, 10:02 PM
excelenthusiast1 excelenthusiast1 is offline Getting custom filename using SplitMergedDocument() Windows 11 Getting custom filename using SplitMergedDocument() Office 2016
Novice
Getting custom filename using SplitMergedDocument()
 
Join Date: Nov 2024
Posts: 8
excelenthusiast1 is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
The Merge_to_Individual_Files macro in the link is really simple to use - if you follow the instructions.

You are creating a problem for yourself if to expect to split a merged output file, because that file has no connection to the data source and no automatic way of identifying whatever records you expect to find the naming data in. You can't even store those data in a hidden form in bookmarks because any bookmarks get deleted in a mailmerge.
Yeah it's probably not the solution I'm looking for. I noticed the SplitMergedDocument() uses the H1 or Main headings as the filenaming convention for each page, is there another place I could potentially store different values and use those for each page?
Reply With Quote
  #12  
Old 11-24-2024, 10:39 PM
macropod's Avatar
macropod macropod is offline Getting custom filename using SplitMergedDocument() Windows 10 Getting custom filename using SplitMergedDocument() Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,467
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

If you want any more help on this, you're going to have to upload at least a sample copy of your Excel workbook with anonymized data and your mailmerge main document, similarly anonymized.

You can do that via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply



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
Getting custom filename using SplitMergedDocument() 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
Getting custom filename using SplitMergedDocument() Filename UnlimitedPower Word VBA 1 08-19-2016 12:22 AM
Filename in footer Dixon Word 3 09-24-2009 09:12 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:24 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft