473,839 Members | 1,508 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Access VBA - automatically printing reports to PDF at specific location

119 New Member
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 AdobePDFOutputF older) 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
  1. Software\Adobe\Acrobat Distiller\8.0\AdobePDFOutputFolder
I saw the following entries:

Expand|Select|Wrap|Line Numbers
  1. Name, Type, Data
  2. (Default), REG_DWORD, 0x0000004(4)
  3. 2, REG_SZ, C:Documents and Settings\Desktop
  4. 3, REG_SZ, S:
  5. 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:

Expand|Select|Wrap|Line Numbers
  1.     ' ### Set the PDF Output folder ###
  2.     SetKeyValue "Software\Adobe\Acrobat Distiller\8.0\AdobePDFOutputFolder", "4", strPath, REG_SZ
  3.  
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:

Expand|Select|Wrap|Line Numbers
  1.     ' ### Set the PDF Output folder ###
  2.     SetKeyValue "Software\Adobe\Acrobat Distiller\8.0\AdobePDFOutputFolder", "5", strPath, REG_SZ
  3.  
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?



*** Here is the complete code: ***

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. '**********************************************************
  5.  
  6.    Public Const REG_SZ As Long = 1
  7.    Public Const REG_DWORD As Long = 4
  8.  
  9.    Public Const HKEY_CLASSES_ROOT = &H80000000
  10.    Public Const HKEY_CURRENT_USER = &H80000001
  11.    Public Const HKEY_LOCAL_MACHINE = &H80000002
  12.    Public Const HKEY_USERS = &H80000003
  13.  
  14.    Public Const ERROR_NONE = 0
  15.    Public Const ERROR_BADDB = 1
  16.    Public Const ERROR_BADKEY = 2
  17.    Public Const ERROR_CANTOPEN = 3
  18.    Public Const ERROR_CANTREAD = 4
  19.    Public Const ERROR_CANTWRITE = 5
  20.    Public Const ERROR_OUTOFMEMORY = 6
  21.    Public Const ERROR_ARENA_TRASHED = 7
  22.    Public Const ERROR_ACCESS_DENIED = 8
  23.    Public Const ERROR_INVALID_PARAMETERS = 87
  24.    Public Const ERROR_NO_MORE_ITEMS = 259
  25.  
  26.    Public Const KEY_QUERY_VALUE = &H1
  27.    Public Const KEY_SET_VALUE = &H2
  28.    Public Const KEY_ALL_ACCESS = &H3F
  29.  
  30.    Public Const REG_OPTION_NON_VOLATILE = 0
  31.  
  32.    Declare Function RegCloseKey Lib "advapi32.dll" _
  33.    (ByVal hKey As Long) As Long
  34.    Declare Function RegCreateKeyEx Lib "advapi32.dll" Alias _
  35.    "RegCreateKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, _
  36.    ByVal Reserved As Long, ByVal lpClass As String, ByVal dwOptions _
  37.    As Long, ByVal samDesired As Long, ByVal lpSecurityAttributes _
  38.    As Long, phkResult As Long, lpdwDisposition As Long) As Long
  39.    Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias _
  40.    "RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, _
  41.    ByVal ulOptions As Long, ByVal samDesired As Long, phkResult As _
  42.    Long) As Long
  43.    Declare Function RegQueryValueExString Lib "advapi32.dll" Alias _
  44.    "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As _
  45.    String, ByVal lpReserved As Long, lpType As Long, ByVal lpData _
  46.    As String, lpcbData As Long) As Long
  47.    Declare Function RegQueryValueExLong Lib "advapi32.dll" Alias _
  48.    "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As _
  49.    String, ByVal lpReserved As Long, lpType As Long, lpData As _
  50.    Long, lpcbData As Long) As Long
  51.    Declare Function RegQueryValueExNULL Lib "advapi32.dll" Alias _
  52.    "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As _
  53.    String, ByVal lpReserved As Long, lpType As Long, ByVal lpData _
  54.    As Long, lpcbData As Long) As Long
  55.    Declare Function RegSetValueExString Lib "advapi32.dll" Alias _
  56.    "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, _
  57.    ByVal Reserved As Long, ByVal dwType As Long, ByVal lpValue As _
  58.    String, ByVal cbData As Long) As Long
  59.    Declare Function RegSetValueExLong Lib "advapi32.dll" Alias _
  60.    "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, _
  61.    ByVal Reserved As Long, ByVal dwType As Long, lpValue As Long, _
  62.    ByVal cbData As Long) As Long
  63.  
  64. '**********************************************************
  65.  
  66. Public Sub SaveReportAsPDF(strReportName As String, strPath As String)
  67.  
  68.     Dim strOldDefault As String
  69.  
  70.     ' ### Set the default printer ###
  71.     strOldDefault = QueryKey("Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device")
  72.  
  73.     ' ### Change the default printer to Adobe ###
  74.     SetKeyValue "Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device", "Adobe PDF", REG_SZ
  75.  
  76.     ' --- This is the part of the code that is not ideal ---
  77.     ' ### Set the PDF Output folder ###
  78.     SetKeyValue "Software\Adobe\Acrobat Distiller\8.0\AdobePDFOutputFolder", "4", strPath, REG_SZ
  79.  
  80.     ' ### Print the Report ###
  81.     DoCmd.OpenReport strReportName
  82.  
  83.     ' ### Set the Printer back to the default ###
  84.     SetKeyValue "Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device", strOldDefault, REG_SZ
  85.  
  86. End Sub
  87.  
  88.    Public Function SetValueEx(ByVal hKey As Long, sValueName As String, _
  89.    lType As Long, vValue As Variant) As Long
  90.        Dim lValue As Long
  91.        Dim sValue As String
  92.        Select Case lType
  93.            Case REG_SZ
  94.                sValue = vValue & Chr$(0)
  95.                SetValueEx = RegSetValueExString(hKey, sValueName, 0&, _
  96.                                               lType, sValue, Len(sValue))
  97.            Case REG_DWORD
  98.                lValue = vValue
  99.                SetValueEx = RegSetValueExLong(hKey, sValueName, 0&, _
  100.    lType, lValue, 4)
  101.            End Select
  102.    End Function
  103.  
  104.    Function QueryValueEx(ByVal lhKey As Long, ByVal szValueName As _
  105.    String, vValue As Variant) As Long
  106.        Dim cch As Long
  107.        Dim lrc As Long
  108.        Dim lType As Long
  109.        Dim lValue As Long
  110.        Dim sValue As String
  111.  
  112.        On Error GoTo QueryValueExError
  113.  
  114.        ' Determine the size and type of data to be read
  115.        lrc = RegQueryValueExNULL(lhKey, szValueName, 0&, lType, 0&, cch)
  116.        If lrc <> ERROR_NONE Then Error 5
  117.  
  118.        Select Case lType
  119.            ' For strings
  120.            Case REG_SZ:
  121.                sValue = String(cch, 0)
  122.  
  123.    lrc = RegQueryValueExString(lhKey, szValueName, 0&, lType, _
  124.    sValue, cch)
  125.                If lrc = ERROR_NONE Then
  126.                    vValue = Left$(sValue, cch - 1)
  127.                Else
  128.                    vValue = Empty
  129.                End If
  130.            ' For DWORDS
  131.            Case REG_DWORD:
  132.    lrc = RegQueryValueExLong(lhKey, szValueName, 0&, lType, _
  133.    lValue, cch)
  134.                If lrc = ERROR_NONE Then vValue = lValue
  135.            Case Else
  136.                'all other data types not supported
  137.                lrc = -1
  138.        End Select
  139.  
  140. QueryValueExExit:
  141.        QueryValueEx = lrc
  142.        Exit Function
  143.  
  144. QueryValueExError:
  145.        Resume QueryValueExExit
  146.    End Function
  147.  
  148. Public Function CreateNewKey(sNewKeyName As String, lPredefinedKey As Long)
  149.  
  150.     Dim hNewKey As Long         ' Handle to the new key
  151.     Dim lRetVal As Long         ' Result of the RegCreateKeyEx function
  152.  
  153.     lRetVal = RegCreateKeyEx(lPredefinedKey, sNewKeyName, 0&, vbNullString, REG_OPTION_NON_VOLATILE, _
  154.         KEY_ALL_ACCESS, 0&, hNewKey, lRetVal)
  155.  
  156.     RegCloseKey (hNewKey)
  157.  
  158. End Function
  159.  
  160. Public Function SetKeyValue(sKeyName As String, sValueName As String, vValueSetting As Variant, lValueType As Long)
  161.  
  162.     Dim lRetVal As Long         ' Result of the SetValueEx function
  163.     Dim hKey As Long            ' Handle of open key
  164.  
  165.     ' Open the specified key
  166.     lRetVal = RegOpenKeyEx(HKEY_CURRENT_USER, sKeyName, 0, KEY_SET_VALUE, hKey)
  167.  
  168.     lRetVal = SetValueEx(hKey, sValueName, lValueType, vValueSetting)
  169.  
  170.     RegCloseKey (hKey)
  171.  
  172. End Function
  173.  
  174. Public Function QueryKey(sKeyName As String, sValueName As String)
  175.  
  176.     Dim lRetVal As Long         ' Result of the API functions
  177.     Dim hKey As Long            ' Handle of opened key
  178.     Dim vValue As Variant       ' Setting of queried value
  179.  
  180.     lRetVal = RegOpenKeyEx(HKEY_CURRENT_USER, sKeyName, 0, KEY_QUERY_VALUE, hKey)
  181.  
  182.     lRetVal = QueryValueEx(hKey, sValueName, vValue)
  183.  
  184.     QueryKey = vValue
  185.  
  186.     RegCloseKey (hKey)
  187.  
  188. End Function
  189.  
  190. '**********************************************************
  191.  
Jul 23 '08 #1
1 6259
billelev
119 New Member
This is the modified code in answer to my problem.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Sub SaveReportAsPDF(strReportName As String, strPath As String)
  3.  
  4.     Dim strOldDefault As String
  5.  
  6.     ' ### Set the default printer ###
  7.     strOldDefault = QueryKey("Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device")
  8.  
  9.     ' ### Change the default printer to Adobe ###
  10.     SetKeyValue "Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device", "Adobe PDF", REG_SZ
  11.  
  12.     ' ### Set the PDF Output folder ### HKEY_CURRENT_USER
  13.     SetKeyValue "Software\Adobe\Acrobat Distiller\PrinterJobControl", "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE", strPath, REG_SZ
  14.  
  15.     ' ### Print the Report ###
  16.     DoCmd.OpenReport strReportName
  17.  
  18.     ' ### Set the Printer back to the default ###
  19.     SetKeyValue "Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device", strOldDefault, REG_SZ
  20.  
  21. End Sub
  22.  
  23.  
Jul 25 '08 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

1
2775
by: chiry | last post by:
Is there a way to make Element.addContent add the content at a specific location? I need to update an existing jdom object in memory, by adding/removing some elements. problem is, addContent() always add at the end, and as a result the resulting XML object doesn't validate against the XSD file. Is there a way to add an element at a specific location?
0
1315
by: Zentil | last post by:
While printing reports Spooling of print document size get increased into larger size ie the document size is 260kb but on spooling it increased to 268mb. but this is happening in some systems only. if anyone help on this it will be useful to me
0
1241
by: Mibbetson | last post by:
I am having trouble with Access 2002 randomly printing reports with all the information smashed up in the upper left corner of the paper. All the info is there, it is just really small. If a user reprints the report it usually comes out fine. It might not happen for a few days, then maybe once or twice, then not again for a while. I have updated drivers for the printers, with no help. Any ideas?
2
5637
by: Susan Bricker | last post by:
Greetings! Is it possible to open a form in a specific location on the screen? I have three cascading forms ... fill one in and then open another ... fill that one in and open another. I want to keep each previous form open and position the forms such that the user can see all three (overlapping somewhat). Also, I want to make them modal (can't get focus to the previous form until the current form is closed). Is this possible? Is...
1
1883
by: Linda Wienholt | last post by:
Hi I am trying to link to a specific location on a page using a named anchor. I also have some parameters attached to the query string. I am having problems getting the page to display the correct portion of the page when it loads I have a few named anchors on the page using the following format <a name="branding"></a>Then some text here. In the URL I have http://mysite.com.au/default.aspx?id=1#branding when I load this page it does...
1
1427
by: Li Pang | last post by:
Hi, I receive daily an email with a attached file from my Outlook inbox. This file must be saved into a specific location manually. I made an app to automate this process. My app can read the emails from the inbox folder, I can catch all properties of the emails. I want to know how to save the attached file into a specific location.
0
2740
by: neoteny2 | last post by:
I need MS Access to automatically create reports/subreports based on specific criteria. I am building a database in Access 2003 with different locations/sites. I have the "sites" table created containing site info, including an empty field for "date". I also have a report format already created displaying the site info. I need Access to ask the user for a date, calculate three additional dates using the entered date (adding or subtracting...
0
1230
by: sushilgajbhiye | last post by:
Hi, I am new in C# programming. I have saved some data to the database and after i retrieve it, i want to print it to the printer. I want to print that text to a specific location i.e. something like (x,y) location. Can anyone tell me, how to do this? Sushil
3
7506
by: Birky | last post by:
I have a report which I am opening from a command button within a form. I am using the “DoCmd.OpenReport stDocName” command to open the report but it is automatically printing instead of popping the report to the screen. How can I stop it from printing and just have the output sent to the screen? Any help would be greatly appreciated. Thanks Birky
0
9856
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9698
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10913
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10592
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10656
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7022
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5871
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4495
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3136
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.