1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'VBA Macros' started by DME, May 14, 2018.

  1. DME

    DME Member

    Messages:
    40
    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 (TEXT):

    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
     
  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,573
    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?
  3. DME

    DME Member

    Messages:
    40
    @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?
  4. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,573
    Do you still have the code that did the mail merge ?
  5. DME

    DME Member

    Messages:
    40
    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?
  6. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,573
    I look on a Word Forum
    They may have such code to do what you want
  7. Dimmai Badsha

    Dimmai Badsha New Member

    Messages:
    1
    U need to have merge field in your footer. That field will be the unique filename when autosaving the pdf's

Share This Page