• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Split file into multiple documents based on footer

DME

Member
Hi all, my apologies as this is actually being used in a .Word doc, but hoping it's okay to post it here as perhaps this is something that someone may tackle in Excel.

I've created a large file using a mail merge. The large file is a combination of multiple 3pg sets of information, with each 3pg set specific to 1 employee and accompanied by a custom footer from the mail merge. My goal was to run a Macro to split that large file into each employees own 3 pages and use the footer to name each new PDF document that was generated. My code is below.

Something appears to be wrong with the ".SaveAs FileName :=fname.Text..." line of the code but I can't figure it out. Any suggestions or resources I can reference? Thanks so much in advance for your help!!

Code:
Sub NewMacroForTesting()
'
' NewMacroForTesting Macro
'
'
Dim i As Long, Source As Document, Target As Document, Letter As Range

Dim fname As Range

Set Source = ActiveDocument

With Source

    For i = 1 To .Sections.Count

        Set fname = .Sections(i).Footers(wdHeaderFooterPrimary).Range

        fname.End = fname.End - 1

        Set Letter = .Sections(i).Range

        Letter.End = Letter.End - 1

        Set Target = Documents.Add

        With Target

            .Range.FormattedText = Letter.FormattedText

            .Sections(1).Footers(wdHeaderFooterPrimary).Range.Text = ""

            .SaveAs FileName:=fname.Text & " - Contributions", FileFormat:=wdFormatPDF

            .Close wdDoNotSaveChanges

        End With

    Next i

End With
End Sub
 

Hui

Excel Ninja
Staff member
When you perform nail merge you have the option to do it as a single file or as separate files for each record

Why not redo it that way?
 

DME

Member
@Hui good question- we had done that but were unable to name each pdf when creating them. As we created over 250 PDFs, we thought we’d try this. Any thoughts on an easy way to split out the sheets and name them in the merge/macro?
 

DME

Member
Hi @Hui, I do not. A colleague of mine used the Wizard to create the merge and now has the large document containing each set of individual documents. They’re being emailed to managers and we need the file name for each to be descriptive about what each file is. Any suggestions?
 

Hui

Excel Ninja
Staff member
I look on a Word Forum
They may have such code to do what you want
 
Top