Excel Macro - change printer

I am trying to set up a print macro which automatically print to a
pre-selected printer (LABEL) instead of the default printer on the computer
(KYOCERA).

When complete, the macro must switch back to the default printer (KYOCERA).
This macro is used on several machines in the office and each machine has a
different Ne__ numbers for the the same printers.

ActiveSheet.PageSetup.PrintArea = "$P$8:$Y$13"
Application.ActivePrinter = "label on Ne05:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"label on Ne05:", Collate:=True
Application.ActivePrinter = "KYOCERA FS3820N on Ne06:"

The Ne__ number appears to be the printer's installation order. When you
change printers (as we have needed to do), the Ne number changes rendering
the macro useless. I have had to change these things SEVERAL TIMES on
SEVERAL machines.

I want to find a way to define the KYOCERA and LABEL printers without using
those Ne__ numbers so the same macro will work throughout the office and if
one of the available printers is uninstalled, order changes, the macro will
continue to work. All the machines in question use Windows XP Pro on a
network. This problem did not appear when we were on windows 98!

One possibility is to use API functions to return an array of printer names

http://www.word.mvps.org/FAQs/Macros...lePrinters.htm

Then, loop through the array and see which one contains the word "label" or
"kyocera" (check VBA help for InStr function).


"Samrcat" wrote:

> I am trying to set up a print macro which automatically print to a
> pre-selected printer (LABEL) instead of the default printer on the computer
> (KYOCERA).
>
> When complete, the macro must switch back to the default printer (KYOCERA).
> This macro is used on several machines in the office and each machine has a
> different Ne__ numbers for the the same printers.
>
> ActiveSheet.PageSetup.PrintArea = "$P$8:$Y$13"
> Application.ActivePrinter = "label on Ne05:"
> ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
> "label on Ne05:", Collate:=True
> Application.ActivePrinter = "KYOCERA FS3820N on Ne06:"
>
> The Ne__ number appears to be the printer's installation order. When you
> change printers (as we have needed to do), the Ne number changes rendering
> the macro useless. I have had to change these things SEVERAL TIMES on
> SEVERAL machines.
>
> I want to find a way to define the KYOCERA and LABEL printers without using
> those Ne__ numbers so the same macro will work throughout the office and if
> one of the available printers is uninstalled, order changes, the macro will
> continue to work. All the machines in question use Windows XP Pro on a
> network. This problem did not appear when we were on windows 98!

I just remembered Tom Ogilvy had posted a solution that does not involve API.

http://www.microsoft.com/office/comm...c-ccecf74fe362

"Samrcat" wrote:

> I am trying to set up a print macro which automatically print to a
> pre-selected printer (LABEL) instead of the default printer on the computer
> (KYOCERA).
>
> When complete, the macro must switch back to the default printer (KYOCERA).
> This macro is used on several machines in the office and each machine has a
> different Ne__ numbers for the the same printers.
>
> ActiveSheet.PageSetup.PrintArea = "$P$8:$Y$13"
> Application.ActivePrinter = "label on Ne05:"
> ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
> "label on Ne05:", Collate:=True
> Application.ActivePrinter = "KYOCERA FS3820N on Ne06:"
>
> The Ne__ number appears to be the printer's installation order. When you
> change printers (as we have needed to do), the Ne number changes rendering
> the macro useless. I have had to change these things SEVERAL TIMES on
> SEVERAL machines.
>
> I want to find a way to define the KYOCERA and LABEL printers without using
> those Ne__ numbers so the same macro will work throughout the office and if
> one of the available printers is uninstalled, order changes, the macro will
> continue to work. All the machines in question use Windows XP Pro on a
> network. This problem did not appear when we were on windows 98!

I've looked through those responses (thank you for the references), but I do
not understand enough about them to implement, I am sorry to say! My macros
are basically written with record then I edit as needed.

I found a site that had a promising answer seen here:
http://www.erlandsendata.no/english/...tchangeprinter

Would anyone be willing to coach me in combining my current macro with it?
Assistance on this would be very greatly appreciated.

My current macro (for one machine, changes from default Kyocera to Label
printer and back):

Range("A7:M18").Select
With Selection.Font
.Name = "10 CPI Utility"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
Range("P8:Y11").Select
ActiveSheet.PageSetup.PrintArea = "$P$8:$Y$13"
Application.ActivePrinter = "label on Ne05:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"label on Ne05:", Collate:=True
Application.ActivePrinter = "KYOCERA FS3820N on Ne06:"
Range("A2").Select
End Sub

I had to change printers in a macro which outputted an Excel file as a
PDF file - here's a fragment of the code, for what it's worth:

my_printer = Application.ActivePrinter
Application.ActivePrinter = "Adobe PDF on Ne02:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
ActivePrinter:="Adobe PDF on Ne02:", Collate:=True
Application.ActivePrinter = my_printer

Note the first and last line of the fragment, which you might want to
use in your code rather than explicitly setting the Kyocera printer.

Hope this helps.

Pete

I can see the value of that.
But unfortunately, it still leaves me with defining the "on Ne02" etc for
the printer I am asking to print. That's just the point. I am trying to get
away from using that Ne02, Ne03, etc as that changes on our network, breaking
the macro.

-Samantha

"Pete_UK" wrote:

> I had to change printers in a macro which outputted an Excel file as a
> PDF file - here's a fragment of the code, for what it's worth:
>
> my_printer = Application.ActivePrinter
> Application.ActivePrinter = "Adobe PDF on Ne02:"
> ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
> ActivePrinter:="Adobe PDF on Ne02:", Collate:=True
> Application.ActivePrinter = my_printer
>
> Note the first and last line of the fragment, which you might want to
> use in your code rather than explicitly setting the Kyocera printer.
>
> Hope this helps.
>
> Pete
>
>

That may bring you back to using either API or Tom's suggestion. You have to
capture all of the network printer names, search through them to see which
one has "Label" in the name and set the active printer based on that. You
might check VBA help for a Printers collection. I know Excel 2000 does not
have this collection, but I don't know if it is included in later versions.

If no Printers collection, you have to create a printers collection. I've
reposted Tom Ogilvy's code with the changes I think you'll need (you'll have
to add the code to print what you need with the "Label" printer). I don't
have multiple printers, so I couldn't fully test it.


Sub Test()
Dim sConn As String
Dim WshNetwork As Object
Dim oPrinters As Object
Dim i As Long
Dim avTmp As Variant
Dim MyPrinter As String

#If VBA6 Then
avTmp = Split(Excel.ActivePrinter, " ")
#Else
avTmp = Split97(Excel.ActivePrinter, " ")
#End If

sConn = " " & avTmp(UBound(avTmp) - 1) & " "
Set WshNetwork = CreateObject("WScript.Network")
Set oPrinters = WshNetwork.EnumPrinterConnections
MyPrinter = ActivePrinter

For i = 0 To oPrinters.Count - 1 Step 2
If InStr(1, oPrinters.Item(i + 1), "Label", _
vbTextCompare) Then
ActivePrinter = oPrinters.Item(i + 1) & _
sConn & oPrinters.Item(i)
Exit For
End If
Next

'Print using "Label"

ActivePrinter = MyPrinter

End Sub

Function Split97(sStr As String, sdelim As String) As Variant
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function




"Samrcat" wrote:

> I can see the value of that.
> But unfortunately, it still leaves me with defining the "on Ne02" etc for
> the printer I am asking to print. That's just the point. I am trying to get
> away from using that Ne02, Ne03, etc as that changes on our network, breaking
> the macro.
>
> -Samantha
>
> "Pete_UK" wrote:
>
> > I had to change printers in a macro which outputted an Excel file as a
> > PDF file - here's a fragment of the code, for what it's worth:
> >
> > my_printer = Application.ActivePrinter
> > Application.ActivePrinter = "Adobe PDF on Ne02:"
> > ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
> > ActivePrinter:="Adobe PDF on Ne02:", Collate:=True
> > Application.ActivePrinter = my_printer
> >
> > Note the first and last line of the fragment, which you might want to
> > use in your code rather than explicitly setting the Kyocera printer.
> >
> > Hope this helps.
> >
> > Pete
> >
> >

Hit a bug when I tried to test it.
Method "Active Printer" of object "_Global" failed.
Debug highlighted these lines and I don't understand it so can not
resolve it.

ActivePrinter = oPrinters.Item(i + 1) & _
sConn & oPrinters.Item(i)


I hate to take your time on a headache of mine.
I usually do not give up easily, but must say that if I knew where to go to
pay someone to write this particular macro completely and safely, I would as
I feel this project is over my head. The time saved would justify an
expenditure to get it done.

-Samantha

"JMB" wrote:

> That may bring you back to using either API or Tom's suggestion. You have to
> capture all of the network printer names, search through them to see which
> one has "Label" in the name and set the active printer based on that. You
> might check VBA help for a Printers collection. I know Excel 2000 does not
> have this collection, but I don't know if it is included in later versions.
>
> If no Printers collection, you have to create a printers collection. I've
> reposted Tom Ogilvy's code with the changes I think you'll need (you'll have
> to add the code to print what you need with the "Label" printer). I don't
> have multiple printers, so I couldn't fully test it.
>
>
> Sub Test()
> Dim sConn As String
> Dim WshNetwork As Object
> Dim oPrinters As Object
> Dim i As Long
> Dim avTmp As Variant
> Dim MyPrinter As String
>
> #If VBA6 Then
> avTmp = Split(Excel.ActivePrinter, " ")
> #Else
> avTmp = Split97(Excel.ActivePrinter, " ")
> #End If
>
> sConn = " " & avTmp(UBound(avTmp) - 1) & " "
> Set WshNetwork = CreateObject("WScript.Network")
> Set oPrinters = WshNetwork.EnumPrinterConnections
> MyPrinter = ActivePrinter
>
> For i = 0 To oPrinters.Count - 1 Step 2
> If InStr(1, oPrinters.Item(i + 1), "Label", _
> vbTextCompare) Then
> ActivePrinter = oPrinters.Item(i + 1) & _
> sConn & oPrinters.Item(i)
> Exit For
> End If
> Next
>
> 'Print using "Label"
>
> ActivePrinter = MyPrinter
>
> End Sub
>
> Function Split97(sStr As String, sdelim As String) As Variant
> Split97 = Evaluate("{""" & _
> Application.Substitute(sStr, sdelim, """,""") & """}")
> End Function
>
>
>
>
> "Samrcat" wrote:
>
> > I can see the value of that.
> > But unfortunately, it still leaves me with defining the "on Ne02" etc for
> > the printer I am asking to print. That's just the point. I am trying to get
> > away from using that Ne02, Ne03, etc as that changes on our network, breaking
> > the macro.
> >
> > -Samantha
> >
> > "Pete_UK" wrote:
> >
> > > I had to change printers in a macro which outputted an Excel file as a
> > > PDF file - here's a fragment of the code, for what it's worth:
> > >
> > > my_printer = Application.ActivePrinter
> > > Application.ActivePrinter = "Adobe PDF on Ne02:"
> > > ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
> > > ActivePrinter:="Adobe PDF on Ne02:", Collate:=True
> > > Application.ActivePrinter = my_printer
> > >
> > > Note the first and last line of the fragment, which you might want to
> > > use in your code rather than explicitly setting the Kyocera printer.
> > >
> > > Hope this helps.
> > >
> > > Pete
> > >
> > >