We help IT Professionals succeed at work.

Problem auto-generating PDF files from Access VBA specifying a specific directory

777 Views
Last Modified: 2008-11-13
Here is some code that I have adapted slightly. It allows a report to be printed to a specific location.

It works by calling SaveReportAsPDF and specifying the access report name, and the root directory to which the filename should be saved. The routine adjusts registry values to achieve its aim.

Now, it works, but not quite as I would like it. I originally thought that I would be able to adjust a registry key (such as AdobePDFOutputFolder) and it would then be a simple case of printing. That didn't seem to work, and so I played around with various options until I came up with the following solution.

When I looked at the registry key at the following registry route:

   Software\Adobe\Acrobat Distiller\8.0\AdobePDFOutputFolder

I saw the following entries:

   Name, Type, Data
   (Default), REG_DWORD, 0x0000004(4)
   2, REG_SZ, C:Documents and Settings\Desktop
   3, REG_SZ, S:
   4, REG_SZ, Z:

At the time, printing to Adobe PDF printed automatically to the key named at "4" (the Z: drive). I therefore realized that by adjusting the "4" key, I could change the location of the printing. Hence the line in the code below:

   1.' ### Set the PDF Output folder ###
   2.   SetKeyValue "Software\Adobe\Acrobat Distiller\8.0\AdobePDFOutputFolder", "4", strPath, REG_SZ

This was fine, but if I then manually changed the Adobe PDF print directory (via the Printers and Faxes folder, for instance), a 5th registry was then added. This then became the "live" Adobe printer key. For the code to work, I then needed to change the line in the code to:

   1. ' ### Set the PDF Output folder ###
   2. SetKeyValue "Software\Adobe\Acrobat Distiller\8.0\AdobePDFOutputFolder", "5", strPath, REG_SZ

reflecting the new entry in the registry, which was the "live" entry.

So...Is there a way to adjust the registry key in a clean way, so that the PDF output folder is changed directly, and is not affected by manual changes?
Option Compare Database
Option Explicit
 
'**********************************************************
 
   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)
 
    Dim strOldDefault As String
    
    ' ### Set the default printer ###
    strOldDefault = QueryKey("Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device")
    
    ' ### Change the default printer to Adobe ###
    SetKeyValue "Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device", "Adobe PDF", REG_SZ
        
    ' ### Set the PDF Output folder ###
    SetKeyValue "Software\Adobe\Acrobat Distiller\8.0\AdobePDFOutputFolder", "5", strPath, REG_SZ
 
    ' ### Print the Report ###
    DoCmd.OpenReport strReportName
    
    ' ### Set the Printer back to the default ###
    SetKeyValue "Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device", 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
 
'**********************************************************

Open in new window

Comment
Watch Question

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Just an FYI ... I'm using this in a couple of apps - and it's simple and works great!

http://www.lebans.com/reporttopdf.htm

mx
CERTIFIED EXPERT

Commented:
Have you read the documentation that comes with the Acrobat SDK? There is a supported and documented way to set the output directory. You can find it in the "Acrobat Distiller API Reference" at http://www.adobe.com/devnet/acrobat/ (click on the "Documentation" tab). If I remember correctly, it's documented around page 15.

Author

Commented:
Thanks for your replies...

DatabaseMX: I had seen that and can see that it works, but was hoping for a solution that didn't involve extra files.  Of course, it could be argued that modifying the registry settings is less clean a solution than the one you suggest.

khkremer: I have read the API Reference, but I can't see how to change the registry setting.  It suggests:

[HKEY_CURRENT_USER\Software\Adobe\Acrobat Distiller\PrinterJobControl]
"C:\Program Files\Windows NT\Accessories\wordpad.exe" =
"c:\MyPDFoutputFileName.pdf"

But I'm not sure how the two entries fit into the -- Name | Type | Data -- format of the registry.

I also see that the PrinterJobControl has multiple entries in the same way that AdobePDFOutputFolder did.  Any suggestions?
CERTIFIED EXPERT

Commented:
Look at the last comment in this thread:
http://www.acrobatusers.com/forums/aucbb/viewtopic.php?id=1320

It explains what data type and how to add the key.
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
"I had seen that and can see that it works, but was hoping for a solution that didn't involve extra files."

Note that the two Lebans DLL's *do not* need to be(or should the be) registered.  They just need to be in the same directory as the MDB.

mx

Author

Commented:
khkremer - I followed your instructions but when I tried printing from Wordpad.exe it printed to the current registry set in AdobePDFOutputFolder.
CERTIFIED EXPERT

Commented:
It worked for me. Is your WordPad application stored in the location you've used as the registry value name?

Author

Commented:
It is indeed...but I figured out the problem.

In Printing Preferences the Adobe PDF Output has to be set to "Prompt for Adobe PDF filename", rather than a specific directory.

Thanks for both of your inputs...I think I now have two very good solutions.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.