• Print report to Acrobat PDF (AccessXP)

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Print report to Acrobat PDF (AccessXP)

    Author
    Topic
    #397977

    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?

    Viewing 9 reply threads
    Author
    Replies
    • #757415

      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.

    • #757416

      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.

    • #757417

      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.

      • #757425

        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.
        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?

        • #757474

          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.

          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

        • #757475

          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.

          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

      • #757426

        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.
        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?

      • #813442

        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

        • #813534

          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.

          • #813541

            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

            • #813601

              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 crossfingers

            • #813602

              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 crossfingers

          • #813542

            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

        • #813535

          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.

      • #813443

        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

      • #871275

        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.

        • #871298

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

        • #871299

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

      • #871276

        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.

    • #757418

      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.

    • #813464

      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.

      • #813545

        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

      • #813546

        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

    • #813465

      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.

    • #871306

      Francois

      see attached code.

      Justin

      • #871364

        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 ?

        • #871372

          I am using Adobe PDF writer.
          I got the function ExportPDF from the coding provided.

          Justin.

          • #871376

            Can you post the ExportPDF function ?

            • #871378

              see attached code.

              Justin

            • #871442

              The attached code is the same as in the previous post.
              I have been playing the last hours with programming in the registry.
              So far I can change the printer to print to the pdf distiller printer but until now I can’t find how to avoid the dialog to ask the file name.
              If I find something, I’ll post back

            • #871761

              It would be really appreciated if you could sort this out for me , as it would save a lot of time.

              Justin.

            • #871767

              For the moment, if using windows xp, I can set the printer to print to pdf in code.
              In Acrobat distiller you have an option to avoid to ask where to save. If you use this, the report is saved as a pdf file with the name of the report. sample: ReportName.pdf.
              But this is saved on the desktop and I’m looking for a method to save it in a directory of your choice. That’s the last problem.

            • #871769

              I have found the option in Adobe Acrobat for not prompting for filenames.
              Would you be able to send me the completed code so l can try it out.

              Thanks for your help.

              Thanks
              Justin.

            • #871771

              I clean it up a little and send post it in a moment.

            • #871773

              Many thanks, l assume you’ve managed to work it so that the .pdf file can go into any directory?

            • #871774

              Many thanks, l assume you’ve managed to work it so that the .pdf file can go into any directory?

            • #871772

              I clean it up a little and send post it in a moment.

            • #871781

              This program is tested on a win xp and I don’t know if it will run on other windows version.
              In the attachment you’ll find to txt files.
              Extract them and copy the content of each of the files in a different new module. Save the modules but don’t use the name RunReportAsPDF as this is the name of the sub and a sub may not have the same name as a module.
              For each report you want to run, use an instruction as follow :
              Call RunReportAsPDF(“ReportName”,”PrinterName”)
              Replace ReportName with the name of your report.
              Replace Printer Name with the printer name you find in the registry when the Adobe Distiller is set as default.
              To find this out :
              Set the adobe distiller as default printer.
              Go to Start , Run and type regedit.
              Navigate to the key HKEY_CURRENT_USERSoftwareMicrosoftWIndows NTCurrentVersionWindows
              Look in the right pane form the key Device and note the string. (On my pc it’s : Acrobat Distiller,winspool,Ne01: )
              This is the text you have to use as PrinterName.
              Close the regedit.
              Set you usual printer as default back.

              Let me know how it turns out.

            • #871789

              I have found the printer name from the steps you mentioned below.

              What l am unsure about is to how run the code , do l need a command button and use the “On Click” event.
              see attachment.

              Justin.

            • #871791

              Yes, use a command button and in the click event use code like this :
              Private Sub cmdPrintPDF_Click()
              Call RunReportAsPDF(“Report1”, “Acrobat Distiller,winspool,Ne01:”)
              Call RunReportAsPDF(“Report2”, “Acrobat Distiller,winspool,Ne01:”)
              Call RunReportAsPDF(“Report3”, “Acrobat Distiller,winspool,Ne01:”)
              Call RunReportAsPDF(“Report4”, “Acrobat Distiller,winspool,Ne01:”)
              Call RunReportAsPDF(“Report5”, “Acrobat Distiller,winspool,Ne01:”)
              End Sub

              The reports will be on the desktop. I can’t find the method to put them in a specific directory for the moment.

            • #871792

              Yes, use a command button and in the click event use code like this :
              Private Sub cmdPrintPDF_Click()
              Call RunReportAsPDF(“Report1”, “Acrobat Distiller,winspool,Ne01:”)
              Call RunReportAsPDF(“Report2”, “Acrobat Distiller,winspool,Ne01:”)
              Call RunReportAsPDF(“Report3”, “Acrobat Distiller,winspool,Ne01:”)
              Call RunReportAsPDF(“Report4”, “Acrobat Distiller,winspool,Ne01:”)
              Call RunReportAsPDF(“Report5”, “Acrobat Distiller,winspool,Ne01:”)
              End Sub

              The reports will be on the desktop. I can’t find the method to put them in a specific directory for the moment.

            • #871790

              I have found the printer name from the steps you mentioned below.

              What l am unsure about is to how run the code , do l need a command button and use the “On Click” event.
              see attachment.

              Justin.

            • #871811

              Does it matter what names l call the modules?

              Is module1 and module 2 ok?

              Justin.

            • #871813

              That’s ok

            • #871835

              I have run the code but appear to get an error.

              I have checked for End Sub and it appears to be there , not to sure what is causing the error.
              see attachment.

              Justin.

            • #871837

              You have to remove the line
              Private Sub cmdPrintPDF_click
              and one of the two
              End Sub

            • #871845

              I have done this and get an error.
              see attachment.

              Justin.

            • #871854

              Check the line :
              Call RunReportAsPDF(“CTF……….)
              I can’t see the full line as the message box cover it.
              Is the name of the report correct ? Did the report exist ? Are the quotes on the right place ?

            • #871866

              The report name is fine.
              Don’t know what you mean by “Did the report exist?”
              Can you tell me exactly where the quotes should be?

              Justin.

            • #871870

              The report CFT…. should exist in the database window on the tab reports.
              The quotes should be on the same place as in the other lines.
              Call RunReportAsPDF(“CFT…..”, “Acrobat Distiller,winspool,Ne01:”)
              Around the report name and around the printer name.
              Did the report name contain quotes ? if so rename it in the database window and in the code line with a name without quotes.

            • #871874

              The quotes are fine , see attachment.

              I don’t understand what you are impying when you mean did the report name contain quotes?
              I also don’t know what you mean when you say “The report CFT…. should exist in the database window on the tab reports”
              Can you explain these in a bit more details,maybe include a screenshot so l can see what you mean.

              Justin.

            • #871884

              Look at the database window (see attachment)
              Is the CFT Open Action Report in the list of reports ? Is the spelling exact the same as in the code ? Check for double spaces. See attachment one is with single space and the other with double space.
              If this don’t work, select the report in the database window and press F2, then CTRL-C, go to the code window and put the cursor on the line for that report. Erase the report name and put the cursor between the two double quotes (“|”) end press CTRL-V

            • #871922

              The reports are there ok. They need to be different to the . PDF report name.
              I don’t know what you mean by
              “If this don’t work, select the report in the database window and press F2, then CTRL-C, go to the code window and put the cursor on the line for that report. Erase the report name and put the cursor between the two double quotes (“|”) end press CTRL-V”
              I tried this but it did not work , see attachment.

              Justin

            • #871934

              That was to use a copy and past to copy the name of the report in the code.
              In the lines calling the procedure where I told you to put the report name you should enter the exactly same name as appear in the database window, surrounded by double quotes.
              From your attachment I see a report named Install CFT
              If you want a pdf file from that report use the line :
              Call RunReportAsPDF(“Install CFT”,”Acrobat Distiller,winspool,Ne00:”)
              and when you run the code you’ll have on the desktop a file named : Install CFT.pfd

            • #871951

              The PDF report name needs to be different from the report name which l have already created.
              This l am certain is the problem l am having.
              Is there a solution to this?

              Justin.

            • #871964

              I don’t understand.
              You have created reports. You want them on paper or as pdf.
              If you want them on paper, use the normal procedure : DoCmd OpenReport …
              If you want them in a pdf file use my code.
              if you need different reports on paper than in pdf, you’ll have to create new reports and use these new reports to create pdf files.

            • #871978

              If you see the most recent post to D. Willett you will see what l mean.

              Justin

            • #871979

              If you see the most recent post to D. Willett you will see what l mean.

              Justin

            • #871965

              I don’t understand.
              You have created reports. You want them on paper or as pdf.
              If you want them on paper, use the normal procedure : DoCmd OpenReport …
              If you want them in a pdf file use my code.
              if you need different reports on paper than in pdf, you’ll have to create new reports and use these new reports to create pdf files.

            • #871966

              Justin

              I think there’s a bit of confusion here.
              why does the PDF need to be a different name to the Access report.
              It would be much simpler to rename the reports within Access.

              You can right click on a report and select “Rename”
              The reports must be named exactly as the code is trying to pull.

              If your users will be viewing the PDF’s as opposed to the Access Reports, this doesn’t really seem to be an issue.

            • #871974

              It needs to be different for requirement reasons.
              see attachement for an example as to what l mean.

              Justin.

            • #871981

              Justin

              1.Which version will your users view: Access or PDF ?

              2. Who has access to the Access reports: Just you, or many users ?

              3. Why do the names have to be different.

              If you’re not specific in your answers, it’s extremely difficult to help you.

            • #871988

              Dave,

              Response to your questions:

              1.Which version will your users view: Access or PDF ? PDF

              2. Who has access to the Access reports: Just you, or many users ? Myself and others.

              3. Why do the names have to be different. User requirements

            • #872011

              Justin

              Sorry I can’t help you further on this.

              Francois, thank you for the conversion code, it is proving to be usefull within my application.
              I think I have a solution to the save location, I’ll test it further.

            • #872012

              Justin

              Sorry I can’t help you further on this.

              Francois, thank you for the conversion code, it is proving to be usefull within my application.
              I think I have a solution to the save location, I’ll test it further.

            • #872029

              You need to discuss the user requirements with your users because someone doesn’t understand the situation. Just because there is a user requirement doesn’t mean that it makes sense. Access and pdf reports *always* have a different name because Access reports don’t exist outside the Access database and pdf reports don’t exist inside it. The pdf report will have a pdf extension, while an Access report “printed” to Word will have an rtf extension. What more difference do you need?

            • #872033

              I fully understand they have different extension names.
              When l am impying difference names l mean a different report name to a different .pdf filename.

              Justin.

            • #872089

              If you rename your reports to what the user want, you will have your pdf files with the right names.
              If your application is well designed, the users would never see a report name. You should provide a menu or switch board, with whatever description you want, to launch the reports.
              If you persist, the only (very bad) solution I see is duplicating the reports and use the ones for pdf files and the others for paper prints.

            • #872098

              If l rename the report names then this would take the same amount of time as typing out the .pdf filesname in the first
              place , therfore no need for the additional coding which you have provided.
              I have designed a command button to run the reports.
              If there is not a solution to not having to re-type out the pdf filenames , then l will see the user to see if he will allow me to call the .pdf filenames the same name as the report names.

              Thank you for your help today , it has been much appreciated.

              Justin.

            • #872209

              What are you talking about when you say “file names”? The pdf version of the report, once printed to the pdf printer, definitely has a file name. The Access report NEVER has a file name because it is not a FILE. The only way you get a file name for an Access report is to print it or output it to an rtf or excel format, for instance. Most PDF printers have a method of some sort that allows you to pass a filename to be created when generating the pdf file. You’ll need to look for that in the one you’re using if you want a different name from the default generated from the Access report’s name itself.

            • #872210

              What are you talking about when you say “file names”? The pdf version of the report, once printed to the pdf printer, definitely has a file name. The Access report NEVER has a file name because it is not a FILE. The only way you get a file name for an Access report is to print it or output it to an rtf or excel format, for instance. Most PDF printers have a method of some sort that allows you to pass a filename to be created when generating the pdf file. You’ll need to look for that in the one you’re using if you want a different name from the default generated from the Access report’s name itself.

            • #872390

              Do you realise that the report name (I mean Filename) is generated from the Caption property of the report.

            • #872427

              Here is how I do it using PDF995 in Access 2003.

              * PDF995 has an option to specify a default filename for all files. Mine is set to catalogue.pdf. So I create the pdf, then make a copy of the file to the desired filename.
              * As written my code uses another function I have not included fnGetLinkedPath that returns the location of the backend tables. You could replace that with a hardcoded value for mypath, which is where you want the file put.

              * I have also included the code needed for Sleep to work, because I call that.

              * There is no error handling in this yet. I have only just cobbled this together from something slightly different.

              Private Declare Sub sapiSleep Lib “kernel32” _
              Alias “Sleep” _
              (ByVal dwMilliseconds As Long)

              Sub sSleep(lngMilliSec As Long)
              If lngMilliSec > 0 Then
              Call sapiSleep(lngMilliSec)
              End If
              End Sub

              Sub sbPrintReporttoPDF(strReportName As String, strPDFfilename As String, strCriteria As String)
              Dim blPDFinstalled As Boolean
              Dim prt As Printer
              Dim mypath As String
              Dim prtDefault As Printer
              mypath = fngetlinkedpath() & “”
              blPDFinstalled = False
              ‘check if there is a printer called PDF995 – cant create pdfs without it
              For Each prt In Printers
              If prt.DeviceName = “PDF995” Then
              blPDFinstalled = True
              End If
              Next prt
              If blPDFinstalled = False Then
              MsgBox “You have asked for a PDF to be created, but PDF995 is not installed.” & vbNewLine & “PDF995 is required for creation of PDF files”
              Exit Sub
              End If

              If Right(strPDFfilename, 3) “pdf” Then
              Next prt strPDFfilename = strPDFfilename & “.pdf”
              End If

              Set prtDefault = Application.Printer
              ‘ to remember the current printer
              Set Application.Printer = Application.Printers(“PDF995”)
              DoCmd.OpenReport strReportName, acNormal, , strCriteria
              Call sSleep(2000)
              ‘ otherwise the filecopy tries to happen before the previous step is finished.
              FileCopy mypath & “catalogue.pdf”, mypath & strPDFfilename

              Set Application.Printer = prtDefault
              ‘ to set the printer back

              End Sub

            • #872428

              Here is how I do it using PDF995 in Access 2003.

              * PDF995 has an option to specify a default filename for all files. Mine is set to catalogue.pdf. So I create the pdf, then make a copy of the file to the desired filename.
              * As written my code uses another function I have not included fnGetLinkedPath that returns the location of the backend tables. You could replace that with a hardcoded value for mypath, which is where you want the file put.

              * I have also included the code needed for Sleep to work, because I call that.

              * There is no error handling in this yet. I have only just cobbled this together from something slightly different.

              Private Declare Sub sapiSleep Lib “kernel32” _
              Alias “Sleep” _
              (ByVal dwMilliseconds As Long)

              Sub sSleep(lngMilliSec As Long)
              If lngMilliSec > 0 Then
              Call sapiSleep(lngMilliSec)
              End If
              End Sub

              Sub sbPrintReporttoPDF(strReportName As String, strPDFfilename As String, strCriteria As String)
              Dim blPDFinstalled As Boolean
              Dim prt As Printer
              Dim mypath As String
              Dim prtDefault As Printer
              mypath = fngetlinkedpath() & “”
              blPDFinstalled = False
              ‘check if there is a printer called PDF995 – cant create pdfs without it
              For Each prt In Printers
              If prt.DeviceName = “PDF995” Then
              blPDFinstalled = True
              End If
              Next prt
              If blPDFinstalled = False Then
              MsgBox “You have asked for a PDF to be created, but PDF995 is not installed.” & vbNewLine & “PDF995 is required for creation of PDF files”
              Exit Sub
              End If

              If Right(strPDFfilename, 3) “pdf” Then
              Next prt strPDFfilename = strPDFfilename & “.pdf”
              End If

              Set prtDefault = Application.Printer
              ‘ to remember the current printer
              Set Application.Printer = Application.Printers(“PDF995”)
              DoCmd.OpenReport strReportName, acNormal, , strCriteria
              Call sSleep(2000)
              ‘ otherwise the filecopy tries to happen before the previous step is finished.
              FileCopy mypath & “catalogue.pdf”, mypath & strPDFfilename

              Set Application.Printer = prtDefault
              ‘ to set the printer back

              End Sub

            • #872391

              Do you realise that the report name (I mean Filename) is generated from the Caption property of the report.

            • #872099

              If l rename the report names then this would take the same amount of time as typing out the .pdf filesname in the first
              place , therfore no need for the additional coding which you have provided.
              I have designed a command button to run the reports.
              If there is not a solution to not having to re-type out the pdf filenames , then l will see the user to see if he will allow me to call the .pdf filenames the same name as the report names.

              Thank you for your help today , it has been much appreciated.

              Justin.

            • #872090

              If you rename your reports to what the user want, you will have your pdf files with the right names.
              If your application is well designed, the users would never see a report name. You should provide a menu or switch board, with whatever description you want, to launch the reports.
              If you persist, the only (very bad) solution I see is duplicating the reports and use the ones for pdf files and the others for paper prints.

            • #872034

              I fully understand they have different extension names.
              When l am impying difference names l mean a different report name to a different .pdf filename.

              Justin.

            • #872030

              You need to discuss the user requirements with your users because someone doesn’t understand the situation. Just because there is a user requirement doesn’t mean that it makes sense. Access and pdf reports *always* have a different name because Access reports don’t exist outside the Access database and pdf reports don’t exist inside it. The pdf report will have a pdf extension, while an Access report “printed” to Word will have an rtf extension. What more difference do you need?

            • #871989

              Dave,

              Response to your questions:

              1.Which version will your users view: Access or PDF ? PDF

              2. Who has access to the Access reports: Just you, or many users ? Myself and others.

              3. Why do the names have to be different. User requirements

            • #871982

              Justin

              1.Which version will your users view: Access or PDF ?

              2. Who has access to the Access reports: Just you, or many users ?

              3. Why do the names have to be different.

              If you’re not specific in your answers, it’s extremely difficult to help you.

            • #871975

              It needs to be different for requirement reasons.
              see attachement for an example as to what l mean.

              Justin.

            • #871967

              Justin

              I think there’s a bit of confusion here.
              why does the PDF need to be a different name to the Access report.
              It would be much simpler to rename the reports within Access.

              You can right click on a report and select “Rename”
              The reports must be named exactly as the code is trying to pull.

              If your users will be viewing the PDF’s as opposed to the Access Reports, this doesn’t really seem to be an issue.

            • #871976

              Users won’t be able to just “view” pdf reports the way they do an Access report. The PDF report is created by printing the Access report to a PDF printer. You will NEVER see the pdf report in the database window because it isn’t in Access, it’s a pdf file on the drive and it has to be opened with a pdf reader.

            • #871977

              Users won’t be able to just “view” pdf reports the way they do an Access report. The PDF report is created by printing the Access report to a PDF printer. You will NEVER see the pdf report in the database window because it isn’t in Access, it’s a pdf file on the drive and it has to be opened with a pdf reader.

            • #871952

              The PDF report name needs to be different from the report name which l have already created.
              This l am certain is the problem l am having.
              Is there a solution to this?

              Justin.

            • #871935

              That was to use a copy and past to copy the name of the report in the code.
              In the lines calling the procedure where I told you to put the report name you should enter the exactly same name as appear in the database window, surrounded by double quotes.
              From your attachment I see a report named Install CFT
              If you want a pdf file from that report use the line :
              Call RunReportAsPDF(“Install CFT”,”Acrobat Distiller,winspool,Ne00:”)
              and when you run the code you’ll have on the desktop a file named : Install CFT.pfd

            • #871923

              The reports are there ok. They need to be different to the . PDF report name.
              I don’t know what you mean by
              “If this don’t work, select the report in the database window and press F2, then CTRL-C, go to the code window and put the cursor on the line for that report. Erase the report name and put the cursor between the two double quotes (“|”) end press CTRL-V”
              I tried this but it did not work , see attachment.

              Justin

            • #871926

              The report name is different to the .PDF name.
              The reports are there.

            • #871927

              The report name is different to the .PDF name.
              The reports are there.

            • #871885

              Look at the database window (see attachment)
              Is the CFT Open Action Report in the list of reports ? Is the spelling exact the same as in the code ? Check for double spaces. See attachment one is with single space and the other with double space.
              If this don’t work, select the report in the database window and press F2, then CTRL-C, go to the code window and put the cursor on the line for that report. Erase the report name and put the cursor between the two double quotes (“|”) end press CTRL-V

            • #871894

              Justin

              Francois has given all the information you require to complete your task.
              What you must do is to create the reports first in your database.
              The code relies on the reports first being present.
              If the reports are not present, then the code has nothing to convert, unless as Francois suggests, they are not named correctly.

            • #871924

              The reports are present , altough there name needs to be different to the .PDF name.
              Does this make a diffrence?

              Justin

            • #871925

              The reports are present , altough there name needs to be different to the .PDF name.
              Does this make a diffrence?

              Justin

            • #871895

              Justin

              Francois has given all the information you require to complete your task.
              What you must do is to create the reports first in your database.
              The code relies on the reports first being present.
              If the reports are not present, then the code has nothing to convert, unless as Francois suggests, they are not named correctly.

            • #871875

              The quotes are fine , see attachment.

              I don’t understand what you are impying when you mean did the report name contain quotes?
              I also don’t know what you mean when you say “The report CFT…. should exist in the database window on the tab reports”
              Can you explain these in a bit more details,maybe include a screenshot so l can see what you mean.

              Justin.

            • #871871

              The report CFT…. should exist in the database window on the tab reports.
              The quotes should be on the same place as in the other lines.
              Call RunReportAsPDF(“CFT…..”, “Acrobat Distiller,winspool,Ne01:”)
              Around the report name and around the printer name.
              Did the report name contain quotes ? if so rename it in the database window and in the code line with a name without quotes.

            • #871867

              The report name is fine.
              Don’t know what you mean by “Did the report exist?”
              Can you tell me exactly where the quotes should be?

              Justin.

            • #871855

              Check the line :
              Call RunReportAsPDF(“CTF……….)
              I can’t see the full line as the message box cover it.
              Is the name of the report correct ? Did the report exist ? Are the quotes on the right place ?

            • #871846

              I have done this and get an error.
              see attachment.

              Justin.

            • #871838

              You have to remove the line
              Private Sub cmdPrintPDF_click
              and one of the two
              End Sub

            • #871836

              I have run the code but appear to get an error.

              I have checked for End Sub and it appears to be there , not to sure what is causing the error.
              see attachment.

              Justin.

            • #871814

              That’s ok

            • #871812

              Does it matter what names l call the modules?

              Is module1 and module 2 ok?

              Justin.

            • #871782

              This program is tested on a win xp and I don’t know if it will run on other windows version.
              In the attachment you’ll find to txt files.
              Extract them and copy the content of each of the files in a different new module. Save the modules but don’t use the name RunReportAsPDF as this is the name of the sub and a sub may not have the same name as a module.
              For each report you want to run, use an instruction as follow :
              Call RunReportAsPDF(“ReportName”,”PrinterName”)
              Replace ReportName with the name of your report.
              Replace Printer Name with the printer name you find in the registry when the Adobe Distiller is set as default.
              To find this out :
              Set the adobe distiller as default printer.
              Go to Start , Run and type regedit.
              Navigate to the key HKEY_CURRENT_USERSoftwareMicrosoftWIndows NTCurrentVersionWindows
              Look in the right pane form the key Device and note the string. (On my pc it’s : Acrobat Distiller,winspool,Ne01: )
              This is the text you have to use as PrinterName.
              Close the regedit.
              Set you usual printer as default back.

              Let me know how it turns out.

            • #871770

              I have found the option in Adobe Acrobat for not prompting for filenames.
              Would you be able to send me the completed code so l can try it out.

              Thanks for your help.

              Thanks
              Justin.

            • #871768

              For the moment, if using windows xp, I can set the printer to print to pdf in code.
              In Acrobat distiller you have an option to avoid to ask where to save. If you use this, the report is saved as a pdf file with the name of the report. sample: ReportName.pdf.
              But this is saved on the desktop and I’m looking for a method to save it in a directory of your choice. That’s the last problem.

            • #871762

              It would be really appreciated if you could sort this out for me , as it would save a lot of time.

              Justin.

            • #871443

              The attached code is the same as in the previous post.
              I have been playing the last hours with programming in the registry.
              So far I can change the printer to print to the pdf distiller printer but until now I can’t find how to avoid the dialog to ask the file name.
              If I find something, I’ll post back

            • #871379

              see attached code.

              Justin

          • #871377

            Can you post the ExportPDF function ?

        • #871373

          I am using Adobe PDF writer.
          I got the function ExportPDF from the coding provided.

          Justin.

      • #871365

        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 ?

    • #871307

      Francois

      see attached code.

      Justin

    • #872115

      If you have the full version of Adobe Acrobat Try this. It uses the PDF writer of Acrobat
      You have to have PDF writer installed. Check your printers folder for this.
      If not re-install Acrobat, do a custom install and select PDF writer option.

      • #1067870

        Is there a way to make this work “WITHOUT” prompting the user for the file name?

        • #1106699

          Assume you have found the answer. As a help to others reading this post, to suppress Save as dialog, open pdf995.ini and add the line Output File=SAMEASDOCUMENT.

    • #872116

      If you have the full version of Adobe Acrobat Try this. It uses the PDF writer of Acrobat
      You have to have PDF writer installed. Check your printers folder for this.
      If not re-install Acrobat, do a custom install and select PDF writer option.

    Viewing 9 reply threads
    Reply To: Print report to Acrobat PDF (AccessXP)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: