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

Resolved Question:

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.
Submitted: 8 months ago.
Category: Microsoft Office
Expert:  MrBargain replied 8 months ago.

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?

Customer:

ready

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

Customer:

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?

Customer:

How do I know when you're done?

MrBargain :

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

Customer:

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

Expert:  Mr. PC Wizard replied 8 months ago.
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: http://www.foxitsoftware.com/Secure_PDF_Reader/
Customer: replied 8 months ago.

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

Expert:  Mr. PC Wizard replied 8 months ago.
General audience, what do you mean, why are you stuck with adobe reader?
Customer: replied 8 months 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.

Expert:  Mr. PC Wizard replied 8 months ago.
oh, so you want the person reading it be able to print, not yourself be able to do this, correct?
Customer: replied 8 months 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.


 

Expert:  Mr. PC Wizard replied 8 months ago.
I'm pretty sure that's not possible
Customer: replied 8 months 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?

Expert:  Mr. PC Wizard replied 8 months ago.
Perhaps, I shall opt out to see if there's a vba expert available. Thank you
Expert:  Rachel-Mod replied 8 months ago.

Hello,

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,

Rachel

Customer: replied 8 months ago.

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

Expert:  Rachel-Mod replied 8 months ago.

Hello,

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.

Best,

Rachel

Expert:  kooleraid replied 8 months ago.
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 8 months 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.

Expert:  kooleraid replied 8 months ago.
In your .ExportAsFixedFormat line add these 2 properties:
IncludeDocProperties:=True, IgnorePrintAreas:=False

Let me know
Customer: replied 8 months 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:= _
False


 


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

Expert:  kooleraid replied 8 months ago.
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
Sheets(N).Activate
SheetName = ActiveSheet.Name
Cells.Copy
Workbooks.Add (xlWBATWorksheet)
With ActiveWorkbook
With .ActiveSheet
.Paste
.Name = SheetName
[A1].Select
.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
Next
End With
Sheet1.Activate
End Sub

Customer: replied 8 months 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.

Expert:  kooleraid replied 8 months ago.
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 8 months ago.

How can I pursue this?

Expert:  kooleraid replied 8 months ago.
I will investigate and get back to you.
Expert:  kooleraid replied 8 months ago.

Hello!

***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

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

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 8 months 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.


 

Expert:  kooleraid replied 8 months ago.
Send me that chunk of code and I'll review / test
Customer: replied 8 months 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
Loop
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
Loop
If Remainder > 0 Then
ReDim Buffer(1 To Remainder)
Get #hOriginal, , Buffer
Put #hFullscreen, , Buffer
Remainder = 0
End If
Close hOriginal, hFullscreen


End Sub

Expert:  kooleraid replied 8 months ago.
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 8 months 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?

Expert:  kooleraid replied 8 months ago.
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 8 months ago.

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

Expert:  kooleraid replied 8 months ago.
I'll have a look and get back to you.
Expert:  kooleraid replied 8 months ago.

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 8 months 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?

Expert:  kooleraid replied 8 months ago.
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 8 months ago.

Great!!!

Expert:  kooleraid replied 8 months ago.
Hello!

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

(1) If you do not have it -> Install java http://www.java.com/en/download/index.jsp

(2) Download program I wrote from this link:
Password XXXXX: temp
http://www.mediafire.com/download/mejhtbanc60qc37/updatePDF.zip

(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 8 months ago.

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

Expert:  kooleraid replied 8 months ago.

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

Customer: replied 8 months 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.

Expert:  kooleraid replied 8 months ago.
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 8 months ago.

Thanks so much for your effort.

Expert:  kooleraid replied 8 months ago.

You are most welcome!

Expert:  kooleraid replied 8 months ago.
Is there anything else I can help you with?
kooleraid, Consultant
Category: Microsoft Office
Satisfied Customers: 124
Experience: Microsoft Office
kooleraid and 7 other Microsoft Office Specialists are ready to help you

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional