Select network printer for printing Word doc

[JohanG]

Hi I can't manage to print to a specific network printer.

I have several word documents and now I want to assign a macro to a button which opens this word document, print it to a specific printer (because some docs have to be printed in color = other than default printer) and close this word doc.

The following code works fine to print on the default printer, but I can't find out how to print to a different one.

[vba]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[/vba]


The specific printers in the network are:

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


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

[mdmackillop]

Try
Application.activeprinter = "\\Plantijn\P656 op Ne00:"

[JohanG]

hi,

thx for the fast reply, but that code I already tried, but gives an error message "5216" I think.

I can use Application.activeprinter = "\\Plantijn\P656 op Ne00:" when printing data from excel, but the macro above opens a Word doc and that seems to be the problem.
When opening a Word file with Excel I only can print to the default printer in the network, I can't figure out how to enclose a code to change the printer.

All things I tried gave me error codes. When I want to print something on the color printer in the network "P656", I always have to change the printer settings before opening Excel.

Anyone a sollution to this problem?

[Charlize]

What about this one ?[VBA]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.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[/VBA]

[JohanG]

Hi,

negative, I already tried that one without succes.
I'm getting desperate about this.

[Charlize]

Hi,

negative, I already tried that one without succes.
I'm getting desperate about this.

I'm not sure why[vba]oWord.ActivePrinter = "\\Plantijn\P656 op Ne00:"[/vba]wouldn't work. The name that you use must be the name that you can see in Word when you change the printer manually. All I can say is that it works fine for me.

"Plantijn" is probably the name of your server and "P656" is you printername. Maybe drop " Ne00:" and see what that gives.

ps. maybe better save the active printer of word to a temp holder and restore that setting after you have finished doing what you must do.

[RichardSchollar]

Johan

It would have been helpful to the forum observers here had you referenced the cross-post you made at MrExcel:

http://www.mrexcel.com/board2/viewto...770&highlight=

As then they would have seen that their suggested solutions had already been provided but had failed to work on your computer. Instead, you have wasted their time and yours, going over old ground.

Richard

[Charlize]

[VBA]Sub view_active_printer_from_excel_in_word()
Dim oWord As Object
Dim Old_printer As String
'get current printer
Old_printer = Application.ActivePrinter
Set oWord = CreateObject("Word.Application")
MsgBox "Active printer is : " & oWord.ActivePrinter
'Change printer. Name must be exactly the same
oWord.ActivePrinter = "\\Plantijn\P656"
MsgBox "Active printer is : " & oWord.ActivePrinter
'restore old printer
oWord.ActivePrinter = Left(Old_printer, Len(Old_printer) - 9)
MsgBox "Active printer is : " & oWord.ActivePrinter
oWord.Quit False
Set oWord = Nothing
End Sub[/VBA]

[JohanG]

Thx for all the fast answers.

@RichardSchol -> sorry about it, didn't know about that

@ Charlize
I will try it out next week, when back at work.

I didn't knew there could be a difference when printing from Excel or Word.

Have a nice WE

[JohanG]

Ok, thx guys

the problem is solved.

When removing "op Ne00" the macro works.

[JohanG]

now I can change from default printer (P661), to print certain docs on a different printer (P656) but .... this changes the default printer in windows.
I'm not able to change it back to default printer with vba (did some search)

[vba]

Dim sPrinterOriginal As String

sPrinterOriginal = Application.ActivePrinter

printing code

Application.ActivePrinter = sPrinterOriginal

[/vba]

Can anyone tell me how to do this with the following macro

[vba]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

MsgBox "LABELS WORDEN AFGEDRUKT OP" & vbCr & "KLEURENPRINTER " & vbCr & " " & vbCr & "!!! ETIKETTEN NAAR BOVEN LEGGEN !!!", vbExclamation, Title:="AFDRUKKEN MEDICATIELABELTJES"

Set oWord = CreateObject(Class:="Word.Application")
oWord.ActivePrinter = "\\Plantijn\P656"
With oWord.Documents.Open(sPath)
.PrintOut Background:=False, Copies:=iCnt
.Close False
End With

oWord.Quit False
End If
Set oWord = Nothing
End Sub[/vba]

[Charlize]

Maybe you didn't searched on the right places (in this thread was the answer to your question).

now I can change from default printer (P661), to print certain docs on a different printer (P656) but .... this changes the default printer in windows.
I'm not able to change it back to default printer with vba (did some search)
[vba]Dim sPrinterOriginal As String

sPrinterOriginal = Application.ActivePrinter

printing code

'Application.ActivePrinter = sPrinterOriginal
Application.ActivePrinter = Left(sPrinterOriginal, Len(sPrinterOriginal) - 9)

[/vba]Can anyone tell me how to do this with the following macro[vba]Sub PrintNACL_LABEL()
Dim oWord As Object
Dim sPath As String
Dim iCnt As Integer

Dim sPrinterOriginal As String

sPrinterOriginal = Application.ActivePrinter

sPath = ThisWorkbook.Path & Application.PathSeparator & "NACL_LABEL.doc"

iCnt = Val(InputBox("Hoeveel exemplaren?", "NACL_LABEL", 1))
If iCnt >= 1 Then

MsgBox "LABELS WORDEN AFGEDRUKT OP" & vbCr & "KLEURENPRINTER " & _
vbCr & " " & vbCr & "!!! ETIKETTEN NAAR BOVEN LEGGEN !!!", vbExclamation, Title:="AFDRUKKEN MEDICATIELABELTJES"

Set oWord = CreateObject(Class:="Word.Application")
oWord.ActivePrinter = "\\Plantijn\P656"
With oWord.Documents.Open(sPath)
.PrintOut Background:=False, Copies:=iCnt
.Close False
End With

Application.ActivePrinter = Left(sPrinterOriginal, Len(sPrinterOriginal) - 9)

oWord.Quit False
End If
Set oWord = Nothing
End Sub[/vba]

[JohanG]

Hi,
hoped the suggested code would work, but .... gives an error 1004

"Fout 1004 tijdens uitvoering:

Methode ActivePrinter van object_Application is mislukt"

Anyway, thanks for the quick response.



Question: why isn't it possible to use "Application.Dialogs(xlDialogPrinterSetup).Show" -> this code shows the active printers, but selecting another than the default printer results in printing at the default.

[Charlize]

Maybe change [VBA]

Application.ActivePrinter = Left(sPrinterOriginal, Len(sPrinterOriginal) - 9)[/VBA]with[VBA]

Oword.ActivePrinter = Left(sPrinterOriginal, Len(sPrinterOriginal) - 9)

[/VBA]otherwise, show the code you are using.

[JohanG]

Thx a lot Charlize.

Tried it immediatly at work and ..... it works !!!!

[Charlize]

Thx a lot Charlize.

Tried it immediatly at work and ..... it works !!!!