Consulting

Results 1 to 16 of 16

Thread: Split word document save as PDF rename as per name on Excel Sheet.

  1. #1

    Split word document save as PDF rename as per name on Excel Sheet.

    Hi all,

    I came across this forum which is very useful. i am normally in payroll and the payroll provider send me the payslip for my company in a word document. I have around 90 payslip in one word document.

    I want a formulae to split the word rename them as the name on the payslip and also split save as PDF.

    I usually use the format split to pdf but it save as page number. Then I spend my time to rename one by one.

    I have an excel sheet name that align with payslip name. Attached for your references.

    Thanks a lot in advance.

    Payslip Test.docxTest Data sheet.xlsx

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    What you're describing is effectively a mailmerge, with the output sent to separate files. To do that with a properly-configured mailmerge main document, see Send Mailmerge Output to Individual Files in the Mailmerge Tips and Tricks page at: https://www.msofficeforums.com/mail-...ps-tricks.html
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    I get that.. i do use the mail merge output to individual document. But this document is a full 90 pages of word document that i want to split to pdf and save by name
    I have use other vba codes they do split it but it is rename by page number. I want it to be rename by name on the word. Please refer to attach word.

    Thank you again dear.

  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    The link I gave you shows how to generate the individual documents - named as you require - directly from the mailmerge, without the need to split the document afterwards.


    And, although you claim your 90-page document is the result of a mailmerge, the one you attached here most certainly is not. Unless you supply a sample document whose content is much more representative of the actual mailmerge output, all we'll be doing is wasting our time trying to help. In any event, the same link has an article titled Split Merged Output to Separate Documents. The code in that link will do as you want, but still needs to be told where to find the name to be used for thesaving.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  5. #5
    Dear Paul,

    I am sorry if i confused you previously. I meant to say i do use the codes for individual mail merge files. However this particular document i have attached this is not a mail merge. This is a sample of how i get it when i download it. Completely different document we are talking about here.

    Attached is the sample i have only included 12 pages but overall it has 90 pages.

    I just want a macro no split and save as per the name on the word without going through the mail merge.


    Please accept my apology for any confusion.

  6. #6
    The sample has 12 pages, all the same. Can we assume that the document (which bears no obvious relationship to the worksheet) has different values on each page?
    That being the case, run the following macro to replace the many and varied paragraph breaks at the end of each page with a section break. You can then split to PDF with  https://www.gmayor.com/MergeAndSplit.htm.
    Frankly it would make more sense to create a merge document from your first page and split the merge on the fly - which the above link will also facilitate, though I accept this may not be possible if you are getting the document from a download.
    Sub Macro1()
    'Graham Mayor - https://www.gmayor.com - Last updated - 01 May 2021 
    Dim orng As Range
    Dim oSection As Section
        Set orng = ActiveDocument.Range
        With orng.Find
            Do While .Execute(findText:="^13{10,}", MatchWildcards:=True)
                orng.Text = ""
                If Not orng.End = ActiveDocument.Range.End Then
                    orng.InsertBreak wdSectionBreakNextPage
                End If
                orng.Collapse 0
            Loop
        End With
    End Sub
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  7. #7
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    The problem with the document you attached is that there is no consistency between the pages as to where the content begins. On pages 1, 2, 7, & 11, 'Company Name' is in the first paragraph. On pages 3-6, & 10, it's in the 2nd paragraph. On page 8 & 12, it's in the 3rd paragraph. On page 9, it's in the 4th paragraph.

    It seems most unlikely (to me, at least), that that's how they're being generated.

    That said, for what's in your attachment:

    Sub SaveAsPDFs()
    Dim i As Long, Rng As Range
    With ActiveDocument
      With .Range.Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .Text = "[^13]@Company"
        .Replacement.Text = "^p^12Company"
        .Forward = True
        .Format = False
        .Wrap = wdFindContinue
        .MatchWildcards = True
        .Execute Replace:=wdReplaceAll
      End With
      For i = 1 To .ComputeStatistics(wdStatisticPages)
        Set Rng = .Range.GoTo(What:=wdGoToPage, Name:=i).GoTo(What:=wdGoToBookmark, Name:="\page")
        .ExportAsFixedFormat OutputFileName:=.Path & "\" & _
          Split(Rng.Paragraphs(3).Range.Text, vbTab)(2) & ".pdf", _
          ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False, _
          OptimizeFor:=wdExportOptimizeForPrint, Range:=wdExportFromTo, _
          From:=i, To:=i, Item:=wdExportDocumentContent, IncludeDocProps:=False, _
          KeepIRM:=False, CreateBookmarks:=wdExportCreateHeadingBookmarks, _
          DocStructureTags:=True, BitmapMissingFonts:=False, UseISO19005_1:=False
      Next
    End With
    End Sub
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  8. #8
    Dear Paul,

    Yurray!!!!!!! that work absolutely the way i wanted omg this is saving me hours of work. So grateful to you.
    Last edited by Amateur_me; 05-02-2021 at 10:26 PM.

  9. #9
    Hi Paul,

    Thank you for the above code again it is working fine however the business now want the word to split after the signature and save as per employee ID not name

    Please find attached updated sample for u to use.
    Attached Files Attached Files

  10. #10
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    The documents are already split after the signature line.

    For the naming, simply change:
    Split(Rng.Paragraphs(3).Range.Text, vbTab)(2) & ".pdf"
    to:
    Split(Split(Rng.Paragraphs(3).Range.Text, vbTab)(4), vbCr)(0) & ".pdf"
    Last edited by macropod; 05-24-2021 at 03:32 PM.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  11. #11
    Hi Paul,

    I am a pain. Its working fine with my test documents i have uploaded. But i still get the file not being splitting after the signature on the original file.

    Shame i cannot upload the original because of Data protection.

    However i am having this error attached can you please check.

    Best Regards.
    error.jpg

  12. #12
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    OK, lets test what you're getting as the filename. Comment-out the line:
    .ExportAsFixedFormat
    and insert before that line:
    Msgbox Split(Split(Rng.Paragraphs(3).Range.Text, vbTab)(4), vbCr)(0)
    so you can see what is actually being returned as the filename. Report back and we'll see where we can go from there.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  13. #13
    hi Paul,

    Thank you for the response, please find attached.
    Attached Images Attached Images

  14. #14
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    That line in your code is incomplete! Please see the code I posted.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  15. #15
    Hi Paul,

    I cant understand its working absolutely fine with my test data. Let me explain what i did I have added the code in a word document save it macro enable.
    I take my test data run the macro it work absolutely fine I get my data which should be 12 pages as pdf so the test is a pass.

    When i try with Live data now. I do the same copy and paste on the macro enabled word document. i run the Macro and expect to get 84 pages as pdf. what i am getting is 32 pages only.
    with the run time error message.

    i have followed all of your step but cant understand why. Which part of the code is incomplete.

  16. #16
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Quote Originally Posted by Amateur_me View Post
    i have followed all of your step but cant understand why. Which part of the code is incomplete.
    The line that is incomplete is the very line you're having trouble with... As I said -
    Quote Originally Posted by macropod View Post
    That line in your code is incomplete! Please see the code I posted.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •