Choosing specific printer in vba

J

JohanG

New Member
Joined
Jun 15, 2007
Messages
6
Hello,

I know there are already a lot of topics about printers, but I can't find the sollution to my problem.

In my program I want some labels to be printed on printer P661 (black/white) and other labels on printer P656 (color).

Q: is it possible to do this in vba for my code?

The labels are made in Word and printed from within Excel.


The Macro Code I use

Code: 
Sub PrintNACL_LABEL() 
Dim oWord As Object 
Dim sPath As String 
Dim iCnt  As Integer 

 sPath = ThisWorkbook.Path & Application.PathSeparator & "NACL_LABEL.doc" 
  
 iCnt = Val(InputBox("Hoeveel exemplaren?", "NACL_LABEL", 1)) 

 If iCnt >= 1 Then 
  
    Set oWord = CreateObject(Class:="Word.Application") 
    
    With oWord.Documents.Open(sPath) 
        .PrintOut Background:=False, Copies:=iCnt 
        .Close False 
    End With 
    
    oWord.Quit False 
 End If 

 Set oWord = Nothing 
End Sub


The specific printers in the network are:

Color ---> "\\Plantijn\P656 op Ne00:"
Black/white ---> "\\plantijn2000old\P661 op Ne02:"


How do I have to adjust the code above to print on P656 when the active printer in windows is P661?



Thanks in advance
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
I don't know how you could do this automatically but you could use

Code: 
Dim bresponse As Boolean
bresponse = Application.Dialogs(xlDialogPrinterSetup).Show
If bresponse = False Then
    MsgBox "User cancelled"
    Exit Sub
End If

before your .PrintOut statement to allow the user to select the printer.
 
Upvote 0
Doesn't seem to work, anyway, thx for the answer.


I can make a macro code to print on different printers, but can't manage to use it in the code above.

Code: 
Sub PRINTER656()

    Application.ActivePrinter = "\\Plantijn\P656 op Ne00:"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
        "\\Plantijn\P656 op Ne00:", Collate:=True
End Sub


Sub PRINTER661()

    Application.ActivePrinter = "\\plantijn2000old\P661 op Ne02:"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
        "\\plantijn2000old\P661 op Ne02:", Collate:=True
End Sub


Anyone else the sollution to this problem?
 
Upvote 0
Hi

You can assign the activeprinter within the oWord application using the same syntax:

Code: 
oWord.Application.ActivePrinter = "\\Plantijn\P656 op Ne00:"

'or

oWord.Application.ActivePrinter = "\\plantijn2000old\P661 op Ne02:"
 
Upvote 0
Hi, thx for the fast reply.

I will try it immediatly tomorrow at work.

So it should look like this


Code: 
Sub PrintNACL_LABEL() 
Dim oWord As Object 
Dim sPath As String 
Dim iCnt  As Integer 

 sPath = ThisWorkbook.Path & Application.PathSeparator & "NACL_LABEL.doc" 
  
 iCnt = Val(InputBox("Hoeveel exemplaren?", "NACL_LABEL", 1)) 

 If iCnt >= 1 Then 
  
    Set oWord = CreateObject(Class:="Word.Application") 
    
        oWord.Application.ActivePrinter = "\\Plantijn\P656 op Ne00:"

    With oWord.Documents.Open(sPath) 
        .PrintOut Background:=False, Copies:=iCnt 
        .Close False 
    End With 
    
    oWord.Quit False 
 End If 

 Set oWord = Nothing 
End Sub
 
Upvote 0
This seems to be a tough one to solve, because it doesn't work as suggested.
I tried the above code in different positions without result. Can't manage to print on another than the default printer.

Perhaps someone could try it out with another Word doc?


THX in advance
 
Upvote 0
hello,

with the following code I can choose the amount of prints and then I get the error code 5216, Printer error

Code: 
"\Sub PrintNACL_LABEL()
Dim oWord As Object
Dim sPath As String
Dim iCnt  As Integer

 sPath = ThisWorkbook.Path & Application.PathSeparator & "NACL_LABEL.doc"
  
 iCnt = Val(InputBox("Hoeveel exemplaren?", "NACL_LABEL", 1))

 If iCnt >= 1 Then
  
    Set oWord = CreateObject(Class:="Word.Application")
    
        oWord.Application.ActivePrinter = "\\Plantijn\P656 op Ne00:"

    With oWord.Documents.Open(sPath)
        .PrintOut Background:=False, Copies:=iCnt
        .Close False
    End With
    
    oWord.Quit False
 End If

 Set oWord = Nothing
End Sub

Is it possible to post the code that works for you? Getting desperate.

thx
 
Upvote 0
This was the sample code I used:

Code: 
Sub test()
Set oword = CreateObject(Class:="Word.Application")
 oword.Visible = True
oword.Application.ActivePrinter = "Microsoft Office Document Image Writer on Ne00:"

    With oword.Documents.Open("C:\test1\WordTest.doc")
        .PrintOut Background:=False, Copies:=2
        .Close False
    End With
    
oword.Quit False
End Sub

It prints successfully when run.
 
Upvote 0
Can't get it to work.

I suppose it's a network problem or an incompatibility with word.

I can make macro's to print excel pages to different printers, but when a word doc is opened it doesn't work.
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question
VBA to print to a label with certain size, every time, on a network printer
Replies
1
Views
8K
jmacleary
jmacleary
M
  • Question
Change Printer and set print options
Replies
1
Views
292
XLfriend777
XLfriend777
M
  • Question
Print Button Macro - Need to change the printer location.
Replies
0
Views
209
mbrackey
M
J
  • Question
EXCEL2010-VBA-Setting a printer/port question
Replies
6
Views
5K
jackrussell007
J
S
  • Question
VBA Print - Mail Merge Refresh
Replies
1
Views
2K
VHS01
VHS01

Forum statistics

Threads
1,215,895
Messages
6,127,624
Members
449,390
Latest member
joan12

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top