I have a rather complex workbook that when a user clicks a button it prints
that page to a speciaifc printer and a certain number of copies then emails a
copy to that dept head and then reset the form for the next customer..

my problem is that I have 4 version of the same pricelist on 4 differenct
compters.

I need a snippet of code that would look for [printer1] and if it exists
print to that printer, if not checks for [printer 2] and prints to that, if
not then [printer3] and so on.. then resumes the rest of the code as it does
now.

Thanks Wayne @ CNY

You could let the user decide with
Application.Dialogs(xlDialogPrinterSetup).Show

There are also methods of getting a list of installed printers and searching
it for the one(s) you want. One approach using API calls is outlined here
http://word.mvps.org/FAQs/MacrosVBA/...lePrinters.htm

Or you could try looping through a list of printers, try to set the printer,
and test for an error. If no error, then exit the loop and print the
documents (first testing to see if the printer is actually set to something
that is in your list of desired printers - if it is not, you'll need to
decide what to do).


Sub test()
Dim strDefaultPrinter As String
Dim arrPrinterList(1 To 4) As String

arrPrinterList(1) = "Printer1"
arrPrinterList(2) = "Printer2"
arrPrinterList(3) = "Printer3"
arrPrinterList(4) = "Printer4"

strDefaultPrinter = ActivePrinter

On Error Resume Next
For i = LBound(arrPrinterList) To UBound(arrPrinterList)
ActivePrinter = arrPrinterList(i)
If Err.Number = 0 Then
Exit For
Else: Err.Clear
End If
Next i
On Error GoTo 0

If IsError(Application.Match(ActivePrinter, arrPrinterList, 0)) Then
MsgBox "Printer Not Found"
'Now What-let user decide?
Application.Dialogs(xlDialogPrinterSetup).Show
Else
'Print out your documents
End If

ActivePrinter = strDefaultPrinter

End Sub



"Wayno" wrote:

> I have a rather complex workbook that when a user clicks a button it prints
> that page to a speciaifc printer and a certain number of copies then emails a
> copy to that dept head and then reset the form for the next customer..
>
> my problem is that I have 4 version of the same pricelist on 4 differenct
> compters.
>
> I need a snippet of code that would look for [printer1] and if it exists
> print to that printer, if not checks for [printer 2] and prints to that, if
> not then [printer3] and so on.. then resumes the rest of the code as it does
> now.
>
> Thanks Wayne @ CNY

Thank you for the help, I made the following change but I always get "No
Printer Found"

arrPrinterList(1) = "Office Printer"
arrPrinterList(2) = "Epson Color on USB001:"
arrPrinterList(3) = "EPSON TM-H5000II Receipt"
arrPrinterList(4) = "HP 1200"

as I am completely stupid to VB, I need some simple-ized help.
Thanks
Wayne

"JMB" wrote:

> You could let the user decide with
> Application.Dialogs(xlDialogPrinterSetup).Show
>
> There are also methods of getting a list of installed printers and searching
> it for the one(s) you want. One approach using API calls is outlined here
> http://word.mvps.org/FAQs/MacrosVBA/...lePrinters.htm
>
> Or you could try looping through a list of printers, try to set the printer,
> and test for an error. If no error, then exit the loop and print the
> documents (first testing to see if the printer is actually set to something
> that is in your list of desired printers - if it is not, you'll need to
> decide what to do).
>
>
> Sub test()
> Dim strDefaultPrinter As String
> Dim arrPrinterList(1 To 4) As String
>
> arrPrinterList(1) = "Printer1"
> arrPrinterList(2) = "Printer2"
> arrPrinterList(3) = "Printer3"
> arrPrinterList(4) = "Printer4"
>
> strDefaultPrinter = ActivePrinter
>
> On Error Resume Next
> For i = LBound(arrPrinterList) To UBound(arrPrinterList)
> ActivePrinter = arrPrinterList(i)
> If Err.Number = 0 Then
> Exit For
> Else: Err.Clear
> End If
> Next i
> On Error GoTo 0
>
> If IsError(Application.Match(ActivePrinter, arrPrinterList, 0)) Then
> MsgBox "Printer Not Found"
> 'Now What-let user decide?
> Application.Dialogs(xlDialogPrinterSetup).Show
> Else
> 'Print out your documents
> End If
>
> ActivePrinter = strDefaultPrinter
>
> End Sub
>
>
>
> "Wayno" wrote:
>
> > I have a rather complex workbook that when a user clicks a button it prints
> > that page to a speciaifc printer and a certain number of copies then emails a
> > copy to that dept head and then reset the form for the next customer..
> >
> > my problem is that I have 4 version of the same pricelist on 4 differenct
> > compters.
> >
> > I need a snippet of code that would look for [printer1] and if it exists
> > print to that printer, if not checks for [printer 2] and prints to that, if
> > not then [printer3] and so on.. then resumes the rest of the code as it does
> > now.
> >
> > Thanks Wayne @ CNY

Sorry - I overlooked the fact that you are working on a network.
Unfortunately, the exact printer name that VBA wants can be different from
one machine to the next (the port names are sometimes different it seems).
You will probably need to capture the available printers and try to
reconstruct the printer name (Printer On Port).

In the code below, oPrinters returns a collection of installed printers and
their respective ports. My approach is to loop through the collection of
intalled printers and match them to your preference list (arrPrinterList).
Then set the activeprinter to the one that had the lowest match in your list.


Option Explicit

Sub Test()
Dim WshNetwork As Object
Dim oDrives As Object
Dim oPrinters As Object
Dim strDefaultPrinter As String
Dim arrPrinterList(1 To 4) As String
Dim varResult As Variant
Dim i As Long
Dim lngTemp1 As Long
Dim lngTemp2 As Long

arrPrinterList(1) = "Office Printer"
arrPrinterList(2) = "Epson Color"
arrPrinterList(3) = "EPSON TM-H5000II Receipt"
arrPrinterList(4) = "HP 1200"

strDefaultPrinter = ActivePrinter

Set WshNetwork = CreateObject("WScript.Network")
Set oDrives = WshNetwork.EnumNetworkDrives
Set oPrinters = WshNetwork.EnumPrinterConnections
For i = 1 To oPrinters.Count Step 2
varResult = Application.Match(oPrinters.Item(i), _
arrPrinterList, 0)
If IsNumeric(varResult) Then
If lngTemp1 = 0 Then
lngTemp1 = varResult
lngTemp2 = i
ElseIf varResult < lngTemp1 Then
lngTemp1 = varResult
lngTemp2 = i
End If
End If
Next i

If lngTemp1 > 0 Then
ActivePrinter = oPrinters.Item(lngTemp2) & " On " & _
oPrinters.Item(lngTemp2 - 1)
Else
Application.Dialogs(xlDialogPrinterSetup).Show
End If

'Print Your Documents
ActivePrinter = strDefaultPrinter

End Sub


Here is some code posted by others on this newsgroup (and reposted by Tom
Ogilvy) to capture the installed printers. KeepItcool used API calls in his
example (and he may have updated his macro - you could google his name and
check). I used Jim Rech's code in the above macro.


Posting by KeepItcool

Option Explicit

Private Declare Function GetProfileString Lib "kernel32" _
Alias "GetProfileStringA" _
(ByVal lpAppName As String, ByVal lpKeyName As String, _
ByVal lpDefault As String, ByVal lpReturnedString As String, _
ByVal nSize As Long) As Long

Sub showlist()
MsgBox Join(PrinterList, vbNewLine)
End Sub


Function PrinterList()
Dim lRet As Long
Dim sBuffer As String
Dim lSize As Long
Dim avTmp As Variant
Dim aPrn() As String
Dim n%, sPrn$, sConn$, sPort$

'Get localized Connection string
avTmp = Split(Excel.ActivePrinter)
sConn = " " & avTmp(UBound(avTmp) - 1) & " "
'Get Printers
lSize = 1024
sBuffer = Space(lSize)
lRet = GetProfileString("devices", vbNullString, vbNullString, _
sBuffer, lSize)
sBuffer = Left(sBuffer, lRet)
avTmp = Split(sBuffer, Chr(0))

ReDim Preserve avTmp(UBound(avTmp) - 1)
For n = 0 To UBound(avTmp)
lSize = 128
sBuffer = Space(lSize)
lRet = GetProfileString("devices", avTmp(n), vbNullString, _
sBuffer, lSize)
sPort = Mid(sBuffer, InStr(sBuffer, ",") + 1, _
lRet - InStr(sBuffer, ","))
avTmp(n) = avTmp(n) & sConn & sPort
Next
PrinterList = avTmp
End Function




================================

This posting by Jim Rech may be useful as well - certainly simpler:

From: "Jim Rech" <(E-Mail Removed)>
Subject: Re: Setting active printers will Excel 97 VBA
Date: Thu, 19 Oct 2000 14:04:56 -0400
Lines: 9
Newsgroups: microsoft.public.excel.programming

This macro enumerates printers and their connections. Parsing it you may be
able to construct the syntax ActivePrinter wants:

Sub a()
Set WshNetwork = CreateObject("WScript.Network")
Set oDrives = WshNetwork.EnumNetworkDrives
Set oPrinters = WshNetwork.EnumPrinterConnections
For i = 0 To oPrinters.Count - 1 Step 2
Debug.Print "Port " & oPrinters.Item(i) & " = " & _
oPrinters.Item(i + 1)
Next
End Sub


--
Jim Rech
Excel MVP
--------------------------




"Wayno" wrote:

> Thank you for the help, I made the following change but I always get "No
> Printer Found"
>
> arrPrinterList(1) = "Office Printer"
> arrPrinterList(2) = "Epson Color on USB001:"
> arrPrinterList(3) = "EPSON TM-H5000II Receipt"
> arrPrinterList(4) = "HP 1200"
>
> as I am completely stupid to VB, I need some simple-ized help.
> Thanks
> Wayne
>
> "JMB" wrote:
>
> > You could let the user decide with
> > Application.Dialogs(xlDialogPrinterSetup).Show
> >
> > There are also methods of getting a list of installed printers and searching
> > it for the one(s) you want. One approach using API calls is outlined here
> > http://word.mvps.org/FAQs/MacrosVBA/...lePrinters.htm
> >
> > Or you could try looping through a list of printers, try to set the printer,
> > and test for an error. If no error, then exit the loop and print the
> > documents (first testing to see if the printer is actually set to something
> > that is in your list of desired printers - if it is not, you'll need to
> > decide what to do).
> >
> >
> > Sub test()
> > Dim strDefaultPrinter As String
> > Dim arrPrinterList(1 To 4) As String
> >
> > arrPrinterList(1) = "Printer1"
> > arrPrinterList(2) = "Printer2"
> > arrPrinterList(3) = "Printer3"
> > arrPrinterList(4) = "Printer4"
> >
> > strDefaultPrinter = ActivePrinter
> >
> > On Error Resume Next
> > For i = LBound(arrPrinterList) To UBound(arrPrinterList)
> > ActivePrinter = arrPrinterList(i)
> > If Err.Number = 0 Then
> > Exit For
> > Else: Err.Clear
> > End If
> > Next i
> > On Error GoTo 0
> >
> > If IsError(Application.Match(ActivePrinter, arrPrinterList, 0)) Then
> > MsgBox "Printer Not Found"
> > 'Now What-let user decide?
> > Application.Dialogs(xlDialogPrinterSetup).Show
> > Else
> > 'Print out your documents
> > End If
> >
> > ActivePrinter = strDefaultPrinter
> >
> > End Sub
> >
> >
> >
> > "Wayno" wrote:
> >
> > > I have a rather complex workbook that when a user clicks a button it prints
> > > that page to a speciaifc printer and a certain number of copies then emails a
> > > copy to that dept head and then reset the form for the next customer..
> > >
> > > my problem is that I have 4 version of the same pricelist on 4 differenct
> > > compters.
> > >
> > > I need a snippet of code that would look for [printer1] and if it exists
> > > print to that printer, if not checks for [printer 2] and prints to that, if
> > > not then [printer3] and so on.. then resumes the rest of the code as it does
> > > now.
> > >
> > > Thanks Wayne @ CNY

I'll try that.. thanks... I also came across this code but I can't get it
too work...

If computername = "GRAPHICS" Then Application.ActivePrinter = "Epson Color"
If computername = "Customer" Then Application.ActivePrinter = "HP 1200"
If computername = "CNY Office" Then Application.ActivePrinter = "Office
Printer"

Does it make sense to you....

Thanks again
Wayne

"JMB" wrote:

> Sorry - I overlooked the fact that you are working on a network.
> Unfortunately, the exact printer name that VBA wants can be different from
> one machine to the next (the port names are sometimes different it seems).
> You will probably need to capture the available printers and try to
> reconstruct the printer name (Printer On Port).
>
> In the code below, oPrinters returns a collection of installed printers and
> their respective ports. My approach is to loop through the collection of
> intalled printers and match them to your preference list (arrPrinterList).
> Then set the activeprinter to the one that had the lowest match in your list.
>
>
> Option Explicit
>
> Sub Test()
> Dim WshNetwork As Object
> Dim oDrives As Object
> Dim oPrinters As Object
> Dim strDefaultPrinter As String
> Dim arrPrinterList(1 To 4) As String
> Dim varResult As Variant
> Dim i As Long
> Dim lngTemp1 As Long
> Dim lngTemp2 As Long
>
> arrPrinterList(1) = "Office Printer"
> arrPrinterList(2) = "Epson Color"
> arrPrinterList(3) = "EPSON TM-H5000II Receipt"
> arrPrinterList(4) = "HP 1200"
>
> strDefaultPrinter = ActivePrinter
>
> Set WshNetwork = CreateObject("WScript.Network")
> Set oDrives = WshNetwork.EnumNetworkDrives
> Set oPrinters = WshNetwork.EnumPrinterConnections
> For i = 1 To oPrinters.Count Step 2
> varResult = Application.Match(oPrinters.Item(i), _
> arrPrinterList, 0)
> If IsNumeric(varResult) Then
> If lngTemp1 = 0 Then
> lngTemp1 = varResult
> lngTemp2 = i
> ElseIf varResult < lngTemp1 Then
> lngTemp1 = varResult
> lngTemp2 = i
> End If
> End If
> Next i
>
> If lngTemp1 > 0 Then
> ActivePrinter = oPrinters.Item(lngTemp2) & " On " & _
> oPrinters.Item(lngTemp2 - 1)
> Else
> Application.Dialogs(xlDialogPrinterSetup).Show
> End If
>
> 'Print Your Documents
> ActivePrinter = strDefaultPrinter
>
> End Sub
>
>
> Here is some code posted by others on this newsgroup (and reposted by Tom
> Ogilvy) to capture the installed printers. KeepItcool used API calls in his
> example (and he may have updated his macro - you could google his name and
> check). I used Jim Rech's code in the above macro.
>
>
> Posting by KeepItcool
>
> Option Explicit
>
> Private Declare Function GetProfileString Lib "kernel32" _
> Alias "GetProfileStringA" _
> (ByVal lpAppName As String, ByVal lpKeyName As String, _
> ByVal lpDefault As String, ByVal lpReturnedString As String, _
> ByVal nSize As Long) As Long
>
> Sub showlist()
> MsgBox Join(PrinterList, vbNewLine)
> End Sub
>
>
> Function PrinterList()
> Dim lRet As Long
> Dim sBuffer As String
> Dim lSize As Long
> Dim avTmp As Variant
> Dim aPrn() As String
> Dim n%, sPrn$, sConn$, sPort$
>
> 'Get localized Connection string
> avTmp = Split(Excel.ActivePrinter)
> sConn = " " & avTmp(UBound(avTmp) - 1) & " "
> 'Get Printers
> lSize = 1024
> sBuffer = Space(lSize)
> lRet = GetProfileString("devices", vbNullString, vbNullString, _
> sBuffer, lSize)
> sBuffer = Left(sBuffer, lRet)
> avTmp = Split(sBuffer, Chr(0))
>
> ReDim Preserve avTmp(UBound(avTmp) - 1)
> For n = 0 To UBound(avTmp)
> lSize = 128
> sBuffer = Space(lSize)
> lRet = GetProfileString("devices", avTmp(n), vbNullString, _
> sBuffer, lSize)
> sPort = Mid(sBuffer, InStr(sBuffer, ",") + 1, _
> lRet - InStr(sBuffer, ","))
> avTmp(n) = avTmp(n) & sConn & sPort
> Next
> PrinterList = avTmp
> End Function
>
>
>
>
> ================================
>
> This posting by Jim Rech may be useful as well - certainly simpler:
>
> From: "Jim Rech" <(E-Mail Removed)>
> Subject: Re: Setting active printers will Excel 97 VBA
> Date: Thu, 19 Oct 2000 14:04:56 -0400
> Lines: 9
> Newsgroups: microsoft.public.excel.programming
>
> This macro enumerates printers and their connections. Parsing it you may be
> able to construct the syntax ActivePrinter wants:
>
> Sub a()
> Set WshNetwork = CreateObject("WScript.Network")
> Set oDrives = WshNetwork.EnumNetworkDrives
> Set oPrinters = WshNetwork.EnumPrinterConnections
> For i = 0 To oPrinters.Count - 1 Step 2
> Debug.Print "Port " & oPrinters.Item(i) & " = " & _
> oPrinters.Item(i + 1)
> Next
> End Sub
>
>
> --
> Jim Rech
> Excel MVP
> --------------------------
>
>
>
>
> "Wayno" wrote:
>
> > Thank you for the help, I made the following change but I always get "No
> > Printer Found"
> >
> > arrPrinterList(1) = "Office Printer"
> > arrPrinterList(2) = "Epson Color on USB001:"
> > arrPrinterList(3) = "EPSON TM-H5000II Receipt"
> > arrPrinterList(4) = "HP 1200"
> >
> > as I am completely stupid to VB, I need some simple-ized help.
> > Thanks
> > Wayne
> >
> > "JMB" wrote:
> >
> > > You could let the user decide with
> > > Application.Dialogs(xlDialogPrinterSetup).Show
> > >
> > > There are also methods of getting a list of installed printers and searching
> > > it for the one(s) you want. One approach using API calls is outlined here
> > > http://word.mvps.org/FAQs/MacrosVBA/...lePrinters.htm
> > >
> > > Or you could try looping through a list of printers, try to set the printer,
> > > and test for an error. If no error, then exit the loop and print the
> > > documents (first testing to see if the printer is actually set to something
> > > that is in your list of desired printers - if it is not, you'll need to
> > > decide what to do).
> > >
> > >
> > > Sub test()
> > > Dim strDefaultPrinter As String
> > > Dim arrPrinterList(1 To 4) As String
> > >
> > > arrPrinterList(1) = "Printer1"
> > > arrPrinterList(2) = "Printer2"
> > > arrPrinterList(3) = "Printer3"
> > > arrPrinterList(4) = "Printer4"
> > >
> > > strDefaultPrinter = ActivePrinter
> > >
> > > On Error Resume Next
> > > For i = LBound(arrPrinterList) To UBound(arrPrinterList)
> > > ActivePrinter = arrPrinterList(i)
> > > If Err.Number = 0 Then
> > > Exit For
> > > Else: Err.Clear
> > > End If
> > > Next i
> > > On Error GoTo 0
> > >
> > > If IsError(Application.Match(ActivePrinter, arrPrinterList, 0)) Then
> > > MsgBox "Printer Not Found"
> > > 'Now What-let user decide?
> > > Application.Dialogs(xlDialogPrinterSetup).Show
> > > Else
> > > 'Print out your documents
> > > End If
> > >
> > > ActivePrinter = strDefaultPrinter
> > >
> > > End Sub
> > >
> > >
> > >
> > > "Wayno" wrote:
> > >
> > > > I have a rather complex workbook that when a user clicks a button it prints
> > > > that page to a speciaifc printer and a certain number of copies then emails a
> > > > copy to that dept head and then reset the form for the next customer..
> > > >
> > > > my problem is that I have 4 version of the same pricelist on 4 differenct
> > > > compters.
> > > >
> > > > I need a snippet of code that would look for [printer1] and if it exists
> > > > print to that printer, if not checks for [printer 2] and prints to that, if
> > > > not then [printer3] and so on.. then resumes the rest of the code as it does
> > > > now.
> > > >
> > > > Thanks Wayne @ CNY

HI again..
I'm getting a failed error on this part of your code:

ActivePrinter = oPrinters.Item(lngTemp2) & " On " & _
oPrinters.Item(lngTemp2 - 1)


Actual error is:
Runtime error '1004'
Method "ActivePrinter" of object '_global' failed.


"JMB" wrote:

> Sorry - I overlooked the fact that you are working on a network.
> Unfortunately, the exact printer name that VBA wants can be different from
> one machine to the next (the port names are sometimes different it seems).
> You will probably need to capture the available printers and try to
> reconstruct the printer name (Printer On Port).
>
> In the code below, oPrinters returns a collection of installed printers and
> their respective ports. My approach is to loop through the collection of
> intalled printers and match them to your preference list (arrPrinterList).
> Then set the activeprinter to the one that had the lowest match in your list.
>
>
> Option Explicit
>
> Sub Test()
> Dim WshNetwork As Object
> Dim oDrives As Object
> Dim oPrinters As Object
> Dim strDefaultPrinter As String
> Dim arrPrinterList(1 To 4) As String
> Dim varResult As Variant
> Dim i As Long
> Dim lngTemp1 As Long
> Dim lngTemp2 As Long
>
> arrPrinterList(1) = "Office Printer"
> arrPrinterList(2) = "Epson Color"
> arrPrinterList(3) = "EPSON TM-H5000II Receipt"
> arrPrinterList(4) = "HP 1200"
>
> strDefaultPrinter = ActivePrinter
>
> Set WshNetwork = CreateObject("WScript.Network")
> Set oDrives = WshNetwork.EnumNetworkDrives
> Set oPrinters = WshNetwork.EnumPrinterConnections
> For i = 1 To oPrinters.Count Step 2
> varResult = Application.Match(oPrinters.Item(i), _
> arrPrinterList, 0)
> If IsNumeric(varResult) Then
> If lngTemp1 = 0 Then
> lngTemp1 = varResult
> lngTemp2 = i
> ElseIf varResult < lngTemp1 Then
> lngTemp1 = varResult
> lngTemp2 = i
> End If
> End If
> Next i
>
> If lngTemp1 > 0 Then
> ActivePrinter = oPrinters.Item(lngTemp2) & " On " & _
> oPrinters.Item(lngTemp2 - 1)
> Else
> Application.Dialogs(xlDialogPrinterSetup).Show
> End If
>
> 'Print Your Documents
> ActivePrinter = strDefaultPrinter
>
> End Sub
>
>
> Here is some code posted by others on this newsgroup (and reposted by Tom
> Ogilvy) to capture the installed printers. KeepItcool used API calls in his
> example (and he may have updated his macro - you could google his name and
> check). I used Jim Rech's code in the above macro.
>
>
> Posting by KeepItcool
>
> Option Explicit
>
> Private Declare Function GetProfileString Lib "kernel32" _
> Alias "GetProfileStringA" _
> (ByVal lpAppName As String, ByVal lpKeyName As String, _
> ByVal lpDefault As String, ByVal lpReturnedString As String, _
> ByVal nSize As Long) As Long
>
> Sub showlist()
> MsgBox Join(PrinterList, vbNewLine)
> End Sub
>
>
> Function PrinterList()
> Dim lRet As Long
> Dim sBuffer As String
> Dim lSize As Long
> Dim avTmp As Variant
> Dim aPrn() As String
> Dim n%, sPrn$, sConn$, sPort$
>
> 'Get localized Connection string
> avTmp = Split(Excel.ActivePrinter)
> sConn = " " & avTmp(UBound(avTmp) - 1) & " "
> 'Get Printers
> lSize = 1024
> sBuffer = Space(lSize)
> lRet = GetProfileString("devices", vbNullString, vbNullString, _
> sBuffer, lSize)
> sBuffer = Left(sBuffer, lRet)
> avTmp = Split(sBuffer, Chr(0))
>
> ReDim Preserve avTmp(UBound(avTmp) - 1)
> For n = 0 To UBound(avTmp)
> lSize = 128
> sBuffer = Space(lSize)
> lRet = GetProfileString("devices", avTmp(n), vbNullString, _
> sBuffer, lSize)
> sPort = Mid(sBuffer, InStr(sBuffer, ",") + 1, _
> lRet - InStr(sBuffer, ","))
> avTmp(n) = avTmp(n) & sConn & sPort
> Next
> PrinterList = avTmp
> End Function
>
>
>
>
> ================================
>
> This posting by Jim Rech may be useful as well - certainly simpler:
>
> From: "Jim Rech" <(E-Mail Removed)>
> Subject: Re: Setting active printers will Excel 97 VBA
> Date: Thu, 19 Oct 2000 14:04:56 -0400
> Lines: 9
> Newsgroups: microsoft.public.excel.programming
>
> This macro enumerates printers and their connections. Parsing it you may be
> able to construct the syntax ActivePrinter wants:
>
> Sub a()
> Set WshNetwork = CreateObject("WScript.Network")
> Set oDrives = WshNetwork.EnumNetworkDrives
> Set oPrinters = WshNetwork.EnumPrinterConnections
> For i = 0 To oPrinters.Count - 1 Step 2
> Debug.Print "Port " & oPrinters.Item(i) & " = " & _
> oPrinters.Item(i + 1)
> Next
> End Sub
>
>
> --
> Jim Rech
> Excel MVP
> --------------------------
>
>
>
>
> "Wayno" wrote:
>
> > Thank you for the help, I made the following change but I always get "No
> > Printer Found"
> >
> > arrPrinterList(1) = "Office Printer"
> > arrPrinterList(2) = "Epson Color on USB001:"
> > arrPrinterList(3) = "EPSON TM-H5000II Receipt"
> > arrPrinterList(4) = "HP 1200"
> >
> > as I am completely stupid to VB, I need some simple-ized help.
> > Thanks
> > Wayne
> >
> > "JMB" wrote:
> >
> > > You could let the user decide with
> > > Application.Dialogs(xlDialogPrinterSetup).Show
> > >
> > > There are also methods of getting a list of installed printers and searching
> > > it for the one(s) you want. One approach using API calls is outlined here
> > > http://word.mvps.org/FAQs/MacrosVBA/...lePrinters.htm
> > >
> > > Or you could try looping through a list of printers, try to set the printer,
> > > and test for an error. If no error, then exit the loop and print the
> > > documents (first testing to see if the printer is actually set to something
> > > that is in your list of desired printers - if it is not, you'll need to
> > > decide what to do).
> > >
> > >
> > > Sub test()
> > > Dim strDefaultPrinter As String
> > > Dim arrPrinterList(1 To 4) As String
> > >
> > > arrPrinterList(1) = "Printer1"
> > > arrPrinterList(2) = "Printer2"
> > > arrPrinterList(3) = "Printer3"
> > > arrPrinterList(4) = "Printer4"
> > >
> > > strDefaultPrinter = ActivePrinter
> > >
> > > On Error Resume Next
> > > For i = LBound(arrPrinterList) To UBound(arrPrinterList)
> > > ActivePrinter = arrPrinterList(i)
> > > If Err.Number = 0 Then
> > > Exit For
> > > Else: Err.Clear
> > > End If
> > > Next i
> > > On Error GoTo 0
> > >
> > > If IsError(Application.Match(ActivePrinter, arrPrinterList, 0)) Then
> > > MsgBox "Printer Not Found"
> > > 'Now What-let user decide?
> > > Application.Dialogs(xlDialogPrinterSetup).Show
> > > Else
> > > 'Print out your documents
> > > End If
> > >
> > > ActivePrinter = strDefaultPrinter
> > >
> > > End Sub
> > >
> > >
> > >
> > > "Wayno" wrote:
> > >
> > > > I have a rather complex workbook that when a user clicks a button it prints
> > > > that page to a speciaifc printer and a certain number of copies then emails a
> > > > copy to that dept head and then reset the form for the next customer..
> > > >
> > > > my problem is that I have 4 version of the same pricelist on 4 differenct
> > > > compters.
> > > >
> > > > I need a snippet of code that would look for [printer1] and if it exists
> > > > print to that printer, if not checks for [printer 2] and prints to that, if
> > > > not then [printer3] and so on.. then resumes the rest of the code as it does
> > > > now.
> > > >
> > > > Thanks Wayne @ CNY

Hi JMB.. here is the solution I patched together from other sources.... it's
easy works perfect and thanks for all your suggestions.

Set WshShell = CreateObject("WScript.Shell")
Set ObjEnv = WshShell.Environment("Process")
COMPUTERNAME = ObjEnv("COMPUTERNAME")


If COMPUTERNAME = "GRAPHICS" Then ActiveWindow.ActiveSheet.PrintOut From:=1,
To:=1, Copies:=2, Collate:=True, ActivePrinter:="Epson Color" Else

If COMPUTERNAME = "OFFICE" Then ActiveWindow.ActiveSheet.PrintOut From:=1,
To:=1, Copies:=2, Collate:=True, ActivePrinter:="Office Color" Else

If COMPUTERNAME = "CUSTOMER" Then ActiveWindow.ActiveSheet.PrintOut From:=1,
To:=1, Copies:=2, Collate:=True, ActivePrinter:="HP 1200" Else

Wayne @ CNY Awards & Apparel, Inc.

"JMB" wrote:

> Sorry - I overlooked the fact that you are working on a network.
> Unfortunately, the exact printer name that VBA wants can be different from
> one machine to the next (the port names are sometimes different it seems).
> You will probably need to capture the available printers and try to
> reconstruct the printer name (Printer On Port).
>
> In the code below, oPrinters returns a collection of installed printers and
> their respective ports. My approach is to loop through the collection of
> intalled printers and match them to your preference list (arrPrinterList).
> Then set the activeprinter to the one that had the lowest match in your list.
>
>
> Option Explicit
>
> Sub Test()
> Dim WshNetwork As Object
> Dim oDrives As Object
> Dim oPrinters As Object
> Dim strDefaultPrinter As String
> Dim arrPrinterList(1 To 4) As String
> Dim varResult As Variant
> Dim i As Long
> Dim lngTemp1 As Long
> Dim lngTemp2 As Long
>
> arrPrinterList(1) = "Office Printer"
> arrPrinterList(2) = "Epson Color"
> arrPrinterList(3) = "EPSON TM-H5000II Receipt"
> arrPrinterList(4) = "HP 1200"
>
> strDefaultPrinter = ActivePrinter
>
> Set WshNetwork = CreateObject("WScript.Network")
> Set oDrives = WshNetwork.EnumNetworkDrives
> Set oPrinters = WshNetwork.EnumPrinterConnections
> For i = 1 To oPrinters.Count Step 2
> varResult = Application.Match(oPrinters.Item(i), _
> arrPrinterList, 0)
> If IsNumeric(varResult) Then
> If lngTemp1 = 0 Then
> lngTemp1 = varResult
> lngTemp2 = i
> ElseIf varResult < lngTemp1 Then
> lngTemp1 = varResult
> lngTemp2 = i
> End If
> End If
> Next i
>
> If lngTemp1 > 0 Then
> ActivePrinter = oPrinters.Item(lngTemp2) & " On " & _
> oPrinters.Item(lngTemp2 - 1)
> Else
> Application.Dialogs(xlDialogPrinterSetup).Show
> End If
>
> 'Print Your Documents
> ActivePrinter = strDefaultPrinter
>
> End Sub
>
>
> Here is some code posted by others on this newsgroup (and reposted by Tom
> Ogilvy) to capture the installed printers. KeepItcool used API calls in his
> example (and he may have updated his macro - you could google his name and
> check). I used Jim Rech's code in the above macro.
>
>
> Posting by KeepItcool
>
> Option Explicit
>
> Private Declare Function GetProfileString Lib "kernel32" _
> Alias "GetProfileStringA" _
> (ByVal lpAppName As String, ByVal lpKeyName As String, _
> ByVal lpDefault As String, ByVal lpReturnedString As String, _
> ByVal nSize As Long) As Long
>
> Sub showlist()
> MsgBox Join(PrinterList, vbNewLine)
> End Sub
>
>
> Function PrinterList()
> Dim lRet As Long
> Dim sBuffer As String
> Dim lSize As Long
> Dim avTmp As Variant
> Dim aPrn() As String
> Dim n%, sPrn$, sConn$, sPort$
>
> 'Get localized Connection string
> avTmp = Split(Excel.ActivePrinter)
> sConn = " " & avTmp(UBound(avTmp) - 1) & " "
> 'Get Printers
> lSize = 1024
> sBuffer = Space(lSize)
> lRet = GetProfileString("devices", vbNullString, vbNullString, _
> sBuffer, lSize)
> sBuffer = Left(sBuffer, lRet)
> avTmp = Split(sBuffer, Chr(0))
>
> ReDim Preserve avTmp(UBound(avTmp) - 1)
> For n = 0 To UBound(avTmp)
> lSize = 128
> sBuffer = Space(lSize)
> lRet = GetProfileString("devices", avTmp(n), vbNullString, _
> sBuffer, lSize)
> sPort = Mid(sBuffer, InStr(sBuffer, ",") + 1, _
> lRet - InStr(sBuffer, ","))
> avTmp(n) = avTmp(n) & sConn & sPort
> Next
> PrinterList = avTmp
> End Function
>
>
>
>
> ================================
>
> This posting by Jim Rech may be useful as well - certainly simpler:
>
> From: "Jim Rech" <(E-Mail Removed)>
> Subject: Re: Setting active printers will Excel 97 VBA
> Date: Thu, 19 Oct 2000 14:04:56 -0400
> Lines: 9
> Newsgroups: microsoft.public.excel.programming
>
> This macro enumerates printers and their connections. Parsing it you may be
> able to construct the syntax ActivePrinter wants:
>
> Sub a()
> Set WshNetwork = CreateObject("WScript.Network")
> Set oDrives = WshNetwork.EnumNetworkDrives
> Set oPrinters = WshNetwork.EnumPrinterConnections
> For i = 0 To oPrinters.Count - 1 Step 2
> Debug.Print "Port " & oPrinters.Item(i) & " = " & _
> oPrinters.Item(i + 1)
> Next
> End Sub
>
>
> --
> Jim Rech
> Excel MVP
> --------------------------
>
>
>
>
> "Wayno" wrote:
>
> > Thank you for the help, I made the following change but I always get "No
> > Printer Found"
> >
> > arrPrinterList(1) = "Office Printer"
> > arrPrinterList(2) = "Epson Color on USB001:"
> > arrPrinterList(3) = "EPSON TM-H5000II Receipt"
> > arrPrinterList(4) = "HP 1200"
> >
> > as I am completely stupid to VB, I need some simple-ized help.
> > Thanks
> > Wayne
> >
> > "JMB" wrote:
> >
> > > You could let the user decide with
> > > Application.Dialogs(xlDialogPrinterSetup).Show
> > >
> > > There are also methods of getting a list of installed printers and searching
> > > it for the one(s) you want. One approach using API calls is outlined here
> > > http://word.mvps.org/FAQs/MacrosVBA/...lePrinters.htm
> > >
> > > Or you could try looping through a list of printers, try to set the printer,
> > > and test for an error. If no error, then exit the loop and print the
> > > documents (first testing to see if the printer is actually set to something
> > > that is in your list of desired printers - if it is not, you'll need to
> > > decide what to do).
> > >
> > >
> > > Sub test()
> > > Dim strDefaultPrinter As String
> > > Dim arrPrinterList(1 To 4) As String
> > >
> > > arrPrinterList(1) = "Printer1"
> > > arrPrinterList(2) = "Printer2"
> > > arrPrinterList(3) = "Printer3"
> > > arrPrinterList(4) = "Printer4"
> > >
> > > strDefaultPrinter = ActivePrinter
> > >
> > > On Error Resume Next
> > > For i = LBound(arrPrinterList) To UBound(arrPrinterList)
> > > ActivePrinter = arrPrinterList(i)
> > > If Err.Number = 0 Then
> > > Exit For
> > > Else: Err.Clear
> > > End If
> > > Next i
> > > On Error GoTo 0
> > >
> > > If IsError(Application.Match(ActivePrinter, arrPrinterList, 0)) Then
> > > MsgBox "Printer Not Found"
> > > 'Now What-let user decide?
> > > Application.Dialogs(xlDialogPrinterSetup).Show
> > > Else
> > > 'Print out your documents
> > > End If
> > >
> > > ActivePrinter = strDefaultPrinter
> > >
> > > End Sub
> > >
> > >
> > >
> > > "Wayno" wrote:
> > >
> > > > I have a rather complex workbook that when a user clicks a button it prints
> > > > that page to a speciaifc printer and a certain number of copies then emails a
> > > > copy to that dept head and then reset the form for the next customer..
> > > >
> > > > my problem is that I have 4 version of the same pricelist on 4 differenct
> > > > compters.
> > > >
> > > > I need a snippet of code that would look for [printer1] and if it exists
> > > > print to that printer, if not checks for [printer 2] and prints to that, if
> > > > not then [printer3] and so on.. then resumes the rest of the code as it does
> > > > now.
> > > >
> > > > Thanks Wayne @ CNY

Glad you got it working - that does appear to be a more straightforward
approach. I'll have to remember it for next time.

"Wayno" wrote:

> Hi JMB.. here is the solution I patched together from other sources.... it's
> easy works perfect and thanks for all your suggestions.
>
> Set WshShell = CreateObject("WScript.Shell")
> Set ObjEnv = WshShell.Environment("Process")
> COMPUTERNAME = ObjEnv("COMPUTERNAME")
>
>
> If COMPUTERNAME = "GRAPHICS" Then ActiveWindow.ActiveSheet.PrintOut From:=1,
> To:=1, Copies:=2, Collate:=True, ActivePrinter:="Epson Color" Else
>
> If COMPUTERNAME = "OFFICE" Then ActiveWindow.ActiveSheet.PrintOut From:=1,
> To:=1, Copies:=2, Collate:=True, ActivePrinter:="Office Color" Else
>
> If COMPUTERNAME = "CUSTOMER" Then ActiveWindow.ActiveSheet.PrintOut From:=1,
> To:=1, Copies:=2, Collate:=True, ActivePrinter:="HP 1200" Else
>
> Wayne @ CNY Awards & Apparel, Inc.
>
> "JMB" wrote:
>
> > Sorry - I overlooked the fact that you are working on a network.
> > Unfortunately, the exact printer name that VBA wants can be different from
> > one machine to the next (the port names are sometimes different it seems).
> > You will probably need to capture the available printers and try to
> > reconstruct the printer name (Printer On Port).
> >
> > In the code below, oPrinters returns a collection of installed printers and
> > their respective ports. My approach is to loop through the collection of
> > intalled printers and match them to your preference list (arrPrinterList).
> > Then set the activeprinter to the one that had the lowest match in your list.
> >
> >
> > Option Explicit
> >
> > Sub Test()
> > Dim WshNetwork As Object
> > Dim oDrives As Object
> > Dim oPrinters As Object
> > Dim strDefaultPrinter As String
> > Dim arrPrinterList(1 To 4) As String
> > Dim varResult As Variant
> > Dim i As Long
> > Dim lngTemp1 As Long
> > Dim lngTemp2 As Long
> >
> > arrPrinterList(1) = "Office Printer"
> > arrPrinterList(2) = "Epson Color"
> > arrPrinterList(3) = "EPSON TM-H5000II Receipt"
> > arrPrinterList(4) = "HP 1200"
> >
> > strDefaultPrinter = ActivePrinter
> >
> > Set WshNetwork = CreateObject("WScript.Network")
> > Set oDrives = WshNetwork.EnumNetworkDrives
> > Set oPrinters = WshNetwork.EnumPrinterConnections
> > For i = 1 To oPrinters.Count Step 2
> > varResult = Application.Match(oPrinters.Item(i), _
> > arrPrinterList, 0)
> > If IsNumeric(varResult) Then
> > If lngTemp1 = 0 Then
> > lngTemp1 = varResult
> > lngTemp2 = i
> > ElseIf varResult < lngTemp1 Then
> > lngTemp1 = varResult
> > lngTemp2 = i
> > End If
> > End If
> > Next i
> >
> > If lngTemp1 > 0 Then
> > ActivePrinter = oPrinters.Item(lngTemp2) & " On " & _
> > oPrinters.Item(lngTemp2 - 1)
> > Else
> > Application.Dialogs(xlDialogPrinterSetup).Show
> > End If
> >
> > 'Print Your Documents
> > ActivePrinter = strDefaultPrinter
> >
> > End Sub
> >
> >
> > Here is some code posted by others on this newsgroup (and reposted by Tom
> > Ogilvy) to capture the installed printers. KeepItcool used API calls in his
> > example (and he may have updated his macro - you could google his name and
> > check). I used Jim Rech's code in the above macro.
> >
> >
> > Posting by KeepItcool
> >
> > Option Explicit
> >
> > Private Declare Function GetProfileString Lib "kernel32" _
> > Alias "GetProfileStringA" _
> > (ByVal lpAppName As String, ByVal lpKeyName As String, _
> > ByVal lpDefault As String, ByVal lpReturnedString As String, _
> > ByVal nSize As Long) As Long
> >
> > Sub showlist()
> > MsgBox Join(PrinterList, vbNewLine)
> > End Sub
> >
> >
> > Function PrinterList()
> > Dim lRet As Long
> > Dim sBuffer As String
> > Dim lSize As Long
> > Dim avTmp As Variant
> > Dim aPrn() As String
> > Dim n%, sPrn$, sConn$, sPort$
> >
> > 'Get localized Connection string
> > avTmp = Split(Excel.ActivePrinter)
> > sConn = " " & avTmp(UBound(avTmp) - 1) & " "
> > 'Get Printers
> > lSize = 1024
> > sBuffer = Space(lSize)
> > lRet = GetProfileString("devices", vbNullString, vbNullString, _
> > sBuffer, lSize)
> > sBuffer = Left(sBuffer, lRet)
> > avTmp = Split(sBuffer, Chr(0))
> >
> > ReDim Preserve avTmp(UBound(avTmp) - 1)
> > For n = 0 To UBound(avTmp)
> > lSize = 128
> > sBuffer = Space(lSize)
> > lRet = GetProfileString("devices", avTmp(n), vbNullString, _
> > sBuffer, lSize)
> > sPort = Mid(sBuffer, InStr(sBuffer, ",") + 1, _
> > lRet - InStr(sBuffer, ","))
> > avTmp(n) = avTmp(n) & sConn & sPort
> > Next
> > PrinterList = avTmp
> > End Function
> >
> >
> >
> >
> > ================================
> >
> > This posting by Jim Rech may be useful as well - certainly simpler:
> >
> > From: "Jim Rech" <(E-Mail Removed)>
> > Subject: Re: Setting active printers will Excel 97 VBA
> > Date: Thu, 19 Oct 2000 14:04:56 -0400
> > Lines: 9
> > Newsgroups: microsoft.public.excel.programming
> >
> > This macro enumerates printers and their connections. Parsing it you may be
> > able to construct the syntax ActivePrinter wants:
> >
> > Sub a()
> > Set WshNetwork = CreateObject("WScript.Network")
> > Set oDrives = WshNetwork.EnumNetworkDrives
> > Set oPrinters = WshNetwork.EnumPrinterConnections
> > For i = 0 To oPrinters.Count - 1 Step 2
> > Debug.Print "Port " & oPrinters.Item(i) & " = " & _
> > oPrinters.Item(i + 1)
> > Next
> > End Sub
> >
> >
> > --
> > Jim Rech
> > Excel MVP
> > --------------------------
> >
> >
> >
> >
> > "Wayno" wrote:
> >
> > > Thank you for the help, I made the following change but I always get "No
> > > Printer Found"
> > >
> > > arrPrinterList(1) = "Office Printer"
> > > arrPrinterList(2) = "Epson Color on USB001:"
> > > arrPrinterList(3) = "EPSON TM-H5000II Receipt"
> > > arrPrinterList(4) = "HP 1200"
> > >
> > > as I am completely stupid to VB, I need some simple-ized help.
> > > Thanks
> > > Wayne
> > >
> > > "JMB" wrote:
> > >
> > > > You could let the user decide with
> > > > Application.Dialogs(xlDialogPrinterSetup).Show
> > > >
> > > > There are also methods of getting a list of installed printers and searching
> > > > it for the one(s) you want. One approach using API calls is outlined here
> > > > http://word.mvps.org/FAQs/MacrosVBA/...lePrinters.htm
> > > >
> > > > Or you could try looping through a list of printers, try to set the printer,
> > > > and test for an error. If no error, then exit the loop and print the
> > > > documents (first testing to see if the printer is actually set to something
> > > > that is in your list of desired printers - if it is not, you'll need to
> > > > decide what to do).
> > > >
> > > >
> > > > Sub test()
> > > > Dim strDefaultPrinter As String
> > > > Dim arrPrinterList(1 To 4) As String
> > > >
> > > > arrPrinterList(1) = "Printer1"
> > > > arrPrinterList(2) = "Printer2"
> > > > arrPrinterList(3) = "Printer3"
> > > > arrPrinterList(4) = "Printer4"
> > > >
> > > > strDefaultPrinter = ActivePrinter
> > > >
> > > > On Error Resume Next
> > > > For i = LBound(arrPrinterList) To UBound(arrPrinterList)
> > > > ActivePrinter = arrPrinterList(i)
> > > > If Err.Number = 0 Then
> > > > Exit For
> > > > Else: Err.Clear
> > > > End If
> > > > Next i
> > > > On Error GoTo 0
> > > >
> > > > If IsError(Application.Match(ActivePrinter, arrPrinterList, 0)) Then
> > > > MsgBox "Printer Not Found"
> > > > 'Now What-let user decide?
> > > > Application.Dialogs(xlDialogPrinterSetup).Show
> > > > Else
> > > > 'Print out your documents
> > > > End If
> > > >
> > > > ActivePrinter = strDefaultPrinter
> > > >
> > > > End Sub
> > > >
> > > >
> > > >
> > > > "Wayno" wrote:
> > > >
> > > > > I have a rather complex workbook that when a user clicks a button it prints
> > > > > that page to a speciaifc printer and a certain number of copies then emails a
> > > > > copy to that dept head and then reset the form for the next customer..
> > > > >
> > > > > my problem is that I have 4 version of the same pricelist on 4 differenct
> > > > > compters.
> > > > >
> > > > > I need a snippet of code that would look for [printer1] and if it exists
> > > > > print to that printer, if not checks for [printer 2] and prints to that, if
> > > > > not then [printer3] and so on.. then resumes the rest of the code as it does
> > > > > now.
> > > > >
> > > > > Thanks Wayne @ CNY