none
Color printing and staples code for VBA/Excel

    Question

  • Hi, I am trying to determine the code neccesary to print reports that I have created in excel on a color copier in my office.  The copier has th capabilities of printing in color as well as stapling the pages once printed.  Selecting the printed range and other printing functions I have copied succesfully from recording a macro.  However, when I record the macro and try to update the print in color and allow stapling, that part of the code does not show up.  The code that typically comes is

            .LeftHeader = ""
            .CenterHeader = ""
            .RightHeader = ""
            .LeftFooter = ""
            .CenterFooter = ""
            .RightFooter = ""
            .LeftMargin = Application.InchesToPoints(0.3)
            .RightMargin = Application.InchesToPoints(0.3)
            .TopMargin = Application.InchesToPoints(0.5)
            .BottomMargin = Application.InchesToPoints(0.5)
            .HeaderMargin = Application.InchesToPoints(0.5)
            .FooterMargin = Application.InchesToPoints(0.5)
            .PrintHeadings = False
            .PrintGridlines = False
            .PrintComments = xlPrintNoComments
            .PrintQuality = 600
            .CenterHorizontally = False
            .CenterVertically = False
            .Orientation = xlLandscape
            .Draft = False
            .PaperSize = xlPaperLetter
            .FirstPageNumber = xlAutomatic
            .Order = xlDownThenOver
            .BlackAndWhite = False
            .Zoom = False
            .FitToPagesWide = 2
            .FitToPagesTall = 1
            .PrintErrors = xlPrintErrorsDisplayed

    None of these seem to be helpful.  Any advice? Is there a workaround?  Thank you.

    Monday, May 05, 2008 2:22 PM

Answers

  • I wouldn't attempt the registry mods either.

     

    Your SendKeys syntax looks OK. I think you need to remove the spaces though. Do you get an error, or does it just not work?

    In my case, after %fp the cursor is in the "number of copies" box, so the r actually types r into that box. I need to TAB twice to get to a button before r will actually do the Properties button, or else use <Alt>r (%r in SendKeys).

    After that, of course, I get a completely different printer setup dialog to you so I have no idea what's required after that.

     

    For SendKeys to work, Excel needs to be the active application (not the VBA editor). So you need to run the macro from the Excel menu (or a macro button or shortcut key). If you run it from the VBA editor you'll get the editor's print command, not the Excel one.

     

    If you need it to wait (which is a good idea after any keystroke which calls up a new dialog box), try doing separate sendkeys lines, like the following:

    Code Snippet

    Application.SendKeys "%fpr", True 'Try "%fp%r" instead

    Application.SendKeys "{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}", True 

    Application.SendKeys "c", True 

    Application.SendKeys "{TAB}{TAB}{TAB}{TAB}{TAB}", True 

    Application.SendKeys "t", True 

    Application.SendKeys "{TAB}{TAB}{TAB}{TAB}{TAB}{RETURN}", True 

    Application.SendKeys "{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}", True 

    Application.SendKeys "{RETURN}"

     

    It's probably also a good idea to try adding those lines one at a time to see if each of them ends you where you expect.

     

    Another note: SendKeys works fine for me on my WinXP notebook with Office 2003. I just tried on a Vista machine though, and SendKeys gives a "Permission Denied" error. I think there's probably some security setting blocking SendKeys (in Vista or the Internet Security Suite or Excel on that PC). I didn't investigate further, but if that sounds like your problem then post back to that effect.

    Wednesday, May 07, 2008 10:11 AM

All replies

  • In general, VBA can only manage printer settings that are set by the application (Excel in your case), not settings that are set by the printer driver itself.

     

    So you can set any of the things that are in Excel's "Page Setup" dialog box (such as paper size & print quality), but you can't set the printer specific things from any Options/Advanced/etc buttons which take you to the printer driver settings.

     

    There some (very few) printers which you can use VBA with (it is up to the developer of the printer driver to supply this functionality, and very few bother). On the off-chance that you have one of these, the printer documentation should include info on the printer driver's VBA Object Model. If so, and if you need help interpreting it, then post back. Otherwise I will assume that you don't.

     

    There are two possibilities for workarounds that I know of. They are not for the fainthearted, and if you're new to VBA I don't suggest you try them.

    • Use code to change the registry settings for the printer driver. In addition to being risky (serious problems can be caused by messing around with the registry), I believe that this requires the user to have administration rights on the computer, and on a corporate network the average user probably does not have sufficient authority to do this. An example of this (only to give you an idea - it will NOT WORK for you as it's for a different printer driver completely) can be found here:  Microsoft Document Image Printer - MODI Registry Settings
    • Use SendKeys to replicate the keystrokes that would change the printer driver settings. This is probably the easier of the two, but relies on everyone having much the same setup - same version of Excel, probably same version of Windows and probably same printers installed.
    Tuesday, May 06, 2008 4:26 AM
  • Thank you that is helpful.  It sounds like I dont want to try going the registry route at this point.  I looked at the link for the sendkeys method but I am still unsure how I will get it to apply here.  I don't know if it helps, but my printer allows me to ave a settings file as a .sav .  I have done this but once again, I have to go into my printer properties through the print dialog to load it.  Do you know if there is anyway I can use code to load the file?

    For the sendkeys alternative, the keystrokes neccesary are

    alt f p r --- "%fpr"   --- which opens the print dialog and goes to properties
    tab*10  --- {TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB} --- which gets me to the cell for whether its b/w or color
    c ---  c ---- pulls up the color option
    tab*5  --- {TAB}{TAB}{TAB}{TAB}{TAB} -- gets to the field for stapling
    t ---  t --- select "top-left" in the staple field
    tab*5  --- {TAB}{TAB}{TAB}{TAB}{TAB} -- gets to the ok button to save the changes
    Return --- {RETURN} --- clicks the ok button
    tab*11  --- {TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB} --- get to the cancel button, to exit without printing
    Return --- {RETURN} --- clicks the cancel button

    As you can see, the amount of keystrokes are large and prone to error.  I guess my question is, using sendkeys what is the correct syntax to execute these keystrokes?  I have tried:
    Application.SendKeys "%fpr {TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB} c {TAB}{TAB}{TAB}{TAB}{TAB} t {TAB}{TAB}{TAB}{TAB}{TAB}{RETURN} {TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB} {RETURN}"

    but it doesnt seem to work.  Help please.
    Tuesday, May 06, 2008 12:47 PM
  • I wouldn't attempt the registry mods either.

     

    Your SendKeys syntax looks OK. I think you need to remove the spaces though. Do you get an error, or does it just not work?

    In my case, after %fp the cursor is in the "number of copies" box, so the r actually types r into that box. I need to TAB twice to get to a button before r will actually do the Properties button, or else use <Alt>r (%r in SendKeys).

    After that, of course, I get a completely different printer setup dialog to you so I have no idea what's required after that.

     

    For SendKeys to work, Excel needs to be the active application (not the VBA editor). So you need to run the macro from the Excel menu (or a macro button or shortcut key). If you run it from the VBA editor you'll get the editor's print command, not the Excel one.

     

    If you need it to wait (which is a good idea after any keystroke which calls up a new dialog box), try doing separate sendkeys lines, like the following:

    Code Snippet

    Application.SendKeys "%fpr", True 'Try "%fp%r" instead

    Application.SendKeys "{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}", True 

    Application.SendKeys "c", True 

    Application.SendKeys "{TAB}{TAB}{TAB}{TAB}{TAB}", True 

    Application.SendKeys "t", True 

    Application.SendKeys "{TAB}{TAB}{TAB}{TAB}{TAB}{RETURN}", True 

    Application.SendKeys "{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}", True 

    Application.SendKeys "{RETURN}"

     

    It's probably also a good idea to try adding those lines one at a time to see if each of them ends you where you expect.

     

    Another note: SendKeys works fine for me on my WinXP notebook with Office 2003. I just tried on a Vista machine though, and SendKeys gives a "Permission Denied" error. I think there's probably some security setting blocking SendKeys (in Vista or the Internet Security Suite or Excel on that PC). I didn't investigate further, but if that sounds like your problem then post back to that effect.

    Wednesday, May 07, 2008 10:11 AM