INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How To

Save A Report As PDF by SgtJarrow
Posted: 2 Oct 02 (Edited 21 Sep 06)

February 3, 2004

One of my coworkers today discovered something that was causing us some grief.  After installing OfficeXP on his WindowsXP machine, the below code no longer worked.  We finally determine that the registry keys were being changed, but that AccessXP now handles the default printer variable a bit differently.  Walking through the code showed that everything was working correctly, just that Access was not reading it right.  So we created some new code and here it is.  Again, this is for OfficeXP and I believe WindowsXP and newer.  My OfficeXP on my Windows2000 box works fine with the original code.

' #########################################################
Option Compare Database

   Public Const REG_SZ As Long = 1
   Public Const REG_DWORD As Long = 4

   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 ERROR_NONE = 0
   Public Const ERROR_BADDB = 1
   Public Const ERROR_BADKEY = 2
   Public Const ERROR_CANTOPEN = 3
   Public Const ERROR_CANTREAD = 4
   Public Const ERROR_CANTWRITE = 5
   Public Const ERROR_OUTOFMEMORY = 6
   Public Const ERROR_ARENA_TRASHED = 7
   Public Const ERROR_ACCESS_DENIED = 8
   Public Const ERROR_INVALID_PARAMETERS = 87
   Public Const ERROR_NO_MORE_ITEMS = 259

   Public Const KEY_QUERY_VALUE = &H1
   Public Const KEY_SET_VALUE = &H2
   Public Const KEY_ALL_ACCESS = &H3F

   Public Const REG_OPTION_NON_VOLATILE = 0

   Declare Function RegCloseKey Lib "advapi32.dll" _
   (ByVal hKey As Long) As Long
   Declare Function RegCreateKeyEx Lib "advapi32.dll" 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, ByVal lpSecurityAttributes _
   As Long, phkResult As Long, lpdwDisposition As Long) As Long
   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
   Declare Function RegQueryValueExString Lib "advapi32.dll" Alias _
   "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As _
   String, ByVal lpReserved As Long, lpType As Long, ByVal lpData _
   As String, lpcbData As Long) As Long
   Declare Function RegQueryValueExLong Lib "advapi32.dll" Alias _
   "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As _
   String, ByVal lpReserved As Long, lpType As Long, lpData As _
   Long, lpcbData As Long) As Long
   Declare Function RegQueryValueExNULL Lib "advapi32.dll" Alias _
   "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As _
   String, ByVal lpReserved As Long, lpType As Long, ByVal lpData _
   As Long, lpcbData As Long) As Long
   Declare Function RegSetValueExString Lib "advapi32.dll" Alias _
   "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, _
   ByVal Reserved As Long, ByVal dwType As Long, ByVal lpValue As _
   String, ByVal cbData As Long) As Long
   Declare Function RegSetValueExLong Lib "advapi32.dll" Alias _
   "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, _
   ByVal Reserved As Long, ByVal dwType As Long, lpValue As Long, _
   ByVal cbData As Long) As Long

Public Sub SaveReportAsPDF(strReportName As String, strPath As String)

    If Dir(&quot;C:\defaultprinter.bat&quot;) <> &quot;defaultprinter.bat&quot; Then
        Call AddDefaultPrinterBat
        sSleep (5000)
    End If

    Dim strOldDefault As String
    Dim RetVal As Variant
    
    strOldDefault = _
        Left(QueryKey(&quot;Software\Microsoft\Windows NT\CurrentVersion\Windows&quot;, &quot;Device&quot;), _
        InStr(1, QueryKey(&quot;Software\Microsoft\Windows NT\CurrentVersion\Windows&quot;, _
        &quot;Device&quot;), &quot;,&quot;) - 1)
    
    RetVal = Shell(&quot;C:\defaultprinter.bat &quot; & Chr(34) & &quot;Acrobat PDFWriter&quot; & Chr(34), vbMinimizedNoFocus)
    
    'SetKeyValue &quot;Software\Microsoft\Windows NT\CurrentVersion\Windows&quot;, &quot;Device&quot;, &quot;Acrobat PDFWriter,winspool,LPT1:&quot;, REG_SZ
    
    SetKeyValue &quot;Software\Adobe\Acrobat PDFWriter&quot;, &quot;PDFFilename&quot;, strPath, REG_SZ
    
    SetKeyValue &quot;Software\Adobe\Acrobat PDFWriter&quot;, &quot;bExecViewer&quot;, 0, REG_SZ

    sSleep (5000)

    DoCmd.OpenReport strReportName
    
    RetVal = Shell(&quot;C:\defaultprinter.bat &quot; & Chr(34) & strOldDefault & Chr(34), vbMinimizedNoFocus)
    
    'SetKeyValue &quot;Software\Microsoft\Windows NT\CurrentVersion\Windows&quot;, &quot;Device&quot;, strOldDefault, REG_SZ

End Sub

   Public Function SetValueEx(ByVal hKey As Long, sValueName As String, _
   lType As Long, vValue As Variant) As Long
       Dim lValue As Long
       Dim sValue As String
       Select Case lType
           Case REG_SZ
               sValue = vValue & Chr$(0)
               SetValueEx = RegSetValueExString(hKey, sValueName, 0&, _
                                              lType, sValue, Len(sValue))
           Case REG_DWORD
               lValue = vValue
               SetValueEx = RegSetValueExLong(hKey, sValueName, 0&, _
   lType, lValue, 4)
           End Select
   End Function

   Function QueryValueEx(ByVal lhKey As Long, ByVal szValueName As _
   String, vValue As Variant) As Long
       Dim cch As Long
       Dim lrc As Long
       Dim lType As Long
       Dim lValue As Long
       Dim sValue As String

       On Error GoTo QueryValueExError

       ' Determine the size and type of data to be read
       lrc = RegQueryValueExNULL(lhKey, szValueName, 0&, lType, 0&, cch)
       If lrc <> ERROR_NONE Then Error 5

       Select Case lType
           ' For strings
           Case REG_SZ:
               sValue = String(cch, 0)

   lrc = RegQueryValueExString(lhKey, szValueName, 0&, lType, _
   sValue, cch)
               If lrc = ERROR_NONE Then
                   vValue = Left$(sValue, cch - 1)
               Else
                   vValue = Empty
               End If
           ' For DWORDS
           Case REG_DWORD:
   lrc = RegQueryValueExLong(lhKey, szValueName, 0&, lType, _
   lValue, cch)
               If lrc = ERROR_NONE Then vValue = lValue
           Case Else
               'all other data types not supported
               lrc = -1
       End Select

QueryValueExExit:
       QueryValueEx = lrc
       Exit Function

QueryValueExError:
       Resume QueryValueExExit
   End Function

Public Function CreateNewKey(sNewKeyName As String, lPredefinedKey As Long)

    Dim hNewKey As Long         ' Handle to the new key
    Dim lRetVal As Long         ' Result of the RegCreateKeyEx function
    
    lRetVal = RegCreateKeyEx(lPredefinedKey, sNewKeyName, 0&, vbNullString, REG_OPTION_NON_VOLATILE, _
        KEY_ALL_ACCESS, 0&, hNewKey, lRetVal)
        
    RegCloseKey (hNewKey)

End Function

Public Function SetKeyValue(sKeyName As String, sValueName As String, vValueSetting As Variant, lValueType As Long)

    Dim lRetVal As Long         ' Result of the SetValueEx function
    Dim hKey As Long            ' Handle of open key
    
    ' Open the specified key
    lRetVal = RegOpenKeyEx(HKEY_CURRENT_USER, sKeyName, 0, KEY_SET_VALUE, hKey)
    
    lRetVal = SetValueEx(hKey, sValueName, lValueType, vValueSetting)
    
    RegCloseKey (hKey)

End Function

Public Function QueryKey(sKeyName As String, sValueName As String)

    Dim lRetVal As Long         ' Result of the API functions
    Dim hKey As Long            ' Handle of opened key
    Dim vValue As Variant       ' Setting of queried value
    
    lRetVal = RegOpenKeyEx(HKEY_CURRENT_USER, sKeyName, 0, KEY_QUERY_VALUE, hKey)
    
    lRetVal = QueryValueEx(hKey, sValueName, vValue)
    
    QueryKey = vValue
    
    RegCloseKey (hKey)

End Function

Public Sub AddDefaultPrinterBat()

    Dim hFile As Long
    
    hFile = FreeFile
    
    Open &quot;C:\defaultprinter.bat&quot; For Output Access Write As hFile
    
    Print #hFile, &quot;rundll32 printui.dll,PrintUIEntry /y /n %1&quot;
    
    Close hFile

End Sub
' #########################################################

***********************************************************

May 7, 2003

DCBBB deserves much of the credit for this addition.

This code was designed to save a file as a PDF file.  It was not designed to have the newly saved file open after it was created.  But this was happening for many people, including myself.  I knew it was a registry entry somewhere, but due to the technology department at my office, I was unable to reinstall Adobe and find the fiendish registry entry.  DCBBB did just that.  He discovered that the registry entry HKCU\Software\Adobe\Acrobat PDFWriter\bExecViewer had been changed from the default of 0 (do not show after saving) to 1 (show after saving).  So I have modified the code below to ALWAYS set the value of this registry key back to 0, thereby ensuring the file is always saved and never opened.

***********************************************************

It has been brought to my attention that this FAQ does not work on 9x products.  This seems to be related to a registry structure difference.  I have no access to a 9x platform, and therefore cannot correct this code to include 9x products.  If anyone wants to send me the correction, I will be happy to include it and give them credit for their work.

***********************************************************

It is not a difficult task to save a file in PDF Format.  This can be useful as it will keep all you formating, lines, boxes, and images.  With just a small set of code and a simple call procedure, you can have any and all of your reports saved into the PDF format.

This code will:
    - Determine you current default printer and save it as a variable
    - Set your default printer to the Adobe Writer
    - Save the file as PDF using the provided arguements
    - Set your default printer back to what it was prior to this process

Important Note: You must have Adobe Writer (which you have to purchase) in order for this to work.  Having Adobe Reader (Acrobat Reader), which can be downloaded from the Internet for free, will not work for you.

The first thing you must do is copy the following code into a module.  Name the module basPDFSaver.

'**********************************************************

   Public Const REG_SZ As Long = 1
   Public Const REG_DWORD As Long = 4

   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 ERROR_NONE = 0
   Public Const ERROR_BADDB = 1
   Public Const ERROR_BADKEY = 2
   Public Const ERROR_CANTOPEN = 3
   Public Const ERROR_CANTREAD = 4
   Public Const ERROR_CANTWRITE = 5
   Public Const ERROR_OUTOFMEMORY = 6
   Public Const ERROR_ARENA_TRASHED = 7
   Public Const ERROR_ACCESS_DENIED = 8
   Public Const ERROR_INVALID_PARAMETERS = 87
   Public Const ERROR_NO_MORE_ITEMS = 259

   Public Const KEY_QUERY_VALUE = &H1
   Public Const KEY_SET_VALUE = &H2
   Public Const KEY_ALL_ACCESS = &H3F

   Public Const REG_OPTION_NON_VOLATILE = 0

   Declare Function RegCloseKey Lib &quot;advapi32.dll&quot; _
   (ByVal hKey As Long) As Long
   Declare Function RegCreateKeyEx Lib &quot;advapi32.dll&quot; Alias _
   &quot;RegCreateKeyExA&quot; (ByVal hKey As Long, ByVal lpSubKey As String, _
   ByVal Reserved As Long, ByVal lpClass As String, ByVal dwOptions _
   As Long, ByVal samDesired As Long, ByVal lpSecurityAttributes _
   As Long, phkResult As Long, lpdwDisposition As Long) As Long
   Declare Function RegOpenKeyEx Lib &quot;advapi32.dll&quot; Alias _
   &quot;RegOpenKeyExA&quot; (ByVal hKey As Long, ByVal lpSubKey As String, _
   ByVal ulOptions As Long, ByVal samDesired As Long, phkResult As _
   Long) As Long
   Declare Function RegQueryValueExString Lib &quot;advapi32.dll&quot; Alias _
   &quot;RegQueryValueExA&quot; (ByVal hKey As Long, ByVal lpValueName As _
   String, ByVal lpReserved As Long, lpType As Long, ByVal lpData _
   As String, lpcbData As Long) As Long
   Declare Function RegQueryValueExLong Lib &quot;advapi32.dll&quot; Alias _
   &quot;RegQueryValueExA&quot; (ByVal hKey As Long, ByVal lpValueName As _
   String, ByVal lpReserved As Long, lpType As Long, lpData As _
   Long, lpcbData As Long) As Long
   Declare Function RegQueryValueExNULL Lib &quot;advapi32.dll&quot; Alias _
   &quot;RegQueryValueExA&quot; (ByVal hKey As Long, ByVal lpValueName As _
   String, ByVal lpReserved As Long, lpType As Long, ByVal lpData _
   As Long, lpcbData As Long) As Long
   Declare Function RegSetValueExString Lib &quot;advapi32.dll&quot; Alias _
   &quot;RegSetValueExA&quot; (ByVal hKey As Long, ByVal lpValueName As String, _
   ByVal Reserved As Long, ByVal dwType As Long, ByVal lpValue As _
   String, ByVal cbData As Long) As Long
   Declare Function RegSetValueExLong Lib &quot;advapi32.dll&quot; Alias _
   &quot;RegSetValueExA&quot; (ByVal hKey As Long, ByVal lpValueName As String, _
   ByVal Reserved As Long, ByVal dwType As Long, lpValue As Long, _
   ByVal cbData As Long) As Long

'**********************************************************

Public Sub SaveReportAsPDF(strReportName As String, strPath As String)

    Dim strOldDefault As String
    
    strOldDefault = QueryKey(&quot;Software\Microsoft\Windows NT\CurrentVersion\Windows&quot;, &quot;Device&quot;)
    
    SetKeyValue &quot;Software\Microsoft\Windows NT\CurrentVersion\Windows&quot;, &quot;Device&quot;, &quot;Acrobat PDFWriter&quot;, REG_SZ
    
    SetKeyValue &quot;Software\Adobe\Acrobat PDFWriter&quot;, &quot;PDFFilename&quot;, strPath, REG_SZ
    
    SetKeyValue &quot;Software\Adobe\Acrobat PDFWriter&quot;, &quot;bExecViewer&quot;, 0, REG_SZ

    DoCmd.OpenReport strReportName
    
    SetKeyValue &quot;Software\Microsoft\Windows NT\CurrentVersion\Windows&quot;, &quot;Device&quot;, strOldDefault, REG_SZ

End Sub

   Public Function SetValueEx(ByVal hKey As Long, sValueName As String, _
   lType As Long, vValue As Variant) As Long
       Dim lValue As Long
       Dim sValue As String
       Select Case lType
           Case REG_SZ
               sValue = vValue & Chr$(0)
               SetValueEx = RegSetValueExString(hKey, sValueName, 0&, _
                                              lType, sValue, Len(sValue))
           Case REG_DWORD
               lValue = vValue
               SetValueEx = RegSetValueExLong(hKey, sValueName, 0&, _
   lType, lValue, 4)
           End Select
   End Function

   Function QueryValueEx(ByVal lhKey As Long, ByVal szValueName As _
   String, vValue As Variant) As Long
       Dim cch As Long
       Dim lrc As Long
       Dim lType As Long
       Dim lValue As Long
       Dim sValue As String

       On Error GoTo QueryValueExError

       ' Determine the size and type of data to be read
       lrc = RegQueryValueExNULL(lhKey, szValueName, 0&, lType, 0&, cch)
       If lrc <> ERROR_NONE Then Error 5

       Select Case lType
           ' For strings
           Case REG_SZ:
               sValue = String(cch, 0)

   lrc = RegQueryValueExString(lhKey, szValueName, 0&, lType, _
   sValue, cch)
               If lrc = ERROR_NONE Then
                   vValue = Left$(sValue, cch - 1)
               Else
                   vValue = Empty
               End If
           ' For DWORDS
           Case REG_DWORD:
   lrc = RegQueryValueExLong(lhKey, szValueName, 0&, lType, _
   lValue, cch)
               If lrc = ERROR_NONE Then vValue = lValue
           Case Else
               'all other data types not supported
               lrc = -1
       End Select

QueryValueExExit:
       QueryValueEx = lrc
       Exit Function

QueryValueExError:
       Resume QueryValueExExit
   End Function

Public Function CreateNewKey(sNewKeyName As String, lPredefinedKey As Long)

    Dim hNewKey As Long         ' Handle to the new key
    Dim lRetVal As Long         ' Result of the RegCreateKeyEx function
    
    lRetVal = RegCreateKeyEx(lPredefinedKey, sNewKeyName, 0&, vbNullString, REG_OPTION_NON_VOLATILE, _
        KEY_ALL_ACCESS, 0&, hNewKey, lRetVal)
        
    RegCloseKey (hNewKey)

End Function

Public Function SetKeyValue(sKeyName As String, sValueName As String, vValueSetting As Variant, lValueType As Long)

    Dim lRetVal As Long         ' Result of the SetValueEx function
    Dim hKey As Long            ' Handle of open key
    
    ' Open the specified key
    lRetVal = RegOpenKeyEx(HKEY_CURRENT_USER, sKeyName, 0, KEY_SET_VALUE, hKey)
    
    lRetVal = SetValueEx(hKey, sValueName, lValueType, vValueSetting)
    
    RegCloseKey (hKey)

End Function

Public Function QueryKey(sKeyName As String, sValueName As String)

    Dim lRetVal As Long         ' Result of the API functions
    Dim hKey As Long            ' Handle of opened key
    Dim vValue As Variant       ' Setting of queried value
    
    lRetVal = RegOpenKeyEx(HKEY_CURRENT_USER, sKeyName, 0, KEY_QUERY_VALUE, hKey)
    
    lRetVal = QueryValueEx(hKey, sValueName, vValue)
    
    QueryKey = vValue
    
    RegCloseKey (hKey)

End Function

'**********************************************************

That's all the code there is.....Now the setup.

Anywhere you want to save a report, you will use:

    Call SaveReportAsPDF(&quot;Name of Report&quot;, &quot;Save Location&quot;)

The Name of Report is simply the name of the report in the database...if you use standard naming conventions, it will be something like rptInvoiceDetails, rptSummary, etc.

The Save Location is the full path and name of this output file....I would use something like C:\My Documents\Invoice Details.pdf, D:\New Database\Summary.pdf, etc

As a Final Note:  Please be aware this code has NO ERROR CHECKING or graceful crash protection.....that is up to you to provde.  There are no bells or whistles, such as asking if you really want to do this etc.....

And that's it.  You are now saving your files in PDF format.  

Back to Microsoft: Access Reports FAQ Index
Back to Microsoft: Access Reports Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close