using vba to generate pdf file from word- looking for code to remove the extra blank page

[mychi11]

Dear All,

Not sure if this is the right place to ask. I am using vba code to generate a pdf file from word. When it is in word, it only consists one page but when i converted the file into pdf, it is splitting into two pages. Is there vba code that i can add in my current vba code to eliminate the extra blank page.

Here is my original vba code.

Option Explicit

Const FOLDER_SAVED As String = "S:\SA Report\"
Const SOURCE_FILE_PATH As String = "S:\SA\SA Data Master Sheet Version 2021.xlsm"

Sub TestRun()
Dim MainDoc As Document, TargetDoc As Document
Dim dbPath As String
Dim recordNumber As Long, totalRecord As Long

Set MainDoc = ActiveDocument
With MainDoc.MailMerge

'// if you want to specify your data, insert a WHERE clause in the SQL statement
.OpenDataSource Name:=SOURCE_FILE_PATH, sqlstatement:="SELECT * FROM [Sheet1$]"

totalRecord = .DataSource.RecordCount

For recordNumber = 1 To totalRecord

With .DataSource
.ActiveRecord = recordNumber
.FirstRecord = recordNumber
.LastRecord = recordNumber
End With

.Destination = wdSendToNewDocument
.Execute False

Set TargetDoc = ActiveDocument

Selection.MoveLeft
Selection.Delete

TargetDoc.SaveAs2 FOLDER_SAVED & .DataSource.DataFields("File_Name").Value & ".docx", wdFormatDocumentDefault
TargetDoc.ExportAsFixedFormat OutputFileName:=FOLDER_SAVED & .DataSource.DataFields("File_Name").Value & ".pdf", exportformat:=wdExportFormatPDF

TargetDoc.Close False

Set TargetDoc = Nothing

Next recordNumber

End With

Set MainDoc = Nothing
End Sub

 

[Macropod]

I suggest you visit the Mailmerge Tips & Tricks thread at Microsoft Office Forums - Word, Excel, Outlook, PowerPoint, Project and check out the topics:
• Send Mailmerge Output to Individual Files; and
• Run a Mailmerge from Excel, Sending the Output to Individual Files

 

[hydraulicwave]

To eliminate the extra blank page in the PDF file, you can try adding the following line of code after the line that saves the document as a PDF:

Copy code
TargetDoc.Sections(1).PageSetup.DifferentFirstPageHeaderFooter = False

This will ensure that the first page of the PDF file has the same header and footer as the other pages. If the extra blank page is being caused by a header or footer on the first page that is not present on the other pages, this should remove the blank page.

Alternatively, you can try setting the page orientation to landscape mode. This can be done by adding the following line of code after the line that sets TargetDoc:

Copy code
TargetDoc.PageSetup.Orientation = wdOrientLandscape

This will make the PDF file wider, potentially allowing the content to fit on a single page.

 

[Macropod]

Neither of those approaches is likely to be helpful. I very much doubt anyone would want to omit a first-page letterhead or change the page orientation just to resolve this minor issue.

 

[hydraulicwave]

Alright alright

1. Check the page setup of your Word document and make sure that the margins are not set too wide, which could cause the content to be pushed onto the next page. You can do this by going to the "Page Layout" tab in Word and clicking on "Margins".
2. If you are using a table or other large object in your Word document that is causing the content to be split onto multiple pages, you can try adjusting the layout of the object to see if that resolves the issue.
3. Another option you can try is to add the following code to your VBA script after the line where you save the document as a PDF:

If TargetDoc.ComputeStatistics(wdStatisticPages) > 1 Then
TargetDoc.Range.Select
Selection.Delete
End If

This will delete the second page of the PDF if it exists.

 

[Macropod]

You really don't seem to want to address the actual problem. The most likely explanation for the OP's problem is that the mailmerge output document to be split has a 'Next Page' Section break at the end of each page and that Section break is being included in the output. The problem is conclusively addressed in the link I posted...

Reformatting the output document should never be necessary. If the formatting is correct in the mailmerge main document, then one should assume the output are expected to share that formatting.

Your VBA code would be a disaster in a Word document and would not work in the saved PDF!

 

[hydraulicwave]

You are correct that the problem could be caused by a section break at the end of each page in the mail merge output document. To fix this issue, you can try adding the following code to your VBA script before the line where you save the document as a PDF:

Copy code
With TargetDoc
.Sections(.Sections.Count).Range.Delete
End With

This will delete the last section of the document, which should remove the section break and any associated blank page.

 

[Macropod]

You really aren't interested in whether your code destroys documents, are you??? What do you suppose will happen if there isn't always a Section break or the last Section in some outputs actually have content that is to be retained?

Kindly also note that the question was answered in full almost a year ago. I really do wish you wouldn't try to resurrect resolved problems with half-baked answers!

 

[Macropod]

Kindly also note your code simply will not delete and empty last Section...