Solved

Printing PDF from Internet Explorer using VBA.

Posted on 2011-02-19
7
4,936 Views
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)-
IE.ExecWB OLECMDID_PRINT, OLECMDEXECOPT_DONTPROMPTUSER

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 plugin......no 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!!

Thanks!
0
Comment
Question by:dw1284
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34936993
0
 

Author Comment

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

Expert Comment

by:dev00790
ID: 34938150
What are the errors, and what lines in your code do they occur at?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:dw1284
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
      DoEvents
Loop
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 & "."
       Printer.NewPage
Next I
Printer.EndDoc


0
 

Accepted Solution

by:
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 method.....at 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
      DoEvents
Loop
IE.ExecWB 6, 2                               
SendKeys "{ENTER}", True            
IE.Quit

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.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 34954891
dw1284,

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!

Regards,
Brian.
0
 

Author Closing Comment

by:dw1284
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.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

738 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