I created a Macro to PDF certain pages of an Excel sheet using the following
code

ActiveWindow.View = xlPageBreakPreview
Range("A1:G86,A172:G263").Select
Range("A172").Activate
Application.ActivePrinter = "Adobe PDF on Ne06:"
ActiveWindow.Selection.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF on Ne06:", Collate:=True
ActiveWindow.View = xlNormalView
Range("B7").Select

A week later when I went to use the macro it failed as the Printer is now:

Adobe PDF on Ne08

How can I set the macro to always find the "Adobe PDF" printer no matter
what situation or "Ne" that it sits on?.

Iam currently using Adobe Pro 6. As a matter of interest, are there any
versions of Adobe writer that allow you to PDF a selection of cells whether
contiguous or non-contiguous?

Any assistance would be greatly appreciated.

I found some code at Microsoft that should work for you. I've modified the
Sub they provided to be a Function that returns either an empty string ("")
if no Adobe PDF printer is found, or else returns the name in the format you
need if it is. Try this out - a test Sub is at the end, and in your code
you'd put the code snippet you published within the first section of the IF
test after the call - you can try it as is to see if the name being returned
is as you need. Reference to the web page is included. I left their
Debug.Print statements in the code, but commented them out so you can relate
back to original code if you need to. Let me know if you have any questions.

'from http://support.microsoft.com/kb/q166008/

Const PRINTER_ENUM_CONNECTIONS = &H4
Const PRINTER_ENUM_LOCAL = &H2

Type PRINTER_INFO_1
flags As Long
pDescription As String
PName As String
PComment As String
End Type

Type PRINTER_INFO_4
pPrinterName As String
pServerName As String
Attributes As Long
End Type

Declare Function EnumPrinters Lib "winspool.drv" Alias _
"EnumPrintersA" (ByVal flags As Long, ByVal name As String, _
ByVal Level As Long, pPrinterEnum As Long, ByVal cdBuf As Long, _
pcbNeeded As Long, pcReturned As Long) As Long
Declare Function PtrToStr Lib "Kernel32" Alias "lstrcpyA" _
(ByVal RetVal As String, ByVal Ptr As Long) As Long
Declare Function StrLen Lib "Kernel32" Alias "lstrlenA" _
(ByVal Ptr As Long) As Long


Function FindPDF() As String
'modified from code found at:
'from http://support.microsoft.com/kb/q166008/
' specifically from Sub EnumeratePrinters4()
'
'INPUT: none required
'OUTPUT: FAILURE - an empty string is returned
' SUCCESS - name of Adobe PDF printer returned

Dim Success As Boolean
Dim cbRequired As Long, cbBuffer As Long
Dim Buffer() As Long, nEntries As Long
Dim I As Long, PName As String, SName As String
Dim Attrib As Long, Temp As Long

cbBuffer = 3072
ReDim Buffer((cbBuffer \ 4) - 1) As Long
'set return to "" (empty string) for testing
'on return from function for failure
FindPDF = ""

Success = EnumPrinters(PRINTER_ENUM_CONNECTIONS Or _
PRINTER_ENUM_LOCAL, vbNullString, 4, _
Buffer(0), cbBuffer, cbRequired, nEntries)

If Success Then
If cbRequired cbBuffer Then
cbBuffer = cbRequired
' Debug.Print "Buffer too small. Trying again with " & _
cbBuffer & " bytes."
ReDim Buffer(cbBuffer \ 4) As Long
Success = EnumPrinters(PRINTER_ENUM_CONNECTIONS Or _
PRINTER_ENUM_LOCAL, vbNullString, 4, _
Buffer(0), cbBuffer, cbRequired, nEntries)
If Not Success Then
' Debug.Print "Error enumerating printers."
Exit Function
End If
End If
' Debug.Print "There are " & nEntries & _
" local and connected printers."
For I = 0 To nEntries - 1
PName = Space$(StrLen(Buffer(I * 3)))
Temp = PtrToStr(PName, Buffer(I * 3))
SName = Space$(StrLen(Buffer(I * 3 + 1)))
Temp = PtrToStr(SName, Buffer(I * 3 + 1))
Attrib = Buffer(I * 3 + 2)
'test if printer is "Adobe PDF" and if it is
'concatenate PName and SName to get full name
If UCase(Trim(PName)) = "ADOBE PDF" Then
'if Sname is empty, it's local printer
If Trim(SName) < "" Then
'shared printer
'Build the network printer name
FindPDF = Trim(PName) & " On " & Trim(SName) & ":"
Else
FindPDF = Trim(PName)
End If
End If
' Debug.Print "Printer: " & PName, "Server: " & SName, _
"Attributes: " & Hex$(Attrib)
Next I
Else
'do nothing - we had an error
End If

End Function

Sub TestFindPDF()
Dim PrinterName As String

PrinterName = FindPDF()
If PrinterName = "" Then
MsgBox "No Adobe PDF printer found"
Else
MsgBox "Adobe Printer found as:" _
& vbCrLf & PrinterName
End If
End Sub




"I think I need to rephrase the question" wrote:

I created a Macro to PDF certain pages of an Excel sheet using the following
code

ActiveWindow.View = xlPageBreakPreview
Range("A1:G86,A172:G263").Select
Range("A172").Activate
Application.ActivePrinter = "Adobe PDF on Ne06:"
ActiveWindow.Selection.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF on Ne06:", Collate:=True
ActiveWindow.View = xlNormalView
Range("B7").Select

A week later when I went to use the macro it failed as the Printer is now:

Adobe PDF on Ne08

How can I set the macro to always find the "Adobe PDF" printer no matter
what situation or "Ne" that it sits on?.

Iam currently using Adobe Pro 6. As a matter of interest, are there any
versions of Adobe writer that allow you to PDF a selection of cells whether
contiguous or non-contiguous?

Any assistance would be greatly appreciated.

Thanks for your help. I will give it a try.

"JLatham" wrote:

I found some code at Microsoft that should work for you. I've modified the
Sub they provided to be a Function that returns either an empty string ("")
if no Adobe PDF printer is found, or else returns the name in the format you
need if it is. Try this out - a test Sub is at the end, and in your code
you'd put the code snippet you published within the first section of the IF
test after the call - you can try it as is to see if the name being returned
is as you need. Reference to the web page is included. I left their
Debug.Print statements in the code, but commented them out so you can relate
back to original code if you need to. Let me know if you have any questions.

'from http://support.microsoft.com/kb/q166008/

Const PRINTER_ENUM_CONNECTIONS = &H4
Const PRINTER_ENUM_LOCAL = &H2

Type PRINTER_INFO_1
flags As Long
pDescription As String
PName As String
PComment As String
End Type

Type PRINTER_INFO_4
pPrinterName As String
pServerName As String
Attributes As Long
End Type

Declare Function EnumPrinters Lib "winspool.drv" Alias _
"EnumPrintersA" (ByVal flags As Long, ByVal name As String, _
ByVal Level As Long, pPrinterEnum As Long, ByVal cdBuf As Long, _
pcbNeeded As Long, pcReturned As Long) As Long
Declare Function PtrToStr Lib "Kernel32" Alias "lstrcpyA" _
(ByVal RetVal As String, ByVal Ptr As Long) As Long
Declare Function StrLen Lib "Kernel32" Alias "lstrlenA" _
(ByVal Ptr As Long) As Long


Function FindPDF() As String
'modified from code found at:
'from http://support.microsoft.com/kb/q166008/
' specifically from Sub EnumeratePrinters4()
'
'INPUT: none required
'OUTPUT: FAILURE - an empty string is returned
' SUCCESS - name of Adobe PDF printer returned

Dim Success As Boolean
Dim cbRequired As Long, cbBuffer As Long
Dim Buffer() As Long, nEntries As Long
Dim I As Long, PName As String, SName As String
Dim Attrib As Long, Temp As Long

cbBuffer = 3072
ReDim Buffer((cbBuffer \ 4) - 1) As Long
'set return to "" (empty string) for testing
'on return from function for failure
FindPDF = ""

Success = EnumPrinters(PRINTER_ENUM_CONNECTIONS Or _
PRINTER_ENUM_LOCAL, vbNullString, 4, _
Buffer(0), cbBuffer, cbRequired, nEntries)

If Success Then
If cbRequired cbBuffer Then
cbBuffer = cbRequired
' Debug.Print "Buffer too small. Trying again with " & _
cbBuffer & " bytes."
ReDim Buffer(cbBuffer \ 4) As Long
Success = EnumPrinters(PRINTER_ENUM_CONNECTIONS Or _
PRINTER_ENUM_LOCAL, vbNullString, 4, _
Buffer(0), cbBuffer, cbRequired, nEntries)
If Not Success Then
' Debug.Print "Error enumerating printers."
Exit Function
End If
End If
' Debug.Print "There are " & nEntries & _
" local and connected printers."
For I = 0 To nEntries - 1
PName = Space$(StrLen(Buffer(I * 3)))
Temp = PtrToStr(PName, Buffer(I * 3))
SName = Space$(StrLen(Buffer(I * 3 + 1)))
Temp = PtrToStr(SName, Buffer(I * 3 + 1))
Attrib = Buffer(I * 3 + 2)
'test if printer is "Adobe PDF" and if it is
'concatenate PName and SName to get full name
If UCase(Trim(PName)) = "ADOBE PDF" Then
'if Sname is empty, it's local printer
If Trim(SName) < "" Then
'shared printer
'Build the network printer name
FindPDF = Trim(PName) & " On " & Trim(SName) & ":"
Else
FindPDF = Trim(PName)
End If
End If
' Debug.Print "Printer: " & PName, "Server: " & SName, _
"Attributes: " & Hex$(Attrib)
Next I
Else
'do nothing - we had an error
End If

End Function

Sub TestFindPDF()
Dim PrinterName As String

PrinterName = FindPDF()
If PrinterName = "" Then
MsgBox "No Adobe PDF printer found"
Else
MsgBox "Adobe Printer found as:" _
& vbCrLf & PrinterName
End If
End Sub




"I think I need to rephrase the question" wrote:

I created a Macro to PDF certain pages of an Excel sheet using the following
code

ActiveWindow.View = xlPageBreakPreview
Range("A1:G86,A172:G263").Select
Range("A172").Activate
Application.ActivePrinter = "Adobe PDF on Ne06:"
ActiveWindow.Selection.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF on Ne06:", Collate:=True
ActiveWindow.View = xlNormalView
Range("B7").Select

A week later when I went to use the macro it failed as the Printer is now:

Adobe PDF on Ne08

How can I set the macro to always find the "Adobe PDF" printer no matter
what situation or "Ne" that it sits on?.

Iam currently using Adobe Pro 6. As a matter of interest, are there any
versions of Adobe writer that allow you to PDF a selection of cells whether
contiguous or non-contiguous?

Any assistance would be greatly appreciated.