How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask kooleraid Your Own Question
kooleraid, Consultant
Category: Microsoft Office
Satisfied Customers: 124
Experience:  Microsoft Office
Type Your Microsoft Office Question Here...
kooleraid is online now
A new question is answered every 9 seconds

Im using the Excel vba ‘publish to pdf utility to create

This answer was rated:

I’m using the Excel vba ‘publish to pdf’ utility to create a pdf document from a worksheet.
How can I force it to print in Landscape mode?
Configuring the worksheet page setup as Landscape changes the appearance of the opened pdf but not how it prints.
I know that this can be done using the full Adobe application but this is an expensive solution.

MrBargain :

Hi, my name isXXXXX would be happy to help you troubleshoot the PDF to landscape mode problem you have. Let me know when you are ready to start?



MrBargain :

OK great. When I read your problem description a question jumped into my head.

MrBargain :

I've had this problem before and the fix was really easy so here's hoping that the same fix applies here.

MrBargain :

Can you go check the page formatting of your excel document?

MrBargain :

Page layout - orientation - landscape


I don't think this is easy. I've been trying for years. Yes the page setup is set for Landscape. As I said the opened pdf is in Landscape but the print mode is Portrait.

MrBargain :

Yes, please allow me time to finish my instructions or would you rather talk to another expert?


How do I know when you're done?

MrBargain :

Hi, I'm sorry but I am going to opt out for obvious reasons.


Sorry if I offended you. I really thought you were done asking if the setup was Landscape or not

Hello, my name isXXXXX I hope that you're doing well today. I look forward to personally assisting you on this matter and hope that we can resolve your question effectively to your needs.

I'm personally not a fan of adobe reader, it's clunky and the full version is way overpriced. I've had much luck with foxit reader, try downloading that and setting as your default pdf viewer and see if that fixes the issue, let me know the results, I'll be standing by and respond as soon as I can.

Here's the link to foxit reader:
Customer: replied 3 years ago.

I'm creating the pdf for a general audience so I'm stuck with Adobe Reader.

General audience, what do you mean, why are you stuck with adobe reader?
Customer: replied 3 years ago.

I'm mot the guy reading it, I'm the guy creating it. I don't know what application it will be opened in but must assume that it's Adobe Reader.

oh, so you want the person reading it be able to print, not yourself be able to do this, correct?
Customer: replied 3 years ago.

Yes, that's true. It's very frustrating as I create beautiful Landscape pdf's and invariably someone will print 50 copies in the default Portrait mode.

They should set Landscape when they print, but usually just click on print and take what they get. I want to force printing in Landscape mode. I know this can be done with the full Adobe where printing in Landscape can be embedded into the document. . What I'm looking for is a vba hack to do this.


I'm pretty sure that's not possible
Customer: replied 3 years ago.

It's certainly possible if it can be done in Adobe. For example, I have code to force the pdf to open in full screen mode. Are there vba experts available to look at this?

Perhaps, I shall opt out to see if there's a vba expert available. Thank you


I’m Rachel, and I’m moderator for this topic. It seems the professional has left this conversation. This happens occasionally, and it's usually because the professional thinks that someone else might be a better match for your question. I've been working hard to find a new professional to assist you right away, but sometimes finding the right professional can take a little longer than expected.

I was checking to see if you had already found your answer or if you still needing assistance from another one of the professionals?

Please let me know if you wish to continue waiting or if you would like for us to close your question.

Also remember that JustAnswer has a multitude of categories to help you with all your needs from Health, Pets, Computers, Taxes, Cars, Finance, Law, to Home Improvement, and more.

Thank you,


Customer: replied 3 years ago.

Yes I'd like to continue. This issue requires an expert in Excel VBA.


Thank you, XXXXX XXXXX continue to look for a professional to assist you. Please let me know if I can be of any further assistance while you wait.



Hello and Thanks for Choosing JustAnswer!

You can add this line to your vba code to have it export in Landscape mode:
.PageSetup.Orientation = xlLandscape

Add it before where you see ".ExportAsFixedFormat" line

If you are not able to make it work feel free to send me a copy of your vba code and I'd happy to take a look.
Customer: replied 3 years ago.

I do have the page setup as Landscape and it does open in landscape when viewing the pdf. My problem is that it still prints in portrait. I want to somehow embed landscaped printing as default into the pdf.

In your .ExportAsFixedFormat line add these 2 properties:
IncludeDocProperties:=True, IgnorePrintAreas:=False

Let me know
Customer: replied 3 years ago.

This is my code:

ActiveSheet.PageSetup.Orientation = xlLandscape

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ThisWorkbook.Path & "Try PDF.pdf", Quality:=xlQualityStandard _
, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _


When I open it, the print mode is the default of portrait.

OK Here is a macro that will save your sheets into a pdf in Landscape mode. When you open the resulting pdf in Adobe PDF reader it will show on your screen in Landscape mode.

Option Explicit

Sub SheetsAsPDFsLandscape()
Dim Sheet As Worksheet, SheetName$, MyFilePath$, N&
MyFilePath$ = ActiveWorkbook.Path & "\" & _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)
With Application
.ScreenUpdating = False
.DisplayAlerts = False
' End With
On Error Resume Next '<< a folder exists
MkDir MyFilePath '<< create a folder
For N = 1 To Sheets.Count
SheetName = ActiveSheet.Name
Workbooks.Add (xlWBATWorksheet)
With ActiveWorkbook
With .ActiveSheet
.Name = SheetName
.PageSetup.CenterHeader = "&""-,Bold""&20TEST PDF"
.PageSetup.Orientation = xlLandscape
.PageSetup.FitToPagesWide = 1
End With
'save book in this folder
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=MyFilePath _
& "\" & SheetName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
.Close SaveChanges:=False
End With
.CutCopyMode = False
End With
End Sub

Customer: replied 3 years ago.

My problem is not how the pdf shows when opening, it is how it is printed.

I need to somehow embed instructions to print in landscape.

This can be done in Adobe so I'm sure there must be a way with vba.

Advanced PDF Document Properties such as "Print Dialog Presets" which include PDF properties such as Page Scaling, DuplexMode, Paper Source by Page Size, Print Page Range, Number of Copies are NOT available and can not be populated using VBA's standard ExportAsFixedFormat function.

This would require custom VBA code that utilizes the Adobe SDK (Standard Development Kit) or another external PDF utility library. Code would need to open the generated pdf file and update the appropriate advanced document setting thereby embedding the PDF print default settings.
Customer: replied 3 years ago.

How can I pursue this?

I will investigate and get back to you.


***Using Acrobat Standard or Pro

Following Change will accomplish what you are after:

File -> Properties -> Advanced -> Print Dialog Presets -> Check "Paper Source by Page Size" -> Click OK -> Save


***Using Acrobat SDK

Use of Acrobat SDK is not an option without an actual installation of Acrobat itself. Following code lets access to PDF print parameters and enable the "choose paper size by pdf source" option.


Dim SampleFilePath As String

Dim rc As Integer

Dim pdDoc As Acrobat.AcroPDDoc

Dim jso As Object

Dim pp As Object

Dim FV As Object

pdDoc = CreateObject("AcroExch.PDDoc")

If pdDoc Is Nothing Then

MsgBox ("Failed to create Acrobat PDDoc object. Is Acrobat Installed properly?")


End If

' Open the source PDF document

SampleFilePath = "Test.pdf"

rc = pdDoc.Open(SampleFilePath)

If rc -1 Then

MsgBox ("Failed to open PDF document " & SampleFilePath)


End If

jso = pdDoc.GetJSObject

pp = jso.getPrintParams()

FV = pp.Constants.flagValues

pp.flags = (FV.setPageSize + FV.suppressCenter + FV.suppressRotate)

jso.Print (pp)


***Without Using Acrobat

Alternatively, excel to postscript to pdf via ghostscript which is the open source engine that serves the same purpose as adobe's distiller and which is used by all of Adobe's competition including FoxIt would be the avenue. Although not sure if this route is cost-effective as it would an effort to put it together when Adobe is available as a subscription as well.



Customer: replied 3 years ago.

Buying Acrobat is not an option for me. I've tried the free pdf Creator and have the same problem. I'm looking for a way to change the print settings with VBA. I think it must be possible as I have code which modifies a pdf document's properties to have it open in full screen mode.


Send me that chunk of code and I'll review / test
Customer: replied 3 years ago.

Sub SetFullScreenPDF(ByVal original As String, ByVal fullscreen As String)
Const BUFSIZ As Long = 4096
Dim Remainder As Long
Dim hOriginal As Integer
Dim hFullscreen As Integer
Dim StrBuffer As String
Dim Header As String
Dim Buffer() As Byte
Dim Offset As Long
Dim Regex As Object 'RegExp
Dim Fso As Object 'FileSystemObject

hOriginal = FreeFile
Open original For Input Access Read Lock Write As #hOriginal
Line Input #hOriginal, StrBuffer 'get the first line
Header = StrBuffer & vbCrLf
Do While True
Offset = Seek(hOriginal) 'Loc() returns position/128 for sequential files
If LCase(StrBuffer) Like "*endobj*" Then
Exit Do
End If
Line Input #hOriginal, StrBuffer 'get the next line
Header = Header & StrBuffer & vbCrLf
Close hOriginal

Set Regex = CreateObject("VBScript.RegExp") 'New RegExp
Regex.Pattern = "/Type[\s\r\n]*/Catalog"
Regex.MultiLine = True
Header = Regex.Replace(Header, "/Type/Catalog/PageMode/FullScreen")
Set Regex = Nothing

hOriginal = FreeFile
Open original For Binary Access Read Lock Write As #hOriginal
Seek hOriginal, Offset

Set Fso = CreateObject("Scripting.FileSystemObject") 'New FileSystemObject
If Fso.FileExists(fullscreen) Then
Fso.DeleteFile (fullscreen) 'Get permission denied errors here
End If
Set Fso = Nothing

hFullscreen = FreeFile
Open fullscreen For Binary Access Write Lock Read Write As #hFullscreen
Put #hFullscreen, , Header

'copy original file
Remainder = LOF(hOriginal) - Offset + 1 'VB6 offsets are 1-based
ReDim Buffer(1 To BUFSIZ)
Do While Remainder > BUFSIZ
Get #hOriginal, , Buffer
Put #hFullscreen, , Buffer
Remainder = Remainder - BUFSIZ
If Remainder > 0 Then
ReDim Buffer(1 To Remainder)
Get #hOriginal, , Buffer
Put #hFullscreen, , Buffer
Remainder = 0
End If
Close hOriginal, hFullscreen

End Sub

I see and am aware of the approach above - basically hacking the binary file itself by making a find and replace via RegEx without parsing it via a pdf library.

Enabling full screen requires that you simply update the root object in other words it is trivial because that element exists in every PDF file across PDF versions and requires simple addition of 2 key words in the very same line.

PDF is the portable document format, and printer settings are device dependent. There isn't a way to define printer settings in PDF. However, since PDF 1.7 (Adobe Reader 8.0 and later), some viewer preferences were introduced that allow you to prefill some settings in the print dialog.

PickTrayByPDFSize boolean
(Optional; PDF 1.7) A flag specifying whether the PDF page
size is used to select the input paper tray. This setting
influences only the preset values used to populate the print
dialog presented by a PDF viewer application.

If PickTrayByPDFSize is true, the check box in the print dialog
associated with input paper tray is checked.

Note: This setting has no effect on Mac OS systems, which do
not provide the ability to pick the input tray by size.

Default value: as defined by the PDF viewer application

Excel VBA PDF conversion creates a PDF-1.5 document which does not contain the object you would need to update PickTrayByPDFSize property. I can of course open the document in Acrobat and save as and I'll get a PDF-1.7 version but that beats the purpose :)

So if you wanted to pursue this route next step would be to use another method to generate a PDF-1.7 document from Excel and then you could use a VBA script to hack the ViewerPreferences object and set its PickTrayByPDFSize property to true.

Let me know
Customer: replied 3 years ago.

I don't understand the PickTrayByPdfSize. I want to Print in Landscape mode from whatever the active tray is.


Does PdfCreator create the desired file?

Remember we are already are creating the source in Landscape so the option basically tells the PDF parser to use the source size to automatically determine the write mode to print in ie Landscape.
Customer: replied 3 years ago.

So I have to create a pdf v1.7 document. Any ideas?

I'll have a look and get back to you.

Hello! I have written code in java using an external library from a 3rd party and it seems to do the trick ie I am able to mod the pdf file from excel vba and add to it the pdf1.7 object for handling the "choose paper size by PDF source" option in Adobe. I can try and see if excel vba will also function using this library's vb dlls or I can give you java code. As a matter of fact this now powers me to do anything I want to the pdf without Acrobat. Let me know. :)

Customer: replied 3 years ago.

Thanks for your persistence!! I don't know how to proceed. Can you give me an executable I can call with the shell function that will do what I want?

Or is it best to try using vba?

DLL's are not COM ready so you can't do the VBA but you can use the shell function to script it so to speak from your VBA code or you could just run it from the command line however you prefer. I'll put together the instructions and the package.
Customer: replied 3 years ago.



Here are instructions on how to get the program and run it.

(1) If you do not have it -> Install java

(2) Download program I wrote from this link:
Password XXXXX: temp

(3) Unzip program

(4) You can run the program from command line (Start->Run->cmd->Enter->cd to directory where the unzipped program is) as follows:
java -cp "*;" updatePDF "SOURCE.PDF" "TARGET.PDF"

(5) Run the program from command line first to make sure it all works before scripting via shell in VBA.
Customer: replied 3 years ago.

I ran the program and created a new pdf. It still has the print mode set for Portrait.

Is the "choose paper size by PDF source" clicked on when the print dialog comes up in Adobe Reader?

Customer: replied 3 years ago.

This is great!!! "choose paper size by PDF source" is clicked. If my pdf was saved as Landscape, it prints in Landscape whether or not the orientation is selected as Landscape. Same for Portrait. I think this is exactly what I want. I have it working from the shell command.

That is great news!! :) This way it is literally dummy proof and when you send it out ppl just cant print it out in a way you do NOT want them to. Let me know
Customer: replied 3 years ago.

Thanks so much for your effort.

You are most welcome!

Is there anything else I can help you with?
kooleraid, Consultant
Category: Microsoft Office
Satisfied Customers: 124
Experience: Microsoft Office
kooleraid and 2 other Microsoft Office Specialists are ready to help you

Related Microsoft Office Questions