Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Red face Unanswered: How to create new printer settings in Excel

    Good morning,

    I have been given a spreadsheet that when done entering all of the data needs to be printed out. However, it will have to use something other than my default printer settings. It will have to do this:
    Document size will be default 8.5" x 11"
    Change the Input tray to Tray 2
    Change the Job Type from Locked Print to NORMAL
    Set it up to print in DUPLEX and Open to Top
    Then Close and Print.

    How can this all be done in possibly VBA? I have no clue as I don't really work in Excel that much, but since I have been given this to use and the settings will change each time I was hoping to automate the process. Reason: It's a Shared Workbook and others will be updating and printing it as well and the printer is stocked with different colored paper for this project also. In case it matters, the printer is a Lanier LD255 networked laser B/W printer. If you could lend a hand on this one I'd appreciate it a lot.

    have a nice one,
    BUD

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Hi Bud,

    I will ask the obvious question first, Have you tried recording the macro.

    All i have everdone is set the pageoptions and used printout to use the printer.
    But as for tray etc. this is just something i have never done.

    All the Best
    David

  3. #3
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by DavidCoutts
    Hi Bud,

    I will ask the obvious question first, Have you tried recording the macro.

    All i have everdone is set the pageoptions and used printout to use the printer.
    But as for tray etc. this is just something i have never done.

    All the Best
    David
    Hi David and thanks,

    Geez, I forgot all about the Macro thingy. Kinda been using VBA in Access and long since gotten away from those things. I'll look into that and again,
    thanks very much and have a wonderful day.

    Bud
    I'll let you know how it worked out.

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    You too
    and remember to tidy up your code cause the recorder will spew out a lot of nonsense as well.

  5. #5
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by DavidCoutts
    You too
    and remember to tidy up your code cause the recorder will spew out a lot of nonsense as well.
    Well I just tried it twice and it didn't work like I thought it would. I started the Macro Recorder, went through the motions, saved it and it didn't do it. When I went to review the Macro, this is all that was captured by the recorder:

    Code:
    Sub Special()
    '
    ' Special Macro
    ' Macro recorded 11/30/2007 by Southwest airlines. Special Printing
    '
    ' Keyboard Shortcut: Ctrl+s
    '
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    End Sub
    What am I doing wrong? OR will Excel NOT record mouse movements???

    BUD

  6. #6
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Quote Originally Posted by Bud

    What am I doing wrong? OR will Excel NOT record mouse movements???

    BUD
    Nothing, it looks as though excel doesn't interact with the printers print options.
    We might be stumped with regards to excel unless we go down the route of attempting to find windows commands to set these options for the printer.

    I will have a think.

  7. #7
    Join Date
    Oct 2003
    Posts
    1,091
    My guess is that it will require APIs to call the printer specifics, since each printer has its own. I don't have access to it right now, but I think John Walkenbach in Power Programming has a chapter on Windows APIs and something on printers.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  8. #8
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Ive done some reaserch and printers seem to be a bit of a pest.

    See For selecting a printer tray
    See for duplex
    see For Paper Size

    Good Luck and let us know how you get on.

  9. #9
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by DavidCoutts
    Ive done some reaserch and printers seem to be a bit of a pest.

    See For selecting a printer tray
    See for duplex
    see For Paper Size

    Good Luck and let us know how you get on.

    Thanks to you both David and Rich for your assistance. I really wish they would let me re-create the entire thing in Access. I'll see what I can rig up possibly.

    have a nice one,
    BUD

  10. #10
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by Bud
    Well I just tried it twice and it didn't work like I thought it would. I started the Macro Recorder, went through the motions, saved it and it didn't do it. When I went to review the Macro, this is all that was captured by the recorder:

    Code:
    Sub Special()
    '
    ' Special Macro
    ' Macro recorded 11/30/2007 by Bud. Special Printing
    '
    ' Keyboard Shortcut: Ctrl+s
    '
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    End Sub
    What am I doing wrong? OR will Excel NOT record mouse movements???

    BUD
    it's all done

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •