Printing PDF from Internet Explorer using VBA.

Posted on 2011-02-19
Last Modified: 2013-11-10
My problem is printing through Excel VBA. I am able to open a PDF document in internet explorer. Everything thing works fine until I issue the following print command:

IE.ExecWB 6, 2
-also tried the below (i believe is same as above though)-

Both lines succeed in launching the printer dialogue box prompting the user to click "OK" to finish printing. Not only does this brief pause screw up the rest of my code (which I could code a work around to fix the timing, but I'd rather not), it is also inconvenient for the user to have to verify each print job.

It is my understanding that the "do not prompt user" part of the code is not functioning due to the adobe reader plugin being used. I need to know if there is a work around where I can print.....from internet explorer.......with the adobe reader adobe acrobat installed.......silently with no prompt.

Please let me know if you know a way. Also feel free to add any insight you may have as to why adobe made it so difficult to work with PDF files programatically. I've had nothing but headaches with PDF files and I might as well find out why I am having to deal with these headaches!!

Question by:dw1284
LVL 30

Expert Comment

ID: 34936993

Author Comment

ID: 34937730
Sid, I sure have seen that thread and the solution results in errors for me.

Expert Comment

ID: 34938150
What are the errors, and what lines in your code do they occur at?
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


Author Comment

ID: 34938324
The code listed in that other thread seems incomplete, and the user who posted it didn't go to great lengths to explain how the code works. I personally don't see how it applies to my question because it looks to me like the code will print the value of the "Msg" variable....not the pdf page. But I could be wrong since I haven't got the code to even execute yet. The error occurs on the following line:

HWidth = Printer.TextWidth(Msg) / 2

The error states:

"Run-Time error '424'. Object required"

My code is as follows:

Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate ("C:\test.pdf")
IE.Visible = True
Do Until Not IE.Busy And IE.readyState = 4
Dim HWidth, HHeight, I, Msg
Msg = "This is printed on page"
For I = 1 To 2
       HWidth = Printer.TextWidth(Msg) / 2
       HHeight = Printer.textHeight(Msg) / 2
       Printer.CurrentX = Printer.ScaleWidth / 2
       Printer.CurrentY = Printer.ScaleHeight / 2
       Printer.Print Msg & Printer.Page & "."
Next I


Accepted Solution

dw1284 earned 0 total points
ID: 34940592
I am going to close this thread as there does not appear to be solution. For the benefit of those who may search this topic in the future I am going to post the workaround I used and I'm also going to answer my own question as to why PDF files are difficult to work with through VBA. Thanks to those who attempted to help.

First let me talk about why Adobe seems to have gone out of their way to make Reader as unfunctional as possible. Reader is a free program, intended only to provide end users a medium to view, store, and print PDF files. That is all you are supposed to be able to do with the free Reader program. Nothing more.....nothing at all. If you want to create pdf files, edit pdf files, or automate pdf files (that's us!) you must purchase Adobe Acrobat. For those of us who are limited to the tools available on our work PC's, I guess you will be screwed if your company will not spring for Adobe Acrobat licenses. Thanks Adobe, now we like you even more than we did before!!!! (sarcasm)

Now for the solution. This is not going to be popular, but I have tested this on multiple computers, 2 different operating systems (Windows XP and Widows 7), 2 different versions of internet explorer, and with the latest version of adobe reader. It is working very consistently so I am willing to forgo any previous concerns I had about this least in this case.....since there doesn't seem to be any other way. The workaround is to issue the ExecWeb print command, which we know pops up the print dialogue box, and bypass the dialogue box using a sendkeys command (I know...I know, there doesn't seem to be any other way). You can try it, and if it works for you then fantastic. It works for me. Lucky for us the print dialogue box seems to bring the focus to internet explorer allowing for consistent use of the sendkeys command.

The code is as follows:

Dim IE as Internet Explorer
Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate ("C:\test.pdf")
IE.Visible = True
Do Until Not IE.Busy And IE.readyState = 4
IE.ExecWB 6, 2                               
SendKeys "{ENTER}", True            

Open in new window

If you experience timing issues experiment by adding "Sleep()" commands of varying lengths to various points in the code. If you are not familiar with the "Sleep()" function please google it. I'm sure you can make it work. I do not personally, nor do I recommend that you, utilize the sendkeys command at any time in any shape, form, or fashion. Sendkeys is a hack. It is inconsistent and undependable at best.....and completely broken at worst. In this case, it does seem to be working. If it turns out to be a mistake going this route, I will edit this post.
LVL 26

Expert Comment

ID: 34954891

I share your pain. I also heartily concur with your warnings about SendKey. Like you, I was forced to use it on a few occasions. However, I then came across AutoHotKey. At the cost of a bit of a learning curve, this has done everything for me that I've ever asked of it. I'm no longer even tempted by SendKey!


Author Closing Comment

ID: 34978097
Nobody was able to solve this problem. I do not believe there is a solution. I chose my own response as the solution so that I could officially close the question. Also, I posted my work around so that others have another option if they too run into this problem.

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A short article about problems I had with the new location API and permissions in Marshmallow
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question