Avatar of Nancy Therrien
Nancy Therrien
Flag for Canada asked on

VBA Printing problem

Trying to print to the manuel feeder of the printer, and it doesn't work.....
If I do record a macro and do All my steps it will work but the time after when I will run the macro, that won't work.

Here is my code :

Sub Macro1()
'
' Macro1 Macro
'
 'Impression en utilisatn le passe copie de l'imprimante
 
'
    Sheets("Feuille Chariot").Select
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = "$A$2:$BA$39"
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0)
        .RightMargin = Application.InchesToPoints(0.15748031496063)
        .TopMargin = Application.InchesToPoints(0.31496062992126)
        .BottomMargin = Application.InchesToPoints(0.354330708661417)
        .HeaderMargin = Application.InchesToPoints(0.236220472440945)
        .FooterMargin = Application.InchesToPoints(0.31496062992126)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Application.PrintCommunication = True
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("Liste de Sciage").Select
End Sub


Thanks in advance for helping.
Microsoft ExcelVBA

Avatar of undefined
Last Comment
crystal (strive4peace) - Microsoft MVP, Access

8/22/2022 - Mon
crystal (strive4peace) - Microsoft MVP, Access

here is a duct-tape solution if you can't get the code to work: empty the paper tray(s) so the printer looks for the manual feed ...
Nancy Therrien

ASKER
No the user print a lot of thing and need to change the paper color when use the manual feeder
crystal (strive4peace) - Microsoft MVP, Access

just a thought since, to use the manual feed, one would be at the printer anyway ... I do this when I can't get a printer to use the manual feed.  I used to print on card stock a lot.  This often jammed the printer when it was fed from the tray so it was quickest, in the long run, to feed it manually.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Nancy Therrien

ASKER
Ok I have create a new printer with all its properties to Manual Feed, all I have to do now is to tell the macro which printer to use............
crystal (strive4peace) - Microsoft MVP, Access

if your printer doesn't have a tray with legal sized paper, you can do this:
.PageSetup.PaperSize = xlPaperLegal

Open in new window

and the printer will automatically use manual.  Or you can pick another size you don't have a tray for.
Nancy Therrien

ASKER
In my printer I have, letter, legal and 11x17 and 1 papertray............ I can't use what you suggest.

this is my code now :      With Application
      sCurPrinter = .ActivePrinter       ' Store current printer.
      .ActivePrinter = "Konica ManualFeed sur Ne09:" ' Change Printers.
      ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
     ' ActiveDocument.PrintOut            ' Print the active document.
      .ActivePrinter = sCurPrinter       ' Set printer back to current.
   End With
   
   
    Application.PrintCommunication = True

It is doing something, we see the printer name changing but It won't print anywhere........
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
crystal (strive4peace) - Microsoft MVP, Access

here are the object members for PageSetup in Excel:
https://msdn.microsoft.com/EN-US/library/office/ff841253.aspx

I don't see anything to change the paper tray.  

Because trays are tied to sizes, if you pick one you don't have, manual will be used.  There are about 50 sizes on this list, including "custom" ... so surely there is one your printer does not have?

https://msdn.microsoft.com/EN-US/library/office/ff841253.aspx

I'll do a size analysis to see what metric and standard sizes are similar ... post back shortly
crystal (strive4peace) - Microsoft MVP, Access

Perhaps try one of these for size?

xlPaperQuarto (215 mm x 275 mm) is about 8.46" x 10.83" ... so close but slightly smaller.  

xlPaperA4 (210 mm x 297 mm) is 8.27" x 11.69" ... also close and one dimension is slightly larger
Roy Cox

Do you really need to set up the page each time? Create a template that is already formatted. See this

Excel Templates

Also, your recorded macro will fail if the sheet to print has a different name. What error message are you getting?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Nancy Therrien

ASKER
I don't need to create or edit a Template.  Just need the choose the good printer......
ASKER CERTIFIED SOLUTION
crystal (strive4peace) - Microsoft MVP, Access

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Nancy Therrien

ASKER
thanks for your help.  Will try this soon.
crystal (strive4peace) - Microsoft MVP, Access

you're welcome ~ happy to help
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.