Hi,

I want to add a button on a sheet that will print sheets 5-9 to a pdf file.
I'm gonna have a play to try and work this out myself (I'm even gonna try and
get it to calculate page numbers and create a contents page within the
workbook! - any tips appreciated!!).

Starting question I have that I certainly need help with is this:
Is there some code I can write to identify whether or not the user has pdf
writer installed on their machine?

I want to disable the button on sheet activation if they don't have the
appropriate software.

Thanks! Basil

If you talk about the 2007 add-in see the code on this page
http://www.rondebruin.nl/pdf.htm

you can test it like this

'Test If the Microsoft Add-in is installed
If Dir(Environ("commonprogramfiles") & "\Microsoft Shared\OFFICE" _
& Format(Val(Application.Version), "00") & "\EXP_PDF.DLL") < "" Then


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Basil" wrote in message ...
Hi,

I want to add a button on a sheet that will print sheets 5-9 to a pdf file.
I'm gonna have a play to try and work this out myself (I'm even gonna try and
get it to calculate page numbers and create a contents page within the
workbook! - any tips appreciated!!).

Starting question I have that I certainly need help with is this:
Is there some code I can write to identify whether or not the user has pdf
writer installed on their machine?

I want to disable the button on sheet activation if they don't have the
appropriate software.

Thanks! Basil

I modified some Microsoft code that's available and you can give this a go.
You'd want to DISABLE your button either before calling the routine, or at
the beginning of it, and the code to ENABLE it would be down in it (I mark
the spot).

Option Explicit
'source: http://support.microsoft.com/kb/q166008/
' modified to specifically ID "Adobe PDF" printer
'
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

Sub FindPDFPrinter()
Dim Success As Boolean, 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

'turn your button OFF before starting this
' code to turn the button off here?
'
cbBuffer = 3072
ReDim Buffer((cbBuffer \ 4) - 1) As Long
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
MsgBox "Error enumerating printers."
Exit Sub
End If
End If
For I = 0 To nEntries - 1
PName = Space$(StrLen(Buffer(I * 3)))
Temp = PtrToStr(PName, Buffer(I * 3))
If PName = "Adobe PDF" Then
'turn your button ON! here
MsgBox "Adobe PDF Printer FOUND!"
Exit For
End If
Next
Else
MsgBox "Error enumerating printers."
End If
End Sub



"Basil" wrote:

Hi,

I want to add a button on a sheet that will print sheets 5-9 to a pdf file.
I'm gonna have a play to try and work this out myself (I'm even gonna try and
get it to calculate page numbers and create a contents page within the
workbook! - any tips appreciated!!).

Starting question I have that I certainly need help with is this:
Is there some code I can write to identify whether or not the user has pdf
writer installed on their machine?

I want to disable the button on sheet activation if they don't have the
appropriate software.

Thanks! Basil

Awesome, thanks guys.

I used JLatham's solution (Excel 2003) and did a bit of modifying myself =o)

B

"JLatham" wrote:

I modified some Microsoft code that's available and you can give this a go.
You'd want to DISABLE your button either before calling the routine, or at
the beginning of it, and the code to ENABLE it would be down in it (I mark
the spot).

Option Explicit
'source: http://support.microsoft.com/kb/q166008/
' modified to specifically ID "Adobe PDF" printer
'
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

Sub FindPDFPrinter()
Dim Success As Boolean, 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

'turn your button OFF before starting this
' code to turn the button off here?
'
cbBuffer = 3072
ReDim Buffer((cbBuffer \ 4) - 1) As Long
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
MsgBox "Error enumerating printers."
Exit Sub
End If
End If
For I = 0 To nEntries - 1
PName = Space$(StrLen(Buffer(I * 3)))
Temp = PtrToStr(PName, Buffer(I * 3))
If PName = "Adobe PDF" Then
'turn your button ON! here
MsgBox "Adobe PDF Printer FOUND!"
Exit For
End If
Next
Else
MsgBox "Error enumerating printers."
End If
End Sub



"Basil" wrote:

Hi,

I want to add a button on a sheet that will print sheets 5-9 to a pdf file.
I'm gonna have a play to try and work this out myself (I'm even gonna try and
get it to calculate page numbers and create a contents page within the
workbook! - any tips appreciated!!).

Starting question I have that I certainly need help with is this:
Is there some code I can write to identify whether or not the user has pdf
writer installed on their machine?

I want to disable the button on sheet activation if they don't have the
appropriate software.

Thanks! Basil