Hi

Does anybody know how to print out with VBA a worksheet with Adobe Acrobat
6.0?

Tom

"Tom" wrote in
:

Hi

Does anybody know how to print out with VBA a worksheet with Adobe
Acrobat 6.0?

Tom




Here's a code snippet I've used when printing out worksheets with the
Adobe Distiller. You'll need to reference the "Adobe Distiller" library
and the "Registration Manipulation Classes" library. The key to printing
with the Adobe Distiller is that the device name is not guaranteed to be
the same on any two machines. There's a numerical value in the device
name when installed that can change based on other installed devices, but
the GetDistillerPrinterName gets the correct value.

Sub SomeMethod(ws As Excel.Worksheet, fileDest As String)
Dim aDist As New ACRODISTXLib.PdfDistiller
Dim sPrinter As String

sPrinter = GetDistillerPrinterName 'See function below
If sPrinter = vbNullString Then
MsgBox "Adobe Distiller printer not found!"
Exit Sub
End If

ws.PrintOut 1, , , , sPrinter, True, , fileDest & ".ps"
aDist.FileToPDF fileDest & ".ps", fileDest & ".pdf", ""
Kill fileDest & ".ps"
End Sub

'-----------------------------------------------------------------------

Public Function GetDistillerPrinterName() As String
'Purpose: Examines registry to determine the printer name for installed
' Adobe Distiller.

Dim rKey As RegObj.RegKey
Dim s As String

Set rKey = Registry.RegKeyFromString("\HKEY_CURRENT_USER\Soft ware
\Microsoft\Windows NT\CurrentVersion\Devices\")
s = rKey.Values("Acrobat Distiller").Value
If Len(s) Then
GetDistillerPrinterName = "Acrobat Distiller on " & Replace(s,
"winspool,", "")
End If
End Function


HTH
--
Andrew Mauer

To reply directly, remove .nospam from address.