Link to home
Create AccountLog in
Avatar of Nancy Therrien
Nancy TherrienFlag 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.
Avatar of crystal (strive4peace) - Microsoft MVP, Access
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 ...
Avatar of Nancy Therrien

ASKER

No the user print a lot of thing and need to change the paper color when use the manual feeder
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.
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............
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.
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........
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
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
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?
I don't need to create or edit a Template.  Just need the choose the good printer......
ASKER CERTIFIED SOLUTION
Avatar of crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
thanks for your help.  Will try this soon.
you're welcome ~ happy to help