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

[Amateur_me]

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.

2838428385

[macropod]

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-merge/21803-mailmerge-tips-tricks.html

[Amateur_me]

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.

[macropod]

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.

[Amateur_me]

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.

[gmayor]

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

[macropod]

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

[Amateur_me]

Dear Paul,

Yurray!!!!!!! that work absolutely the way i wanted omg this is saving me hours of work. So grateful to you.

[Amateur_me]

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 :banghead:

Please find attached updated sample for u to use.

[macropod]

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"

[Amateur_me]

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

[macropod]

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.

[Amateur_me]

hi Paul,

Thank you for the response, please find attached.

[macropod]

That line in your code is incomplete! Please see the code I posted.

[Amateur_me]

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.

[macropod]

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 -

That line in your code is incomplete! Please see the code I posted.