Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    637
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cntrolling printer from code (2003 SP3)

    I was wondering if anyone has found a simple way of controlling printers from vba? Things I want to select are; the printer, paper size, duplex or not, colour or mono, & paper tray. If that's not possible, does anyone know of any add ons that can facilitate this?
    Regards,
    Peter

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cntrolling printer from code (2003 SP3)

    Look up the Printer object in the Access VBA help, or Printer Object (Access 2003 VBA Language Reference) for the Web version.

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    637
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cntrolling printer from code (2003 SP3)

    Thanks for that Hans. But I’m still stuck.
    I have a new printer and am trying to print A5 birthday cards for clients on it. The printer totally ignores the Prt. .PaperBin. No matter what I set it to the default bin is used unless the Multi-Purpose bin is open, in which case that is used. I can work around that, but the MP bin can only take up to 8 envelopes at a time. If the envelopes run out, the stupid printer takes the next feed from the A4 bin. A4 sheets will never make an envelope. OK I can work around that as long as I can get the Word mail merge doc to print in batches of 7. But here’s where I get stuck.
    With appWd.ActiveDocument.MailMerge
    For x = 1 To Num
    If .State <> wdMainAndDataSource Then Stop
    .DataSource.ActiveRecord = x
    .Destination = wdSendToPrinter
    .Execute
    If x Mod NumInBatch = 0 And x <> Num And x <> 1 Then
    MsgBox “Reload 8 envelopes", vbOKOnly
    End If
    Next x
    End With 'appWd.ActiveDocument.MailMerge
    The line' If .State <> wdMainAndDataSource Then Stop', stops the code. Yet Debug.Print appWd.ActiveDocument.name gives the correct doc. If I open the doc manually it is correctly connected to its datasource.
    The line '.DataSource.ActiveRecord = x', gives the error ‘Requested object is not available.’
    What am I doing wrong?
    Regards,
    Peter

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cntrolling printer from code (2003 SP3)

    Printers often use different bin (tray) numbers than the built-in constants. You can find out the bin numbers used by your printer by recording a macro in Word and selecting a bin in File | Page Setup..., then studying the generated code. If you see something like

    .FirstPageTray = 260

    you can use the same number in Access.

  5. #5
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    637
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cntrolling printer from code (2003 SP3)

    Thanks Hans.
    Unfortunately Word didn’t give the bin (tray) number, even though I tried it a few times, changing the bin each time.
    Code copied from the Word macro:
    Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
    wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
    ManualDuplexPrint:=False, Collate:=True, Background:=True, PrintToFile:= _
    False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
    PrintZoomPaperHeight:=0

    I tried it in Excel. Didn’t show the bin (tray) number either.
    Code copied from Excel macro:
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True


    Do you have any idea on what is wrong with the code in my initial post?
    Unless that can be fixed, my new printer is a bit of a lemon.
    Regards,
    Peter

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cntrolling printer from code (2003 SP3)

    You have recorded a macro of printing a document. Try recording a macro of setting the page setup (File | Page Setup...), as I indicated in my previous reply.

  7. #7
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    637
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cntrolling printer from code (2003 SP3)

    Thanks Hans.
    Attached Images Attached Images
    Regards,
    Peter

Posting Permissions

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