PDA

View Full Version : Select network printer for printing Word doc



JohanG
06-20-2007, 02:11 AM
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.

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:" = DEFAULT PRINTER


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

mdmackillop
06-20-2007, 03:49 AM
Try
Application.activeprinter = "\\Plantijn\P656 (file://\\Plantijn\P656) op Ne00:"

JohanG
06-20-2007, 09:04 AM
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
06-20-2007, 03:01 PM
What about this one ?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 (file://\\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

JohanG
06-21-2007, 12:40 AM
Hi,

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

Charlize
06-21-2007, 01:02 AM
Hi,

negative, I already tried that one without succes.
I'm getting desperate about this. :banghead:I'm not sure whyoWord.ActivePrinter = "\\Plantijn\P656 (file://plantijn/P656) op Ne00:"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
06-21-2007, 01:18 AM
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/viewtopic.php?t=277770&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
06-21-2007, 01:27 AM
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 (file://\\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

JohanG
06-21-2007, 02:24 AM
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
06-26-2007, 06:18 PM
:clap: Ok, thx guys :clap:

the problem is solved.

When removing "op Ne00" the macro works.

:bow: :bow: :bow: :bow: :bow: :bow: :bow: :bow: :bow:

JohanG
06-27-2007, 08:01 PM
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)



Dim sPrinterOriginal As String


sPrinterOriginal = Application.ActivePrinter


printing code


Application.ActivePrinter = sPrinterOriginal


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

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 (file://\\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

Charlize
06-27-2007, 11:32 PM
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)
Dim sPrinterOriginal As String


sPrinterOriginal = Application.ActivePrinter


printing code


'Application.ActivePrinter = sPrinterOriginal
Application.ActivePrinter = Left(sPrinterOriginal, Len(sPrinterOriginal) - 9)
Can anyone tell me how to do this with the following macroSub 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 (file://\\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

JohanG
06-28-2007, 07:09 PM
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
06-28-2007, 11:58 PM
Maybe change

Application.ActivePrinter = Left(sPrinterOriginal, Len(sPrinterOriginal) - 9)with
Oword.ActivePrinter = Left(sPrinterOriginal, Len(sPrinterOriginal) - 9)
otherwise, show the code you are using.

JohanG
06-29-2007, 02:11 PM
:thumb :thumb Thx a lot Charlize. :thumb :thumb

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

:bow: :bow: :bow: :bow: :bow: :bow: :bow: :bow: :bow: :bow:

Charlize
06-30-2007, 03:48 PM
:thumb :thumb Thx a lot Charlize. :thumb :thumb

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

:bow: :bow: :bow: :bow: :bow: :bow: :bow: :bow: :bow: :bow::clever: