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

mychi11

Board Regular
Joined
May 11, 2020
Messages
82
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Watch MrExcel Video

Forum statistics

Threads
1,128,020
Messages
5,628,182
Members
416,299
Latest member
arunvistas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top