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:
Expand|Select|Wrap|Line Numbers
- Software\Adobe\Acrobat Distiller\8.0\AdobePDFOutputFolder
Expand|Select|Wrap|Line Numbers
- Name, Type, Data
- (Default), REG_DWORD, 0x0000004(4)
- 2, REG_SZ, C:Documents and Settings\Desktop
- 3, REG_SZ, S:
- 4, REG_SZ, Z:
Expand|Select|Wrap|Line Numbers
- ' ### Set the PDF Output folder ###
- SetKeyValue "Software\Adobe\Acrobat Distiller\8.0\AdobePDFOutputFolder", "4", strPath, REG_SZ
Expand|Select|Wrap|Line Numbers
- ' ### Set the PDF Output folder ###
- SetKeyValue "Software\Adobe\Acrobat Distiller\8.0\AdobePDFOutputFolder", "5", strPath, REG_SZ
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?
*** Here is the complete code: ***
Expand|Select|Wrap|Line Numbers
- 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
- ' --- This is the part of the code that is not ideal ---
- ' ### Set the PDF Output folder ###
- SetKeyValue "Software\Adobe\Acrobat Distiller\8.0\AdobePDFOutputFolder", "4", 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
- '**********************************************************