Printing Via By Macro for Network Printer

[Kev - Radio Man]

Hello,
I am in need of some help. I have searched this group and found a macro that
has allowed me to print to a certain printer, even if it is the default one.
However I wish to expand on it to allow me to chose how many copies I want
and whether I want it double sided.
I have found that if I use copies:=# in the programing, I can have a button
work, but I don't want to make seperate macro buttons for 1x 2x 3x ... 6x.
I have not been able to find how to double-side print. I have my default for
that printer double-side, but it does not always do this.

So in final, summary.
I want to have the page print to a certain printer, which is working inside
the macro okay (automatically switches and then returns to default printer,
as required). Once the macro is pressed an option pops up for number of
copies. Double-sided will be within the macro program.

Hope that explains it. Regards Kevin.

 

[JLatham]

For the number of copies, you just need a couple of lines of code near the
beginning like:

Dim NumCopies As Integer
NumCopies = InputBox("Enter # of Copies to print:", "Print Copies", 0)
If NumCopies = 0 Then
Exit Sub ' gives you a chance to quit by entering zero
End If
'... your existing code, just use NumCopies with
'the copies:= portion, as copies:=NumCopies
'
I'm not certain you can provide the instruction to print duplex - you might
record a macro while doing it manually and take a look at the code generated.

 

[Kev - Radio Man]

JLatham thanks for your reply.
This is the code I used. Could you please advise the best position to insert
your code?

Sub Print_Log()
Dim str As String
Dim strNetworkPrinter As String
str = Application.ActivePrinter

strNetworkPrinter = GetFullNetworkPrinterName("\\nzsaklprint\pr07b22")
If Len(strNetworkPrinter) > 0 Then
Application.ActivePrinter = strNetworkPrinter
ActiveSheet.PrintOut Copies:=1
End If

Application.ActivePrinter = str
End Sub


Function GetFullNetworkPrinterName(strNetworkPrinterName As String) As String
Dim strCurrentPrinterName As String, strTempPrinterName As String, i As
Long
strCurrentPrinterName = Application.ActivePrinter
i = 0
Do While i < 100
strTempPrinterName = strNetworkPrinterName & " on Ne" & Format(i,
"00") & ":"
On Error Resume Next ' try to change to the network printer
Application.ActivePrinter = strTempPrinterName
On Error GoTo 0
If Application.ActivePrinter = strTempPrinterName Then
GetFullNetworkPrinterName = strTempPrinterName
i = 100 ' makes the loop end
End If
i = i + 1
Loop
Application.ActivePrinter = strCurrentPrinterName ' change back to
the original printer
End Function

Regards Kevin.