VBA Print to PDF

C

camerong

New Member
Joined
May 9, 2023
Messages
42
Office Version
  1. 2016
Platform
  1. Windows
Hi guys,

I have the VBA code below to print the active worksheet to PDF in the Downloads folder. It prints to A4 sheet size on my computer BUT on my colleagues computer it prints it to Letter paper size. We want it to always print to A4 paper size, regardless of what computer it's used on or Excel version is being run.

Thanks in advance guys :)

See below:


Sub Print_to_PDF()
'
' PDF_Single_Page Macro
' Saves to PDF Ctrl+Shft+End page selection area
'
RFIPrefix = "RFI "
RFINum = Range("E4") & " - "
JobNum = Range("B4") & " - "
JobName = Range("B5")
Exten = ".pdf"
'
Range("A1:E28").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ChDir "C:\Users\" & Environ("Username") & "\Downloads\"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\" & Environ("Username") & "\Downloads\" & RFIPrefix & RFINum & JobNum & JobName & Exten _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=True, OpenAfterPublish:=False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0)
.BottomMargin = Application.InchesToPoints(0)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.CenterHorizontally = True
.CenterVertically = False
.PaperSize = xlPaperA4
.BlackAndWhite = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
Application.PrintCommunication = True

End Sub
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
Hi @camerong .

First you must put the code to configure the sheet and then export to PDF:
VBA Code: 
Sub Print_to_PDF()
  '
  ' PDF_Single_Page Macro
  ' Saves to PDF Ctrl+Shft+End page selection area
  '
  RFIPrefix = "RFI "
  RFINum = Range("E4") & " - "
  JobNum = Range("B4") & " - "
  JobName = Range("B5")
  Exten = ".pdf"
  '
  
  With ActiveSheet.PageSetup
    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = ""
    .LeftMargin = Application.InchesToPoints(0)
    .RightMargin = Application.InchesToPoints(0)
    .TopMargin = Application.InchesToPoints(0)
    .BottomMargin = Application.InchesToPoints(0)
    .HeaderMargin = Application.InchesToPoints(0)
    .FooterMargin = Application.InchesToPoints(0)
    .CenterHorizontally = True
    .CenterVertically = False
    .PaperSize = xlPaperA4
    .BlackAndWhite = False
    .FitToPagesWide = 1
    .FitToPagesTall = False
  End With
  Application.PrintCommunication = True
  
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:="C:\Users\" & Environ("Username") & "\Downloads\" & RFIPrefix & RFINum & JobNum & JobName & Exten, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False
End Sub
These lines are not necessary, since you are exporting the entire sheet, not the selection.
VBA Code: 
'  Range("A1:E28").Select
'  Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'  ChDir "C:\Users\" & Environ("Username") & "\Downloads\"


--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------
 
Upvote 0
Hi Dante,

Thanks for the response, however it still seems to be printing to Letter paper size on my colleagues computer.

We have checked the paper size in the excel ribbon and the margins and both match on both computers.

Thank you
 
Upvote 0
Maybe it's a problem with your printer or the version of office.

Before running the macro, you manually set the print to A4 and check if it changed to A4.
Activate the macro recorder on your colleague's computer and you adjust the sheet to A4, stop the macro, copy the macro and paste it here for review.
 
Upvote 0
Solution
Hi Dante,

Ok so I went to his computer, manually checked the paper size is A4 in the ribbon (ribbon > page setup > size), then went to printer settings in Excel and checked that is A4 as well. Then I started recording the macro below, selected the A4 paper size in the ribbon > page setup > size.

See below the VBA code recorded:

VBA Code: 
[11:31 am] Isaac Carson
Sub PDF_PRINT_TEST()
'
' PDF_PRINT_TEST Macro
'
 
'
   Application.PrintCommunication = False
   With ActiveSheet.PageSetup
       .PrintTitleRows = ""
       .PrintTitleColumns = ""
   End With
   Application.PrintCommunication = True
   ActiveSheet.PageSetup.PrintArea = "$A$2:$E$29"
   Application.PrintCommunication = False
   With ActiveSheet.PageSetup
       .LeftHeader = ""
       .CenterHeader = ""
       .RightHeader = ""
       .LeftFooter = ""
       .CenterFooter = ""
       .RightFooter = ""
       .LeftMargin = Application.InchesToPoints(0)
       .RightMargin = Application.InchesToPoints(0)
       .TopMargin = Application.InchesToPoints(0)
       .BottomMargin = Application.InchesToPoints(0)
       .HeaderMargin = Application.InchesToPoints(0)
       .FooterMargin = Application.InchesToPoints(0)
       .PrintHeadings = False
       .PrintGridlines = False
       .PrintComments = xlPrintNoComments
       .PrintQuality = 600
       .CenterHorizontally = True
       .CenterVertically = False
       .Orientation = xlPortrait
       .Draft = False
       .PaperSize = xlPaperA4
       .FirstPageNumber = xlAutomatic
       .Order = xlDownThenOver
       .BlackAndWhite = False
       .Zoom = False
       .FitToPagesWide = 1
       .FitToPagesTall = False
       .PrintErrors = xlPrintErrorsDisplayed
       .OddAndEvenPagesHeaderFooter = False
       .DifferentFirstPageHeaderFooter = False
       .ScaleWithDocHeaderFooter = True
       .AlignMarginsHeaderFooter = False
       .EvenPage.LeftHeader.Text = ""
       .EvenPage.CenterHeader.Text = ""
       .EvenPage.RightHeader.Text = ""
       .EvenPage.LeftFooter.Text = ""
       .EvenPage.CenterFooter.Text = ""
       .EvenPage.RightFooter.Text = ""
       .FirstPage.LeftHeader.Text = ""
       .FirstPage.CenterHeader.Text = ""
       .FirstPage.RightHeader.Text = ""
       .FirstPage.LeftFooter.Text = ""
       .FirstPage.CenterFooter.Text = ""
       .FirstPage.RightFooter.Text = ""
   End With
   Application.PrintCommunication = True
End Sub
 
Upvote 0
In my tests this generates an A4 size sheet.

VBA Code: 
Sub Print_to_PDF()
  '
  ' PDF_Single_Page Macro
  ' Saves to PDF Ctrl+Shft+End page selection area
  '
  RFIPrefix = "RFI "
  RFINum = Range("E4") & " - "
  JobNum = Range("B4") & " - "
  JobName = Range("B5")
  Exten = ".pdf"
  '
  Application.PrintCommunication = False
  With ActiveSheet.PageSetup
      .PrintTitleRows = ""
      .PrintTitleColumns = ""
  End With
  Application.PrintCommunication = True
  ActiveSheet.PageSetup.PrintArea = "$A$2:$E$29"
  Application.PrintCommunication = False
  With ActiveSheet.PageSetup
      .LeftHeader = ""
      .CenterHeader = ""
      .RightHeader = ""
      .LeftFooter = ""
      .CenterFooter = ""
      .RightFooter = ""
      .LeftMargin = Application.InchesToPoints(0)
      .RightMargin = Application.InchesToPoints(0)
      .TopMargin = Application.InchesToPoints(0)
      .BottomMargin = Application.InchesToPoints(0)
      .HeaderMargin = Application.InchesToPoints(0)
      .FooterMargin = Application.InchesToPoints(0)
      .PrintHeadings = False
      .PrintGridlines = False
      .PrintComments = xlPrintNoComments
      .PrintQuality = 600
      .CenterHorizontally = True
      .CenterVertically = False
      .Orientation = xlPortrait
      .Draft = False
      .PaperSize = xlPaperA4
      .FirstPageNumber = xlAutomatic
      .Order = xlDownThenOver
      .BlackAndWhite = False
      .Zoom = False
      .FitToPagesWide = 1
      .FitToPagesTall = False
      .PrintErrors = xlPrintErrorsDisplayed
      .OddAndEvenPagesHeaderFooter = False
      .DifferentFirstPageHeaderFooter = False
      .ScaleWithDocHeaderFooter = True
      .AlignMarginsHeaderFooter = False
      .EvenPage.LeftHeader.Text = ""
      .EvenPage.CenterHeader.Text = ""
      .EvenPage.RightHeader.Text = ""
      .EvenPage.LeftFooter.Text = ""
      .EvenPage.CenterFooter.Text = ""
      .EvenPage.RightFooter.Text = ""
      .FirstPage.LeftHeader.Text = ""
      .FirstPage.CenterHeader.Text = ""
      .FirstPage.RightHeader.Text = ""
      .FirstPage.LeftFooter.Text = ""
      .FirstPage.CenterFooter.Text = ""
      .FirstPage.RightFooter.Text = ""
  End With
   
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:="C:\Users\" & Environ("Username") & "\Downloads\" & RFIPrefix & RFINum & JobNum & JobName & Exten, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False
End Sub

Check the settings on your colleague's computer, so you can see which printer he has by default and check if that printer accepts A4 size sheets.

Set a default printer in Windows - Microsoft Support

Use the Settings app in Windows 10 to set a default printer and manage printer settings.
support.microsoft.com support.microsoft.com

-----
I hope it helps you
 
Upvote 0
Hi Dante, thanks thats done the trick, it was set for Windows to select the default printer and it must have been selecting one that wasnt set to A4 in the preferences.
 
Upvote 1
You must log in or register to reply here.

Similar threads

imback2nite
  • Question Question
Print size change using VBA.
Replies
4
Views
319
imback2nite
imback2nite
C
  • Solved
Print to PDF into created folder in Workbook location
Replies
3
Views
786
camerong
C
K
  • Question Question
80mm Thermal Printer (PSO-80) affects me PDF range
Replies
0
Views
54
kapela2017
K
C
  • Solved
PDF specific sheets into new created folder in Workbook location
Replies
2
Views
693
camerong
C
C
  • Solved
VBA macro to search each sheet cell for specific text and print to PDF when true
Replies
6
Views
996
camerong
C

Forum statistics

Threads
1,223,889
Messages
6,175,224
Members
452,620
Latest member
dsubash

Share this page

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
Back
Top