I have made a programme to print chaques through excel report.
i have network printer “Xerox Phaser 3435 PCL 6” (inkjet) which is my default printer
I have local printer is “Epson LQ-300 ESC/P 2” (dot-matrix) and used only to print chaques.

every time when i want to print chq. i have to change my default printer and againt after printing reset default printer.

How can i print cheaques to local printer without selecting printer and without changing my default printer.

Private Sub Command1_Click()
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)

oSheet.Range("A1").RowHeight = 12#
oSheet.Range("A2").RowHeight = 12#
oSheet.Range("A3").RowHeight = 18#
oSheet.Range("R3:U3").MergeCells = True
oSheet.Range("R3:U3").Font.Size = 12
oSheet.Range("R3:U3").HorizontalAlignment = xlLeft
oSheet.Range("R3:U3").VerticalAlignment = xlCenter
oSheet.Range("R3:U3").Font.Name = "Times New Roman"
oSheet.Range("R3").Value = Text1.Text
oSheet.Range("A4").RowHeight = 15.75
oSheet.Range("A5").RowHeight = 19.5
oSheet.Range("A6").RowHeight = 24
oSheet.Range("D6:S6").MergeCells = True
oSheet.Range("D6:S6").Font.Size = 12
oSheet.Range("D6:S6").HorizontalAlignment = xlLeft
oSheet.Range("D6:S6").VerticalAlignment = xlCenter
oSheet.Range("D6:S6").Font.Name = "Times New Roman"
oSheet.Range("D6").Value = Text2.Text
oSheet.Range("A7").RowHeight = 20.25
oSheet.Range("B7:N7").MergeCells = True
oSheet.Range("B7:N7").Font.Size = 12
oSheet.Range("B7:N7").HorizontalAlignment = xlLeft
oSheet.Range("B7:N7").Font.Name = "Times New Roman"
oSheet.Range("B7").Value = "   " & Text4.Text
oSheet.Range("R7:U8").MergeCells = True
oSheet.Range("R7:U8").Font.Size = 12
oSheet.Range("R7:U8").HorizontalAlignment = xlLeft
oSheet.Range("R7:U8").VerticalAlignment = xlCenter
oSheet.Range("R7:U8").Font.Name = "Times New Roman"
oSheet.Range("R7").Value = Text3.Text
oSheet.Range("A8").RowHeight = 24
oSheet.Range("A8: P8").MergeCells = True
oSheet.Range("A8: P8").Font.Size = 12
oSheet.Range("A8: P8").HorizontalAlignment = xlLeft
oSheet.Range("A8: P8").VerticalAlignment = xlCenter
oSheet.Range("A8: P8").Font.Name = "Times New Roman"
oSheet.Range("A8").Value = Text5.Text

oSheet.PageSetup.PrintArea = "$A$1:$U$11"

oSheet.PrintOut
    
oExcel.Quit
Set oBook = Nothing
Set oExcel = Nothing
MsgBox "done"
End Sub

You have to use the full syntax of the Worksheet.PrintOut method. You can find the full syntax explanation in the Excel Object Model Reference guide.

expression.PrintOut(From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName, IgnorePrintAreas)

Every parameter is optional. So, as you can see, one of the available parameters is ActivePrinter. So, if you know the network name (or local name, for that matter) of your printer, just use it as the ActivePrinter parameter.

If you don't know the name, just put in some Debug.Print statements in your code, then run your code and change the active printer manually, then check the results. Then you can hard-code your printer name where appropriate.

You can reference the active printer via the Excel.Application.ActivePrinter object. For your code above, it would be something like:

debug.print oExcel.ActivePrinter

Hope this helps!

You use the Set Printer = Printer(Index) to set
a particular printer in an application to be used.
You need to do this before setting the other properties.
And, of course, before printing.

You'll need a listbox, and a command button to use this
example.

Private PrinterNames(10) As String
Private prn As Printer
Private intPrinterToUse As Integer

' After Clicking the listbox to select a printer,
' this command will set the printer for the current application.
Private Sub Command1_Click()
    Set Printer = Printer(intPrinterToUse)
End Sub

' This cycles through the list of available printers in the
' Windows Operating system. As it cycles through it saves the
' printer name along with its index. The index will correspond to
' the same index used as the index in the Printers Collection object.
Private Sub Form_Load()
    Set prn = Printer
    Dim i As Integer
    ' Populate an array of Printer Names
    
    For Each prn In Printers
        List1.AddItem CStr(i) & " " & prn.DeviceName
        PrinterNames(i) = i & " " & prn.DeviceName
        i = i + 1
    Next
End Sub

' Click on a printer to save the index for future use.
Private Sub List1_Click()
    intPrinterToUse = List1.ListIndex
End Sub

hi hkdani

when i click on command botton i get error

Run time error "438"
object dosent support this property or method.

and it hilite below text in command code

Set Printer = Printer(intPrinterToUse)

Thanks

Correction: Set Printer = Printers(intPrinterToUse) It's supposed to be Printers. Printers is the collection of printers on your system.

Edited 5 Years Ago by hkdani: n/a

This article has been dead for over six months. Start a new discussion instead.