Results 1 to 2 of 2
  1. #1

    Use VBA to create PDF File (VBA/Acess)

    I have an application in which I want to create about 40 different .PDF files when the application is opened each morning.

    I used the code by Ken Getz listed in an earlier thread. This works great except that Acrobat launches automatically. So after about 8 .PDf files, Access abends.

    This application will be on a dedicated PC with a tech scheduler. I just want the .PDFs to sit there until our sales desk needs them.

    I tried modifying the code by Ken to use distiller. Here is what I did:

    Option Compare Database
    Option Explicit

    Private drexisting As aht_tagDeviceRec
    'Const AcrobatName = "Adobe PDFWriter"
    'Const AcrobatDriver = "PDFWRITR"
    Const AcrobatName = "AdobePS Acrobat Distiller"
    Const AcrobatDriver = "ADOBEPS4"
    'Const AcrobatPort = "LPT1:"
    Const AcrobatPort = "C:Program FilesAdobeAcrobat 4.0PDF Output*.pdf,15,45"

    Sub ResetDefaultPrinter()
    Call ahtSetDefaultPrinter(drexisting)
    End Sub
    Function ChangetoAcrobat()
    If ahtGetDefaultPrinter(drexisting) Then
    Dim dr As aht_tagDeviceRec
    With dr
    .drDeviceName = AcrobatName
    .drDriverName = AcrobatDriver
    .drPort = AcrobatPort
    End With
    Call ahtSetDefaultPrinter(dr)
    End If
    End Function

    Sub ChangePdfFileName(NewFileName As String)
    ' Call aht_apiWriteProfileString("Acrobat PDFWriter", "PDFFileName", NewFileName)
    Call aht_apiWriteProfileString("AdobePS Acrobat Distiller", "PDFFileName", NewFileName)

    Here is my code for creating the individual reports:

    Option Compare Database
    Option Explicit
    'this module will create the .PDF files for the RSD Life Status Reports
    'a .pdf file will be created for each RSD
    Function LSR()

    Dim db As Database
    Dim rst As Recordset
    Dim qdf As QueryDef
    Dim prm As Parameter
    Dim xWhere As String
    Dim vFileName As String
    Dim vReport As String

    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qryLSR_RsdList")
    Set rst = qdf.OpenRecordset(dbOpenDynaset)

    vFileName = "LSR"
    vReport = "rptLSR_LifeStatusReport_PDF"

    With rst
    If rst.RecordCount > 0 Then
    Do Until .BOF
    xWhere = "Territory = """ & rst!territory & """"
    ChangePdfFileName "T:Hartford" & vFileName & rst!territory & rst!rsdname & ".pdf"
    DoCmd.OpenReport vReport, acViewNormal, , xWhere
    End If
    End With

    End Function

    When I use the code with the "Distiller" parameters, the file is trying to be saved with the filename stored in the WIN.INI "Acrobat PDFwriter" filename and not write over it. And Acrobat still launches and my code abends because the file is already in use (happens after the 1st file created).

    Now, I did change the preferences in Distiller so that it would not launch. I know this works when I manually create the .PDF using the file, print options.

    There has to be a way to create this files automatically without Acrobat launching, isn't there?

    Any suggestions?

    If this is confusing, I will provide any additional infomation needed.


    Oh, I am using Distller 4.0

  2. #2
    3 Star Lounger
    Join Date
    Aug 2001
    Jeddah, Saudi Arabia
    Thanked 1 Time in 1 Post

    Re: Use VBA to create PDF File (VBA/Acess)

    We use Acrobat/Distiller to automatically produce our price lists every month.

    Basically the sequence is:

    1. Define a Postscript printer and set it to 'Print to File'. It can be any device. We use an HP LaserJet 8100 PS. We don't actually have one but after experimenting I found this to generate .ps files that Distiller really likes.

    2. Set up a folder for Distiller. Then set Distiller to 'watch' this folder. It will create two sub-folders, In and Out. Let Distiller run all the time.

    3. In your code, print the document you want converted to a PDF to a file with a .ps extension in the 'In' folder using the postscript printer. When the file is printed Distiller will pick up the .ps file and generate a PDF file of the same name in the 'Out' folder.

    I hope this at least gives you a basis to work with. I can't post the code we use due to company policies.


    Kevin Bell

Posting Permissions

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