How do I programmatically uncheck the "Print On Both Sides" Checkbox? I've done the "Record A Macro" thing to both check it, and uncheck it, and I can't see one bit of difference in either macro code. Nothing I've tried so far has any effect.
Printable View
How do I programmatically uncheck the "Print On Both Sides" Checkbox? I've done the "Record A Macro" thing to both check it, and uncheck it, and I can't see one bit of difference in either macro code. Nothing I've tried so far has any effect.
i don't believe you can access this directly from vba /vb, you would need to change the default settings for the windows printer, then change back after printing
i am sure you did not want to hear that.............
see this thread for code
http://www.vbforums.com/showpost.php...8&postcount=11
see this thread for an activex dll
http://www.vbforums.com/showthread.php?t=564553
I'm guessing you have no access to the PRINTER object from vba?
no printers collection, only through api
Is there a way to tell if it is checked (even though you can't change it?)
I think I'm going to go with http://www.vbforums.com/showpost.php...8&postcount=11
The next thing I would need is how to, using Excel VBA, feed the function in the above link the default printer name. In VB6 I would useBut, clearly, this is not supported in Excel VBA. What would be the equivalent?Code:Printer.DeviceName
activeprinter
though you need to trim off the port
left(activeprinter, instr(activeprinter, " on ")-1)
or you can use the getdefaultprinter API
Well, thats intuitive :lol:Quote:
Originally Posted by westconn1
Thanks pete.
Is there something besides this that I need to doThis didn't work. It caused no errors, but it also didn't turn on double sided printing.Code:Dim ret As Long
ret = Setduplex(Left(ActivePrinter, InStr(ActivePrinter, " on ") - 1), 3)
hmmm
it would appear that the printer settings are not changed after excel is open, same as going to
control panel > printers and faxes and changing the properties there
i am guessing excel reads the state of the properties when it opens then uses those throughout, unless changed within excel
i tested the code originally using shellexecute, for printing pdfs etc,
So, basically, we are back to "you can't get there from here", right?
I even hard coded my printer name into the function and it still didn't work.Code:ret = Setduplex("\\servername\printername", 3)
i found some other articles on this issue, including
http://support.microsoft.com/kb/828638 which discusses duplex printing in Word, very similar
and
another forum that stated that for the code to work you must have enough permissions, and it must be a local printer driver
the simplest method if it is available to you, is to create an additional copy of the printer, with duplex set as you require, then change activeprinter to that
I could do that for me, but I'm not sure about doing it for 40 or so other people who actually use the application.Quote:
Originally Posted by westconn1
i did not know if it was possible to install an additional shared printer they all could use
Not sure if you have seen this lengthy code written for VBA:
Controlling the Printer from Word VBA by Jonathan West, MVP
Part 2: Using VBA to control Duplex, Color Mode and Print Quality
Yes, I did and a couple others like it.
I didn't try it from Word so I will take it at face value that it works...I can't, however, get it to work in Excel VBA.
word does do some printer things differently from excel
in word
changing activeprinter in word vba changes windows default printer (word 2000), which is not always desired, changing activeprinter in the printer dialog does not do this
That code does not change ActivePrinter, but it can change duplex setting on ActivePrinter.
It can work for both Excel and Word.
Code:'-- iDuplex = 1 : Single-sided printing
'-- iDuplex = 2 : Duplex printing using a horizontal binding
'-- iDuplex = 3 : Duplex printing using a vertical binding
Sub PrintDuplex(ByVal iDuplex As Long)
Dim iCurDuplex As Long
If iDuplex < 1 Or iDuplex > 3 Then iDuplex = 3
Debug.Print "Current Duplex setting:"; GetDuplex()
'-- save the current setting
iCurDuplex = GetDuplex()
If iCurDuplex <> iDuplex Then SetDuplex iDuplex
Debug.Print "New Duplex setting:"; GetDuplex()
'-- print activesheet
'ActiveSheet.PrintOut Copies:=1
'-- restore the original setting
If iCurDuplex <> iDuplex Then SetDuplex iCurDuplex
Debug.Print "Restored Duplex setting:"; GetDuplex()
End Sub
What am I missing?
I'm trying to print double-sided from an Excel 2007 sheet when a user presses my Form Control Button.
Not sure if I should have made a new thread now that I learned that this does not work in newer versions of Excel.
I modified anhn'sAnd used the module code at http://pubs.logicalexpressions.com/P...cle.asp?ID=116 "Main Code for the Article" in a separate module.Code:'-- iDuplex = 1 : Single-sided printing
'-- iDuplex = 2 : Duplex printing using a horizontal binding
'-- iDuplex = 3 : Duplex printing using a vertical binding
Sub Custom_Double_Sided_Print_Click()
Dim iDuplex As Long
iDuplex = 3
Dim iCurDuplex As Long
Debug.Print "Current Duplex setting:"; GetDuplex()
'-- save the current setting
iCurDuplex = GetDuplex()
If iCurDuplex <> iDuplex Then SetDuplex iDuplex
Debug.Print "New Duplex setting:"; GetDuplex()
'-- print activesheet
ActiveSheet.PrintOut Copies:=1
'-- restore the original setting
If iCurDuplex <> iDuplex Then SetDuplex iCurDuplex
Debug.Print "Restored Duplex setting:"; GetDuplex()
End Sub
I added a Debug.Print to SetPrinterProperty's cleanup code and sure enough, the cleanup code is being called which I expect is when it Couldn't access shared printer settings.
I'm attempting to print to a RICOH Aficio MP C6501, but don't know what other printers may be used.
I still get single sided from VBA. I can manually set to double-sided by Properties | Print on Both Sides | Flip on ... Edge; which I'd expect corresponds to vertical or horizontal binding.