Page 1 of 5 123 ... LastLast
Results 1 to 15 of 61
  1. #1
    2 Star Lounger
    Join Date
    May 2003
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Print report to Acrobat PDF (AccessXP)

    I am trying to write some code to print an Access report to an adobe acrobat file. How do you specify the printer in Access VBA?

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Print report to Acrobat PDF (AccessXP)

    A simple alternative to writing VBA code to select a printer is to format the report for a specific printer.
    Open the report in Design view and go to page setup.

    If you sometimes want to print and sometimes want to convert to pdf, you can have two copies of the report, with one set for the default printer and one for acrobat.
    Attached Images Attached Images
    Regards
    John



  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Print report to Acrobat PDF (AccessXP)

    Look up the Printer object and property and the Printers collection in the online help, and look up the name of the "printer" for Acrobat (I don't have that myself, so I don't know the name)

    Examples:

    Me.Printer = Application.Printers("Acrobat")

    where Me refers to the report, if the code is in the report module, or

    Reports!MyReport.Printer = Application.Printers("Acrobat")

    where MyReport is an open report.

  4. #4
    2 Star Lounger
    Join Date
    May 2003
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print report to Acrobat PDF (AccessXP)

    Thanks Hans. That worked. Now I have one more problem. After setting the printer, I cannot seem to save the report to the PDF file.

    a) If I use Docmd.OutputTo, I can only save it in one of the Microsoft formats (eg RTF, etc..) , which corrupts the PDF file.
    [img]/forums/images/smilies/cool.gif[/img] If I use Docmd.Printout, this doesn't let me specify the destination.

    What is the best way to print to an acrobat file, in a specific location?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Print report to Acrobat PDF (AccessXP)

    I believe it involves setting the filename in a Registry entry before printing the report (using PrintOut, not OutputTo). You can copy the code below into a standard module, then use

    SetPDFFileName "C:AccessTest.pdf"

    You might also use the PDF and Mail library for Access from ACG Soft. It's not free, though.

    <img src=/w3timages/blueline.gif width=33% height=2>

    Option Compare Database
    Option Explicit

    Private Declare Function RegOpenKeyA Lib "advapi32.dll" _
    (ByVal HKEY As Long, ByVal sSubKey As String, ByRef hkeyResult As Long) As Long
    Private Declare Function RegCloseKey Lib "advapi32.dll" _
    (ByVal HKEY As Long) As Long
    Private Declare Function RegSetValueExA Lib "advapi32.dll" _
    (ByVal HKEY As Long, ByVal sValueName As String, _
    ByVal dwReserved As Long, ByVal dwType As Long, _
    ByVal sValue As String, ByVal dwSize As Long) As Long
    Private Declare Function RegCreateKeyA Lib "advapi32.dll" _
    (ByVal HKEY As Long, ByVal sSubKey As String, ByRef hkeyResult As Long) As Long

    Public Enum HKEY_Enum
    HKEY_CLASSES_ROOT = &H80000000
    HKEY_CURRENT_USER = &H80000001
    HKEY_LOCAL_MACHINE = &H80000002
    HKEY_USERS = &H80000003
    HKEY_CURRENT_CONFIG = &H80000004
    HKEY_DYN_DATA = &H80000005
    End Enum

    Public Function SetRegValue(ByVal HKEY As HKEY_Enum, ByVal Path As String, _
    ByVal Entry As String, ByVal Value As String) As Boolean

    ' Set text value in registry
    Dim lngKey As Long
    On Error GoTo Proc_Error

    If RegOpenKeyA(HKEY, Path, lngKey) <> 0 Then
    RegCreateKeyA HKEY, Path, lngKey
    End If
    SetRegValue = (RegSetValueExA(lngKey, Entry, 0&, 1&, Value, Len(Value) + 1) = 0)
    RegCloseKey lngKey
    Exit Function

    Proc_Error:
    MsgBox Err.Description, vbCritical
    End Function

    Public Sub SetPDFFileName(sPath As String)
    SetRegValue HKEY_CURRENT_USER, "SoftwareAdobeAcrobat PDFWriter", _
    "PDFFileName", sPath
    End Sub

    <img src=/w3timages/blueline.gif width=33% height=2>

  6. #6
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Chapel Hill, North Carolina, USA
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print report to Acrobat PDF (AccessXP)

    Hans,

    Thanks for the advice - this is exactly what I am looking for, except.....I don't have a Printers Collection (I've checked the object browser and online help). Do you know which references I need to include to get this collection?

    Thanks again!

    Larry

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Print report to Acrobat PDF (AccessXP)

    There is a utility floating around called "FreePDF", which supposedly allows you to do this at no cost. You can do a Google search to find it, and you might also refine the search by looking for Aladdin Enterprises.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Print report to Acrobat PDF (AccessXP)

    The original question was for Access 2002; the Printers collection was introduced in that version. If you are using Access 2000 or earlier, the Printers collection is not available.

  9. #9
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Chapel Hill, North Carolina, USA
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print report to Acrobat PDF (AccessXP)

    Thanks - do you know of an easy way to accomplish this in Access 2000? I've got a "Free PDF" driver which I want to use (per another post) and want to print reports to disk using it on demand.

    Thanks again!

    Larry

  10. #10
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Chapel Hill, North Carolina, USA
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print report to Acrobat PDF (AccessXP)

    Mark,

    Thanks - I've got the "CutePDF Printer" driver that works great - now I'm trying to select printers in VBA under Access 2000.

    Larry

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Print report to Acrobat PDF (AccessXP)

    As far as I know, setting the printer in Access 2000 is very ugly - see Controlling Your Printer. This chapter from the Access Developer's Handbook is for Access 95, but it applies to Access 97 and 2000 as well.

    Perhaps someone else has a simpler solution <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

  12. #12
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print report to Acrobat PDF (AccessXP)

    I have tried the access PDF report code to produce the PDF reports automatically but am having no luck , could you assist?

    Shall l send you the code?

    Any help would be appreciated.

    Justin.

  13. #13
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print report to Acrobat PDF (AccessXP)

    Hans being away, post the code here and someone will have a look at it.
    Francois

  14. #14
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print report to Acrobat PDF (AccessXP)

    Francois

    see attached code.

    Justin

  15. #15
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print report to Acrobat PDF (AccessXP)

    What are you using to create pdf's ? FreePDF ? The site seems to bee off-line and I can't get it.
    In the code you use ExportPDF "......" . Is these a function you write ?
    Francois

Page 1 of 5 123 ... LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •