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.............
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
activeprinter
though you need to trim off the port left(activeprinter, instr(activeprinter, " on ")-1)
or you can use the getdefaultprinter API
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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,
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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 do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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.
i did not know if it was possible to install an additional shared printer they all could use
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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
Don't forget to use [CODE]your code here[/CODE] when posting code
If your question was answered please use Thread Tools to mark your thread [RESOLVED]
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's
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.
Last edited by Phenix; Jul 12th, 2011 at 04:54 PM.
Reason: Added 2007
Circa 1995
Engineer - I think we should put our website address on our paper catalogs.
Vice President - Don't get too excited about this internet thing.
I am sorry, but the Oracle was mistaken. You cannot help us.
-Matrix video game
I'm doing a (free) operating system (just a hobby, won't be big and professional like gnu) for 386(486) AT clones. ... and it probably never will support anything other than AT-harddisks, as that's all I have :-(.
-Linus
Question. Do you know that the character "?" means I'm asking a question? Question. Do you know that spoken inflection also provides the same cue? So please don't say, "Question" before you ask your question. Believe me I'll know.
That said, I would have said this first if it had to precede what I'm telling you now. Having said that, what I'm telling you now is the same thing I just said about the annoying phrases "That said" and "Having said that".
Are you threatening me, Master Jedi?
-Chancellor Palpatine
Reply With Quote
Originally Posted by westconn1
