My colleagues at work regularly have a several hundred of Excel
spreadsheets that need Printing on either white paper or as PDFs. I
have created a user form, so that each team member can choose which
print method they want before printing. I have produced some VBA that
will then do the job automatically.

The code mostly works fine for printing paper copies. However each
person in the network has there printer set up differently. On one
machine the printer will be listed as "Brother HL-2460 series on Ne01:"
on another it will be "Brother HL-2460 series on Ne03:" etc. Could
someone tell me how to set things up, so that the setting is the same
for all people in my team. E.G. I would like it if the setting is
"Brother HL-2460 series on Ne01:" for everyone.
When printing PDFs I have the same problem. The active printer is
listed as "Adobe PDF on Ne03:", on another it may be "Adobe PDF on
Ne04:" etc.
A more serious problem is that the PDFs only print on the computer
where an early version of PDF writer is installed. I have found out
that a way round this problem is to get the macro to first write a
postscript file using the acrobat distiller, then convert the
postscript to a PDF. I found following code on the web:

Sub PrintPDF()

' Define the postscript and .pdf file names.
Dim PSFileName as String
Dim PDFFileName as String
PSFileName = "c:\myPostScript.ps"
PDFFileName = "c:\myPDF.pdf"

' Print the Excel range to the postscript file
Dim MySheet As WorkSheet
Set MySheet = ActiveSheet
MySheet.Range("myRange").PrintOut copies:=1, preview:=False,
ActivePrinter:="Acrobat Distiller", printtofile:=True, collate:=True,
prttofilename:=PSFileName

' Convert the postscript file to .pdf
Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""

End Sub

Private Sub CommandButton1_Click()

' Define the postscript and .pdf file names.
Dim PSFileName as String
Dim PDFFileName as String
PSFileName = "c:\myPostScript.ps"
PDFFileName = "c:\myPDF.pdf"

' Print the Excel range to the postscript file
Dim MySheet As WorkSheet
Set MySheet = ActiveSheet
MySheet.Range("myRange").PrintOut copies:=1, preview:=False,
ActivePrinter:="Acrobat Distiller", printtofile:=True, collate:=True,
prttofilename:=PSFileName

' Convert the postscript file to .pdf
Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""

End Sub

The above code looks like it will do fine. However when I go to file =
print in an excel document I do not get the option to set the printer
to "Acrobat Distiller". If I can't do the job manually, I can't get
the job done with a macro. Could someone help me set up the my system
so I can use the distiller in this way?

Thanks,

Andy.

I am not sure you can configure which port it uses. Better would be to adapt
your code to use the appropriate port. Here are two different approachs -
the first includes the question being responded to:

I'd like to get a array of all installed printers incl. the connected port.
The array should look like this:


HP LaserJet 6P on LPT1:
Adobe PDF on Ne03:
SnagIt 6 on Ne00:


I already have the following procedure, but this code doesn't return the
connected port:


Private Sub ListPrinters()
Dim wshNetwork As Object
Dim oDrives As Object
Dim oPrinters As Object
Dim iCount As Integer
Dim sCurrentprinter As String
sCurrentprinter = Application.ActivePrinter
Set wshNetwork = CreateObject("WScript.Network")
Set oDrives = wshNetwork.EnumNetworkDrives
Set oPrinters = wshNetwork.EnumPrinterConnections
For iCount = 0 To oPrinters.Count - 1 Step 2
Debug.Print oPrinters.Item(iCount + 1)
Next
End Sub


Many thanks in advance.

Michael Pierron responds

Hi Tom,
For iCount = 0 To oPrinters.Count - 1 Step 2
' Debug.Print "Printer Port " & oPrinter.Item(iCount) & " = " & _
' oPrinter.Item(iCount + 1)
Debug.Print "Printer Port " & oPrinters.Item(iCount) _
& " = " & oPrinters.Item(iCount + 1)
Next


MP

----------------
KeepItCool
---------------
Hi Thomas


This should produce exactly what you're looking for
but note it will NOT work for xl97


The PrinterFind function will return an array of
localized strings ready to assign to the ActivePrinter.


I've amended an old post from myself to allow filtering.
see test procedure for example of how to use.
On a userform you could simply use s'th like
cboPrinters.list=PrinterFind


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 Test()
Dim vaList
'Get all printers
vaList = PrinterFind
'Show m
MsgBox Join(vaList, vbLf), , "List of printers"


'Get all laserjets
vaList = PrinterFind(Match:="Laserjet")


'Switch to the first laserjet found
If UBound(vaList) = -1 Then
MsgBox "Printer not found"
ElseIf MsgBox("from " & vbTab & ": " & ActivePrinter & vbLf & _
"to " & vbTab & ": " & vaList(0), _
vbOKCancel, "Switch Printers") = vbOK Then
Application.ActivePrinter = vaList(0)
End If
End Sub


Public Function PrinterFind(Optional Match As String) As String()
Dim n%, lRet&, sBuf$, sCon$, aPrn$()
Const lLen& = 1024, sKey$ = "devices"


'------------------------------------------------------------------
'written by keepITcool


'requires xl2000 or newer.
'returns a zerobased array of complete localized printer strings
'results are filtered on Match string, if no result the ubound = -1
'------------------------------------------------------------------


'Split ActivePrinter string to get localized word for "on"
aPrn = Split(Excel.ActivePrinter)
sCon = " " & aPrn(UBound(aPrn) - 1) & " "


'Read all installed printers (1k bytes s/b enough)
sBuf = Space(lLen)
lRet = GetProfileString(sKey, vbNullString, vbNullString, sBuf, lLen)
If lRet = 0 Then
Err.Raise vbObjectError + 513, , "Can't read Profile"
Exit Function
End If


'Split buffer string
aPrn = Split(Left(sBuf, lRet - 1), vbNullChar)
'Filter array on Match
If Match < vbNullString Then aPrn = Filter(aPrn, Match, -1, 1)


For n = LBound(aPrn) To UBound(aPrn)
'Add 16bit portname for each Printer
sBuf = Space(lLen)
lRet = GetProfileString(sKey, aPrn(n), vbNullString, sBuf, lLen)
aPrn(n) = aPrn(n) & sCon & _
Mid(sBuf, InStr(sBuf, ",") + 1, lRet - InStr(sBuf, ","))
Next
'Return the result
PrinterFind = aPrn


End Function


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


--
Regards,
Tom Ogilvy


"Andy" wrote:

My colleagues at work regularly have a several hundred of Excel
spreadsheets that need Printing on either white paper or as PDFs. I
have created a user form, so that each team member can choose which
print method they want before printing. I have produced some VBA that
will then do the job automatically.

The code mostly works fine for printing paper copies. However each
person in the network has there printer set up differently. On one
machine the printer will be listed as "Brother HL-2460 series on Ne01:"
on another it will be "Brother HL-2460 series on Ne03:" etc. Could
someone tell me how to set things up, so that the setting is the same
for all people in my team. E.G. I would like it if the setting is
"Brother HL-2460 series on Ne01:" for everyone.
When printing PDFs I have the same problem. The active printer is
listed as "Adobe PDF on Ne03:", on another it may be "Adobe PDF on
Ne04:" etc.
A more serious problem is that the PDFs only print on the computer
where an early version of PDF writer is installed. I have found out
that a way round this problem is to get the macro to first write a
postscript file using the acrobat distiller, then convert the
postscript to a PDF. I found following code on the web:

Sub PrintPDF()

' Define the postscript and .pdf file names.
Dim PSFileName as String
Dim PDFFileName as String
PSFileName = "c:\myPostScript.ps"
PDFFileName = "c:\myPDF.pdf"

' Print the Excel range to the postscript file
Dim MySheet As WorkSheet
Set MySheet = ActiveSheet
MySheet.Range("myRange").PrintOut copies:=1, preview:=False,
ActivePrinter:="Acrobat Distiller", printtofile:=True, collate:=True,
prttofilename:=PSFileName

' Convert the postscript file to .pdf
Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""

End Sub

Private Sub CommandButton1_Click()

' Define the postscript and .pdf file names.
Dim PSFileName as String
Dim PDFFileName as String
PSFileName = "c:\myPostScript.ps"
PDFFileName = "c:\myPDF.pdf"

' Print the Excel range to the postscript file
Dim MySheet As WorkSheet
Set MySheet = ActiveSheet
MySheet.Range("myRange").PrintOut copies:=1, preview:=False,
ActivePrinter:="Acrobat Distiller", printtofile:=True, collate:=True,
prttofilename:=PSFileName

' Convert the postscript file to .pdf
Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""

End Sub

The above code looks like it will do fine. However when I go to file =
print in an excel document I do not get the option to set the printer
to "Acrobat Distiller". If I can't do the job manually, I can't get
the job done with a macro. Could someone help me set up the my system
so I can use the distiller in this way?

Thanks,

Andy.

Hi, I tried your code and it works fine except that I just get
"Adobe PDF" instead of "Adobe PDF on Ne01"
But when I was using VBA code to do the printing.
I need "Adobe PDF on Ne01" to make it work
Could you tell me what does this "Ne01" really means?
Thanks a lot!

"Tom Ogilvy" wrote:

I am not sure you can configure which port it uses. Better would be to adapt
your code to use the appropriate port. Here are two different approachs -
the first includes the question being responded to:

I'd like to get a array of all installed printers incl. the connected port.
The array should look like this:


HP LaserJet 6P on LPT1:
Adobe PDF on Ne03:
SnagIt 6 on Ne00:


I already have the following procedure, but this code doesn't return the
connected port:


Private Sub ListPrinters()
Dim wshNetwork As Object
Dim oDrives As Object
Dim oPrinters As Object
Dim iCount As Integer
Dim sCurrentprinter As String
sCurrentprinter = Application.ActivePrinter
Set wshNetwork = CreateObject("WScript.Network")
Set oDrives = wshNetwork.EnumNetworkDrives
Set oPrinters = wshNetwork.EnumPrinterConnections
For iCount = 0 To oPrinters.Count - 1 Step 2
Debug.Print oPrinters.Item(iCount + 1)
Next
End Sub


Many thanks in advance.

Michael Pierron responds

Hi Tom,
For iCount = 0 To oPrinters.Count - 1 Step 2
' Debug.Print "Printer Port " & oPrinter.Item(iCount) & " = " & _
' oPrinter.Item(iCount + 1)
Debug.Print "Printer Port " & oPrinters.Item(iCount) _
& " = " & oPrinters.Item(iCount + 1)
Next


MP

----------------
KeepItCool
---------------
Hi Thomas


This should produce exactly what you're looking for
but note it will NOT work for xl97


The PrinterFind function will return an array of
localized strings ready to assign to the ActivePrinter.


I've amended an old post from myself to allow filtering.
see test procedure for example of how to use.
On a userform you could simply use s'th like
cboPrinters.list=PrinterFind


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 Test()
Dim vaList
'Get all printers
vaList = PrinterFind
'Show m
MsgBox Join(vaList, vbLf), , "List of printers"


'Get all laserjets
vaList = PrinterFind(Match:="Laserjet")


'Switch to the first laserjet found
If UBound(vaList) = -1 Then
MsgBox "Printer not found"
ElseIf MsgBox("from " & vbTab & ": " & ActivePrinter & vbLf & _
"to " & vbTab & ": " & vaList(0), _
vbOKCancel, "Switch Printers") = vbOK Then
Application.ActivePrinter = vaList(0)
End If
End Sub


Public Function PrinterFind(Optional Match As String) As String()
Dim n%, lRet&, sBuf$, sCon$, aPrn$()
Const lLen& = 1024, sKey$ = "devices"


'------------------------------------------------------------------
'written by keepITcool


'requires xl2000 or newer.
'returns a zerobased array of complete localized printer strings
'results are filtered on Match string, if no result the ubound = -1
'------------------------------------------------------------------


'Split ActivePrinter string to get localized word for "on"
aPrn = Split(Excel.ActivePrinter)
sCon = " " & aPrn(UBound(aPrn) - 1) & " "


'Read all installed printers (1k bytes s/b enough)
sBuf = Space(lLen)
lRet = GetProfileString(sKey, vbNullString, vbNullString, sBuf, lLen)
If lRet = 0 Then
Err.Raise vbObjectError + 513, , "Can't read Profile"
Exit Function
End If


'Split buffer string
aPrn = Split(Left(sBuf, lRet - 1), vbNullChar)
'Filter array on Match
If Match < vbNullString Then aPrn = Filter(aPrn, Match, -1, 1)


For n = LBound(aPrn) To UBound(aPrn)
'Add 16bit portname for each Printer
sBuf = Space(lLen)
lRet = GetProfileString(sKey, aPrn(n), vbNullString, sBuf, lLen)
aPrn(n) = aPrn(n) & sCon & _
Mid(sBuf, InStr(sBuf, ",") + 1, lRet - InStr(sBuf, ","))
Next
'Return the result
PrinterFind = aPrn


End Function


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


--
Regards,
Tom Ogilvy


"Andy" wrote:

My colleagues at work regularly have a several hundred of Excel
spreadsheets that need Printing on either white paper or as PDFs. I
have created a user form, so that each team member can choose which
print method they want before printing. I have produced some VBA that
will then do the job automatically.

The code mostly works fine for printing paper copies. However each
person in the network has there printer set up differently. On one
machine the printer will be listed as "Brother HL-2460 series on Ne01:"
on another it will be "Brother HL-2460 series on Ne03:" etc. Could
someone tell me how to set things up, so that the setting is the same
for all people in my team. E.G. I would like it if the setting is
"Brother HL-2460 series on Ne01:" for everyone.
When printing PDFs I have the same problem. The active printer is
listed as "Adobe PDF on Ne03:", on another it may be "Adobe PDF on
Ne04:" etc.
A more serious problem is that the PDFs only print on the computer
where an early version of PDF writer is installed. I have found out
that a way round this problem is to get the macro to first write a
postscript file using the acrobat distiller, then convert the
postscript to a PDF. I found following code on the web:

Sub PrintPDF()

' Define the postscript and .pdf file names.
Dim PSFileName as String
Dim PDFFileName as String
PSFileName = "c:\myPostScript.ps"
PDFFileName = "c:\myPDF.pdf"

' Print the Excel range to the postscript file
Dim MySheet As WorkSheet
Set MySheet = ActiveSheet
MySheet.Range("myRange").PrintOut copies:=1, preview:=False,
ActivePrinter:="Acrobat Distiller", printtofile:=True, collate:=True,
prttofilename:=PSFileName

' Convert the postscript file to .pdf
Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""

End Sub

Private Sub CommandButton1_Click()

' Define the postscript and .pdf file names.
Dim PSFileName as String
Dim PDFFileName as String
PSFileName = "c:\myPostScript.ps"
PDFFileName = "c:\myPDF.pdf"

' Print the Excel range to the postscript file
Dim MySheet As WorkSheet
Set MySheet = ActiveSheet
MySheet.Range("myRange").PrintOut copies:=1, preview:=False,
ActivePrinter:="Acrobat Distiller", printtofile:=True, collate:=True,
prttofilename:=PSFileName

' Convert the postscript file to .pdf
Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""

End Sub

The above code looks like it will do fine. However when I go to file =
print in an excel document I do not get the option to set the printer
to "Acrobat Distiller". If I can't do the job manually, I can't get
the job done with a macro. Could someone help me set up the my system
so I can use the distiller in this way?

Thanks,

Andy.