VBA to loop excels in the directory and print to pdf. The pdf page setup should be one page

[Bigpotato 668]

Hi all,

I use below code to loop all excel files in the directory and convert to pdf.

But the pdf output has page setup twisted.

How can I modify my coding to make my pdf output page setup as within one page?

Thank you all.

Public Sub Create_Invoice_PDFs()

Dim folderPath As String
Dim fileName As String
Dim invoiceSheet As Worksheet

folderPath = "C:\path\to\workbooks\" 'MODIFY THIS LINE - FOLDER CONTAINING THE WORKBOOKS

If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"

Application.ScreenUpdating = False

fileName = Dir(folderPath & "*.xls*")
Do While fileName <> ""
Workbooks.Open folderPath & fileName
Set invoiceSheet = Nothing
On Error Resume Next
Set invoiceSheet = ActiveWorkbook.Worksheets("RCTI")
Err.Clear
If Not invoiceSheet Is Nothing Then
invoiceSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:=folderPath & Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".")) & "pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
Else
Debug.Print fileName & " doesn't contain a 'RCTI' sheet"
End If
ActiveWorkbook.Close False

fileName = Dir
Loop



Application.ScreenUpdating = True

End Sub

 

[severynm]

Welcome!

See the answer to this same question posted here. If you want to do the equivalent of "File→Print→Print Sheet On One Page", the bare minimum you will want to add before printing is

    SheetName.PageSetup.Zoom = False
    SheetName.PageSetup.FitToPagesWide = 1
    SheetName.PageSetup.FitToPagesTall = 1

 

[Bigpotato 668]

thanks mate. Sorry for the late reply. I added below and try again, but seems not work...

Public Sub Create_Invoice_PDFs()

Dim folderPath As String
Dim fileName As String
Dim invoiceSheet As Worksheet

folderPath = "C:\Users\aacc143\Desktop\Monthly Commission Coding and Macro\Test" 'MODIFY THIS LINE - FOLDER CONTAINING THE WORKBOOKS

If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"

Application.ScreenUpdating = False

fileName = Dir(folderPath & "*.xls*")
Do While fileName <> ""
Workbooks.Open folderPath & fileName
Set invoiceSheet = Nothing
On Error Resume Next
Set invoiceSheet = ActiveWorkbook.Worksheets("RCTI")
Err.Clear
If Not invoiceSheet Is Nothing Then
invoiceSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:=folderPath & Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".")) & "pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
Else
SheetName.PageSetup.Zoom = False
SheetName.PageSetup.FitToPagesWide = 1
SheetName.PageSetup.FitToPagesTall = 1
Debug.Print fileName & " doesn't contain a 'RCTI' sheet"
End If
ActiveWorkbook.Close False

fileName = Dir
Loop



Application.ScreenUpdating = True

End Sub

 

[severynm]

And what is the name of your worksheet you are printing from? You need to change SheetName to the correct name.

Edit: Spelling