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

Last Comment

crystal (strive4peace) - Microsoft MVP, Access

8/22/2022 - Mon

crystal (strive4peace) - Microsoft MVP, Access

1/18/2016

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

1/18/2016

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

1/18/2016

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

1/18/2016

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

1/18/2016

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

1/18/2016

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

1/18/2016

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

1/18/2016

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

1/18/2016

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

1/18/2016

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

1/18/2016

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.