Converting Acces reports to PDF using pdfFactory

[[email protected]]

Hi forum readers,

in the past 2 days i've been trying to find a way to (automatically) convert Acces reports to PDF files. The best way i've come across is to install a PDF printer, a program that acts as a printer but instead of spitting paper, outouts a pdf file. In my case i've installed pdfFactory from Fine Print http://www.fineprint.com/products/index.html

In addition i've found the following code at www.planet-source-code.com (many cheers for mister Benson) that basicaly switches the default printer to the PDF printer, generates the PDF file and then switches the defaul back to what it was

'**************************************
'Windows API/Global Declarations for :Cr
'     eate PDF from MS Access Report
'**************************************

Declare Function RegEnumValue Lib "advapi32.dll" Alias "RegEnumValueA" (ByVal hKey As Long, ByVal dwIndex As Long, ByVal lpValueName As String, lpcbValueName As Long, ByVal lpReserved As Long, lpType As Long, ByVal lpData As String, lpcbData As Long) As Long

Declare Function RegOpenKeyEx Lib "advapi32" Alias "RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal ulOptions As Long, ByVal samDesired As Long, phkResult As Long) As Long

Declare Function RegSetValueEx Lib "advapi32" Alias "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal Reserved As Long, ByVal dwType As Long, ByVal szData As String, ByVal cbData As Long) As Long

Declare Function RegCloseKey Lib "advapi32" (ByVal hKey As Long) As Long

Declare Function RegCreateKeyEx Lib "advapi32" Alias "RegCreateKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal Reserved As Long, ByVal lpClass As String, ByVal dwOptions As Long, ByVal samDesired As Long, lpSecurityAttributes As SECURITY_ATTRIBUTES, phkResult As Long, lpdwDisposition As Long) As Long

    #If Win32 Then
        Public Const HKEY_CLASSES_ROOT = &H80000000
        Public Const HKEY_CURRENT_USER = &H80000001
        Public Const HKEY_LOCAL_MACHINE = &H80000002
        Public Const HKEY_USERS = &H80000003
        Public Const KEY_ALL_ACCESS = &H3F
        Public Const REG_OPTION_NON_VOLATILE = 0&
        Public Const REG_CREATED_NEW_KEY = &H1
        Public Const REG_OPENED_EXISTING_KEY = &H2
        Public Const ERROR_SUCCESS = 0&
        Public Const REG_SZ = (1)
    #End If


Type SECURITY_ATTRIBUTES
    nLength As Long
    lpSecurityDescriptor As Long
    bInheritHandle As Boolean
    End Type
'**************************************
' Name: Create PDF from MS Access Report
'
' Description:On a machine where the Ado
'     be PDFWriter is installed, the current p
'     rinter is swapped out with the PDFWriter
'     and the PDF file is created. The origina
'     l printer is then restored.
' By: Todd Benson
'
'
' Inputs:rptName = Microsoft Access repo
'     rt name you want to create pdf from. sPD
'     FPath = the directory path where you wan
'     t to create the pdf file (ex. - "c:\data
'     \"). sPDFName = the name of the pdf file
'     you are wanting to create (ex. - "file00
'     1.pdf").
'
' Returns:None
'
'Assumes:This code is easily modified to
'     be used in other programs
'
'Side Effects:Please use the most recent
'     installs of Adobe Exchange or PDFWriter
'     to ensure proper functionality.
'This code is copyrighted and has limite
'     d warranties.
'Please see http://www.Planet-Source-Cod
'     e.com/xq/ASP/txtCodeId.35321/lngWId.1/qx
'     /vb/scripts/ShowCode.htm
'for details.
'**************************************
Public Function bGetRegValue(ByVal hKey As Long, ByVal sKey As String, ByVal sSubKey As String) As String
    Dim lResult As Long
    Dim phkResult As Long
    Dim dWReserved As Long
    Dim szBuffer As String
    Dim lBuffSize As Long
    Dim szBuffer2 As String
    Dim lBuffSize2 As Long
    Dim lIndex As Long
    Dim lType As Long
    Dim sCompKey As String
    Dim bFound As Boolean
    lIndex = 0
    lResult = RegOpenKeyEx(hKey, sKey, 0, 1, phkResult)

    Do While lResult = ERROR_SUCCESS And Not (bFound)
        szBuffer = Space(255)
        lBuffSize = Len(szBuffer)
        szBuffer2 = Space(255)
        lBuffSize2 = Len(szBuffer2)
        lResult = RegEnumValue(phkResult, lIndex, szBuffer, lBuffSize, dWReserved, lType, szBuffer2, lBuffSize2)

        If (lResult = ERROR_SUCCESS) Then
            sCompKey = Left(szBuffer, lBuffSize)
            If (sCompKey = sSubKey) Then
                bGetRegValue = Left(szBuffer2, lBuffSize2 - 1)
                RegCloseKey phkResult
                Exit Function
            End If
        End If
        lIndex = lIndex + 1
    Loop
    RegCloseKey phkResult
End Function

Public Function bSetRegValue(ByVal hKey As Long, ByVal lpszSubKey As String, ByVal sSetValue As String, ByVal sValue As String) As Boolean
    On Error Resume Next
    Dim phkResult As Long
    Dim lResult As Long
    Dim SA As SECURITY_ATTRIBUTES
    Dim lCreate As Long
    RegCreateKeyEx hKey, lpszSubKey, 0, "", REG_OPTION_NON_VOLATILE, KEY_ALL_ACCESS, SA, phkResult, lCreate
    lResult = RegSetValueEx(phkResult, sSetValue, 0, REG_SZ, sValue, CLng(Len(sValue) + 1))
    RegCloseKey phkResult
    bSetRegValue = (lResult = ERROR_SUCCESS)
End Function

Public Function RunReportAsPDF(rptName As String, sPDFPath As String, sPDFName As String)
    '---------------------------------
    'rptName = Microsoft Access report name
    '     you
    'want to create pdf from
    'sPDFPath = the directory path where you
    '     want
    'to create the pdf file (ex. - "c:\data\
    '     ")
    'sPDFName = the name of the pdf file you
    '     are
    'wanting to create (ex. - "file001.pdf")
    '
    '---------------------------------
    Dim sMyDefPrinter As String
    On Error GoTo Err_RunReport
    'Save current default printer
    sMyDefPrinter = bGetRegValue(HKEY_CURRENT_USER, "Software\Microsoft\WIndows NT\CurrentVersion\Windows", "Device")
    ' Set default printer to PDF Writer
    bSetRegValue HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device", "FinePrint pdfFactory Pro,winspool,FPP1:"
    'Setting value for PDFFileName in the registry stops file dialog box from appearing
    bSetRegValue HKEY_CURRENT_USER, "Software\FinePrint Software\pdfFactory", "PDFFileName", sPDFPath + sPDFName
    'Run the report
    DoCmd.OpenReport rptName, acViewNormal
Exit_RunReport:
        ' Restore default printer
        bSetRegValue HKEY_CURRENT_USER, "Software\Microsoft\WIndows NT\CurrentVersion\Windows", "Device", sMyDefPrinter
        Exit Function
Err_RunReport:
        MsgBox Err.Description
        Resume Exit_RunReport
    End Function

I then call the funtion like so

PDF = RunReportAsPDF("Reportname", "C:\temp\", "test.pdf")

Now comes the problem: In the original code by mister Benson, he uses Adobe PDFwriter instead of pdfFactory

bSetRegValue HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device", "FinePrint pdfFactory Pro,winspool,FPP1:"

So that it now points to pdfFactory. The only problem i still have is that i get a preview/save as dialog box before the document gets created. I would like to see the whole process automated. It apears that the line where i set the PDF filename, the line in bold, doesn't have the same effect as it does in PDFwriter. Is there anyone out here that can shed some light on this, is there a way anyone knows of that gets around this.

Many thank for even reading this far. Greets Ruben

[westconn1]

i have done similar in VB using cute pdf and was able to use sendkeys to fill in the dialog and close.
sendkeys does work in vba, but whether you will be able to get the keystrokes into the right place, i don't know, maybe if you hide the access window while you send the keys, and also you might have to put in a delay (sleep api) to wait for the dialog to appear.

pete