Automate printing using Adobe 7.0 with VBA in Excel

[bmcilroy]

I have successfully automated printing an Excel sheet using VBA to a defined sub-directory using Adobe 5.0 (utilizing pdfwriter). I bought Adobe 7.0 and can not figure out which registration files to manipulate to achieve the same results using the Adobe PDF. I need 7.0 for some of the functions that it has that 5.0 doesn't have. I am currently running both versions, but am finding out some of the recipients that I'm sending the .pdfs to are unable to open them. I'm including an excerpt of my current code for whomever to review.

Start Code

_________________________________________________________

Option Explicit

Private Const HKEY_CURRENT_USER = &H80000001
Private Const STANDARD_RIGHTS_ALL = &H1F0000
Private Const KEY_QUERY_VALUE = &H1
Private Const KEY_SET_VALUE = &H2
Private Const KEY_CREATE_SUB_KEY = &H4
Private Const KEY_NOTIFY = &H10
Private Const KEY_ENUMERATE_SUB_KEYS = &H8
Private Const KEY_CREATE_LINK = &H20
Private Const SYNCHRONIZE = &H100000
Private Const KEY_ALL_ACCESS = ((STANDARD_RIGHTS_ALL Or

KEY_QUERY_VALUE Or _ KEY_SET_VALUE Or KEY_CREATE_SUB_KEY Or _ KEY_ENUMERATE_SUB_KEYS Or KEY_NOTIFY Or _ KEY_CREATE_LINK) And (Not SYNCHRONIZE))

Private Declare Function RegOpenKeyEx Lib "ADVAPI32.DLL" Alias "RegOpenKeyExA" _
(ByVal hKey As Long, ByVal lpSubKey As String, _
ByVal ulOptions As Long, ByVal samDesired As Long, _
phkResult As Long) As Long

Private Declare Function RegCloseKey Lib "ADVAPI32.DLL" (ByVal hKey As Long) As Long

Private Declare Function RegSetValueExA Lib "ADVAPI32.DLL" _
(ByVal hKey As Long, ByVal sValueName As String, _
ByVal dwReserved As Long, ByVal dwType As Long, _
ByVal sValue As String, ByVal dwSize As Long) As Long

Private Declare Function RegCreateKeyA Lib "ADVAPI32.DLL" _
(ByVal hKey As Long, ByVal sSubKey As String, ByRef hkeyResult As Long) As Long

Private Function WriteRegistry(TheKey As Long, _ Folder As String, _Key As String, _Value As Variant) As Boolean

Dim hKey As Long, x As Long

RegOpenKeyEx TheKey, Folder, 0, KEY_ALL_ACCESS, hKey

If hKey <> 0 Then x = RegSetValueExA(hKey, Key, 0, 1, Value, Len(Value) + 1)

WriteRegistry = (x = 0)RegCloseKey hKey

End If

End Function

Sub Print_Individual()

Dim PDF1 As String

PDF1 = "\\Our_Desktop\shareddocs\Truth Benefits\Quotes\Individual\" & Worksheets("Individual").Range("F2").Value & ".pdf"


Call WriteRegistry(HKEY_CURRENT_USER, "Software\Adobe\Acrobat PDFWriter\", _"PDFFileName", _PDF1)


Sheets(Array("Individual")).Select Application.ActivePrinter = "Acrobat PDFWriter on LPT1:" ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _"Acrobat PdfWriter on LPT1:"


End Sub

________________________________________________________

End Code