PDA

View Full Version : Open Print Properties using VBA



daniels012
10-19-2007, 09:25 AM
I have searched around and found this code:


Sub ShowPrinterProperties()
Call Shell("rundll32 printui.dll,PrintUIEntry /p /n\\Sidonna\HP DeskJet 895Cse on Ne05:")

End Sub

I just can't get it to work I have added the printer as they have suggested??:banghead:
I even tried this:


Sub ShowPrinterProperties()
ActPrn = Application.ActivePrinter
Call Shell("rundll32 printui.dll,PrintUIEntry /p /n ActPrn")
End Sub


This didn't work either??

Any ideas?:help
Thank you,
Michael

Bob Phillips
10-19-2007, 09:29 AM
What are you trying to do?

daniels012
10-19-2007, 09:33 AM
All I want to do is when i go to print, the printer properties dialog box will appear, so the User can change the setting accordingly.

Michael

RonMcK3
10-19-2007, 05:08 PM
Here's an interim answer as I search for an answer.

Scanning msdn2.microsoft.com for "rundll32 printui.dll, PrintUIEntry" leads me to a number of articles with instructions for adding a printer with no user interaction in Windows [XP].

For instance, see http://support.microsoft.com/kb/314486 (for XP) or ../189105 (for Windows)

I'm still digging, more later.


Ron
Orlando, FL

Bob Phillips
10-20-2007, 01:12 AM
So how about just showing the printer dialog




application.Dialogs(xlDialogPrinterSetup).show

daniels012
10-22-2007, 05:55 AM
XLD,
I know I can do that! I want to get right to the properties page. Not printer setup. PROPERTIES page.

Michael

Bob Phillips
10-22-2007, 06:03 AM
There is a Setup button to access the properties from there.

daniels012
10-22-2007, 06:06 AM
OK:dunno
Here is my error message!
"Printer properties cannot be displayed.
Make sure that you have typed the name
correctly, and that the printer is connected
to the network."

I know it is connected to the network, because I can print fine. I can also pull up the printer properties window manually just fine.

Not sure what I need to do?:help

Michael

johnske
10-22-2007, 06:30 AM
Option Explicit

Sub ShowPrinterProperties()
With Application
.SendKeys "{TAB},{TAB},{TAB},{TAB},{TAB},{TAB}"
.SendKeys "{ENTER}"
.CommandBars("File").Controls("Print...").Execute
End With
End Sub

daniels012
10-22-2007, 06:58 AM
thank you for your attempt Johnske.
This gets me to the Print Dialog, I need one more step to the Print Properties Dialog

Michael

johnske
10-22-2007, 07:10 AM
well on my machine that brings up the printer properties, try putting ".CommandBars("File").Controls("Print...").Execute" before sendkeys

daniels012
10-22-2007, 07:16 AM
I did as you said...
No luck? Same results, just the print dialog

Not sure if it is because it is
an HP printer
a Network printer
I can't figure this one out.

Michael

johnske
10-22-2007, 07:34 AM
what office version ?

johnske
10-22-2007, 07:37 AM
... I can also pull up the printer properties window manually just fine...also, what do you mean by this? do you mean by going to File > Print... > Properties ?

daniels012
10-22-2007, 07:39 AM
1) Office version 2000 Premium
2) Yes, File>Print> Properties etc.

johnske
10-22-2007, 07:48 AM
ok, bring up your print dialog manually, then count how many times you have to hit the Tab key before the 'Properties' button is selected...

daniels012
10-22-2007, 07:58 AM
6 times?
Which is what you provided in your code?

Michael

johnske
10-22-2007, 08:00 AM
Yes, it should work, maybe it needs a delay, try

Option Explicit

Sub ShowPrinterProperties()
With Application
.SendKeys "{TAB}", True
.SendKeys "{TAB}", True
.SendKeys "{TAB}", True
.SendKeys "{TAB}", True
.SendKeys "{TAB}", True
.SendKeys "{TAB}", True
.SendKeys "{ENTER}", True
.CommandBars("File").Controls("Print...").Execute
End With
End Sub

daniels012
10-22-2007, 08:08 AM
No Luck.
I have put the code in a module1 then moved it to ThisWorkbook. I am not sure if any of this matters but I tried them and no change.

When I run your code it just brings up the print Dialog???
Michael

johnske
10-22-2007, 08:13 AM
put it in module1, not the ThisWorkbook module - are you waiting? (it won't come up instantly, it may take a few seconds to appear)

daniels012
10-22-2007, 08:16 AM
i did move it back to Module 1
waited about 3 minutes... Nothing?

Michael

johnske
10-22-2007, 08:30 AM
Few seconds only (5 or 10 at most)... dunno what's going on there, all the code is doing is mimicing the manual operations you're using to bring up the properties dialog.

You may need to revisit the shell option - have a look here (http://scriptorium.serve-it.nl/view.php?sid=24) and if you can't get that to work you may need to find an API that does this...

daniels012
10-22-2007, 12:02 PM
I have been refered to that site. This is where I get the error message in previous posts
Not sure why your code will not work?

Michael

johnske
10-22-2007, 03:30 PM
another sendkeys variation, but if the previous didn't work i don't hold out much hope for this

Sub ShowPrinterProperties()
With Application
.SendKeys "{TAB 6}", True
.SendKeys "~", True
.CommandBars("File").Controls("Print...").Execute
End With
End Sub

daniels012
10-22-2007, 04:44 PM
I tried your most recent code... No luck :(

Not sure if this matters, but when the code does execute, It stops at the print window with the "Number of copies" block highlighted.
So it seems the code doesn't gp all the way through??

Keep in mind I am not that great with code, I am just taking guesses.

Thank you,
Michael

johnske
10-22-2007, 04:52 PM
but that's just the same as using

Application.CommandBars("File").Controls("Print...").Execute
on its' own. in other words, sendkeys is being ignored

johnske
10-22-2007, 05:02 PM
or maybe it's just the Application.Sendkeys being ignored, so try

Sub ShowPrinterProperties()
SendKeys "{TAB 6}"
SendKeys "~"
Application.CommandBars("File").Controls("Print...").Execute
End Sub

daniels012
10-22-2007, 05:15 PM
Wow,
That worked. Can you explain to me why?

Michael

johnske
10-22-2007, 05:41 PM
Sendkeys sends the keystrokes to the keyboard buffer so it's a windows version thing, for instance, with Windows 95/98/ME you need SendKeys on it's own. for NT/2000/XP you need to use Application.SendKeys

However it's a bit flakey, for instance I'm using XP, and SendKeys and Application.SendKeys both work for this :)

Ivan F Moala
10-23-2007, 04:57 AM
Micheal

You have cross posted @ Mrexcel where I provided an anwser.
http://www.mrexcel.com/board2/viewtopic.php?t=297666&start=10

Please don't waste peoples time and effort. It would be appropriate to
tell us all up front.

daniels012
10-23-2007, 07:46 AM
hello Ivan,
As I tried to explain to Norie, I really didn't think and Excel site and a VBA site were "cross posted". The way I see it if I can get this out on 6 different sites, then I have 6 times as many people trying to give me a solution. You may have a favorite site you are on all the time. Where someone else may be elsewhere.

As far as your solution, I get the SAME ERROR message I have been getting with your solution. In actuality, I got the code provided here using SendKeysd to work.

I have never seen johnske on the Mr. excel site. But he helped me find a solution on this site. All the folks at mr. Excel help me all the time with many of my questions.

I am not trying to be a burden to anyone on either site. I am just trying to utilize all my resources, which I think worked.

johnske
10-23-2007, 02:00 PM
hmmm, perhaps all board owners/admins need to look at actually defining cross-posting more rigidly and showing the rule more prominently. e.g. as (maybe) in the rules on my own board http://xlvba.3.forumer.com/index.php?act=boardrules

Michael, many helpers (e.g. Norie and Ivan) often help out in more than one board. For that matter, I also go to MrExcel and help out sometimes when i have the time and inclination.

It is very frustrating to go to another board and find one persons question that a helper may have spent some time on in one board has already been answered on another board.

This wastes peoples time and the accepted procedure is that you generally don't cross-post, but if you have to cross-post (perhaps because no solution you've been given works and everyone seems to be out of ideas) it is simply courteous to give the link to the cross-post (on both sides) so helpers 1) are not duplicating things that didn't work for you and 2) know when a working solution has been provided.

daniels012
10-24-2007, 05:27 AM
johnske,
Thank you for your insight. I appreciate what you are saying and I understand now. I will indeed from now on when I am looking for a solution and have the need to cross-post I will keep the link between the 2 sites in the posts.

Thank you kindly,
Michael

rory
10-24-2007, 05:50 AM
As a matter of interest, does this work for you:
Sub ShowPrinterProperties()
Call Shell("rundll32 printui.dll,PrintUIEntry /p /n " & Application.ActivePrinter)
End Sub

daniels012
10-24-2007, 07:39 AM
rory,
Thank you for the post. No i get the same error message?

Michael

daniels012
11-01-2007, 11:55 AM
johnske,
I use this:


Sub ShowPrinterProperties()
SendKeys "{TAB 6}"
SendKeys "~"
Application.CommandBars("File").Controls("Print...").Execute
End Sub

Why won't this work when I hit Crtl-P?

Michael

johnske
11-01-2007, 02:24 PM
sorry, don't understand your question - if you have the printer dialog showing why don't you just click the properties button?

daniels012
11-01-2007, 05:42 PM
I actually used this in before Print
Does Crtl-P bypass Before_Print?

Michael

daniels012
11-02-2007, 07:57 AM
I actually have it like this:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
SendKeys "{TAB 6}"
SendKeys "~"
Application.CommandBars("File").Controls("Print...").Execute
End Sub

So I am not sure if I can have the code divert to my code before the the Crtl-P has been pressed.

Michael

mqdias
11-05-2007, 09:06 AM
Hi,

I've taken a look to all posts regarding this question, because i'm searching for the same thing.
In my case, copying Johnske code, i obtain this error: "Could not find the specified object."
This error is related to the line:
Application.CommandBars("File").Controls("Print...").Execute

Thanks for some help!!!

daniels012
11-05-2007, 10:11 AM
Mine Still will not work when Crtl-P is pressed?

michael

mqdias
11-05-2007, 10:49 AM
Sorry daniel, i didn't understand what you said...

daniels012
11-07-2007, 09:41 AM
If I use the keyboard shortcut (Control key and "P") Crtl-P to print my code does not work. I was wondering if there is a work around or what?


Michael

mqdias
11-08-2007, 03:10 AM
In my case i still receiving that same error report, and can't solve the problem. I didn't put the Print... dialog box to open by code yet....