Solved

Printing PDF from Internet Explorer using VBA.

Posted on 2011-02-19
7
4,370 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
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now