Use VBA to create PDF File (VBA/Acess)

Topic

New Reply

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
.MoveLast
Do Until .BOF
xWhere = “Territory = “”” & rst!territory & “”””
ChangePdfFileName “T:Hartford” & vFileName & rst!territory & rst!rsdname & “.pdf”
DoCmd.OpenReport vReport, acViewNormal, , xWhere
.MovePrevious
Loop
End If
.Close
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.

Thanks!!

Oh, I am using Distller 4.0

Reply | Quote

Replies

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.

Regards,

Kevin Bell

Reply | Quote