Using VBA to create PDFs

I searched up and down the net to find out how to use the Adobe (Using Adobe 8 for form creation, as well as placing my Access reports into PDF format). I ended up creating some basic code, which I will add, that basically goes through a loop to print out each report (I'm printing the same report out 300+ times). Perhaps not the greatest way of doing it, but again, no luck in using the Adobe objects.

Here is the code:

Public Function ExportScorecards()
Dim oldname As String
Dim newname As String
Dim existingfile As String
Dim inputdir
Dim rs As DAO.Recordset
Dim strContractID As String

On Error Resume Next

Set rs = CurrentDb.OpenRecordset("Select ContractID from GETID;")

rs.MoveFirst

While Not rs.EOF
gstrContractID = rs!ContractID

gstrExistingScorecard = Dir(gstrScorecardFilePath & "" & gstrContractID & ".pdf")' *** This is to delete the existing pdf file if it exists
Do While Len(gstrExistingScorecard) > 0
Kill gstrScorecardFilePath & "" & gstrExistingScorecard
gstrExistingScorecard = Dir
Loop' *** Print the report, Report has adobe pdf as specific printer
DoCmd.OpenReport gstrScorecardReportName, acNormal, , "ContractID='" & gstrContractID & "' "' *** Delay for completion of adobe printing
WaitAwhile (3)

' *** File locations
oldname = gstrScorecardFilePath & "" & gstrReportPDFStaticName _
: newname = gstrScorecardFilePath & "" & gstrContractID & ".pdf"' *** function for moving and changing the file name
Name oldname As newname

rs.MoveNext
Wend

Set rs = Nothing

End FunctionPublic Function WaitAwhile(seconds As Integer)
On Error GoTo wait_err
Dim tmp As Variant
tmp = Now
Call SysCmd(acSysCmdInitMeter, "Waiting " & seconds & " Seconds...", seconds)
Do While Now < tmp + (seconds / 24 / 60 / 60)
Call SysCmd(acSysCmdUpdateMeter, DateDiff("s", tmp, Now))
DoEvents
Loop
wait_res:
Call SysCmd(acSysCmdClearStatus)
Exit Function
wait_err:
MsgBox Error & " " & Err.Description, 0, "WaitAwhile Error!"
Resume wait_res
End Function

Thanks! I'll give this a try.

I adapted this to my program and i still get the dialog box and have to click ok to save the PDF. Is this what you get?

Simple way is to got to "Printers and Faxes" folder
- Right click on the "Adobe PDF" Printer to access the Properties window.
- Select the General Tab
- Click on "Printing Preferences"
- Select a folder location for "Adobe PDF Output Folder", Deselecting the dropdown option of "Prompt for Adobe PDF Filename" will remove the dialog box prompt to save the PDF.
- Make required changes as desired on this tab like you can uncheck "View Adobe PDF result".
Hope this is what you desired...all tough this is a very late response to you problem.

Since you are writing VBA code you might be able to use the Acrobat IAC to open the file in Acrobat to force a conversion. Have you looked at using Acrobat Distiller directly? There are also many different 3rd party tools for doing PDF conversions. Many of them are free. For example: http://www.primopdf.com/

Thom Parker
The source for PDF Scripting Info
[url=http://www.pdfScripting.com]pdfscripting.com[/url]

The Acrobat JavaScript Reference, Use it Early and Often
[url=http://www.adobe.com/devnet/acrobat/]http://www.adobe.com/devnet/acrobat/[/url]