Solved: PDF an Access report

[globetrot]

Hello All,

Another day another sticky VBA question!

I have an invoicing database in Access, that increments the invoice number each time a new invoice is entered which works fine. I want to be able to convert the Access report which is the invoice into a PDF file with the correct invoice number as part of the PDF file name. i.e. If I have created invoice number 210 in the database I want to produce a PDF with the filename invoice210.pdf. I have tried various products like Jaws PDF but being external to Access I can pass on the filename when I go to print it.

Anyone tried this before?

Thanks for any assistance

[xCav8r]

globetrot, http://vbaexpress.com/forum/images/smilies/039.gif

Use the OutputTo method of the DoCmd object to generate an RTF and name it according to the desired invoice number. Then automate Word to send it to whatever PDF printer you have that retains the file name when it creates the PDF. I use Adobe Acrobat for this, and I have a routine that waits for it to show up in the specified directory (Adobe outputs only to one directory), and then moves the file to the desired location on my file system.

I also tend to favor Word over Access's Report tools, since converting from Access Reports to RTF can lose elements like graphics. That's the point of view, anyway, for my response to your question. A non-exporting alternative would be to build a little routine to wait for a generically named PDF to appear in the file system, and then rename it in accordance with the invoice number.

HTH!

[GaryB]

Hi,
Another thought on this. I did this the hard way but it is working for me. I created a report for the invoices and a print query also with [what invoice number] in the criteria field for invoice number in the query. Then created a command button to print the invoice, which, when activated brings up a window asking for the invoice number. I set the specified printer in the report for to a pdf printer ( you need to have acrobat distiller loaded on your computer for this ) and it automatically creates a pdf and opens Acrobat. ( that happens to be a default setting I have for acrobat). Another cool thing about this is you can email directly from the pdf file when it is open in Acrobat.

Gary

[xCav8r]

Gary, I'm not sure I understand. Are you saying that the PDF you're creating is automatically named in accordance with the invoice number? If so, could you explain in different words how the PDF gets named?

[GaryB]

My apologies, I left out a step. Did this from memory .



I have a query, a report and a command button:




I created a query name printinvoice based on the information from my invoice form and query.
in that query, in the field Invoicenumber, I set the criteria with [what invoice] this will invoke the enter parameter value box
I Created a report using the print invoice query as it?s data source. In pagesetup I set the printer to be a pdf printer
I Created a command button ?printinvoicereport? with the setting for it ?openreport? and in the view box ?printpreview?
When I click on the command button, the enter parameter value box appears and asks for the invoice number of the invoice I want to print. I enter the invoice number.
The invoice previews and I tell it to print and it prints to the specified pdf printer and Acrobat opens with a print view of the invoice that I can either print,email or save as. To save as you must have the full version of Acrobat.


I hope this helps and that I have conveyed this correctly. If not, please let me know.



Gary

[xCav8r]

Much clearer. Thank you! :)

[globetrot]

Thx xCav8r, but the RTF export doesnt retain drawn lines which I have used to separate columns in the report. The problem with GaryB's method is that you still have to specify a filename to PDF it to when you print.

I have had another thought that I could print it to a PostScript printer driver using the print to file option and then use the watch capability of Acrobat Distiller or the like to automatically convert the .ps file into a PDF.

Any other thoughts on this.

[xCav8r]

Thx xCav8r, but the RTF export doesnt retain drawn lines which I have used to separate columns in the report. The problem with GaryB's method is that you still have to specify a filename to PDF it to when you print.

That's why I favor merging with Word to create reports over using Access Reports and exporting them to RTF. Reports are meant to be shared, and although Access Reports can be exported in a variety of formats, each has their disadvantages. Printing to PDF is the only alternative when you want to share your reports electronically, but that's not always as desireable as a Word doc.

It seems to me that you have three options. First, you can create a Document Template in Word with which to merge your data. You can automate the merge, file name, and PDF creation from Access. Second, you can export the report to RTF and automate Word to reinsert the graphical elements you lost on conversion to RTF; then you can send to your PDF printer, naming your file in accordance with your invoice number, if you haven't already done so, and storing your file in a location that is not your PDF output directory (if you're using Adobe and worried about overwrites). Third, you can do as I suggested in my previous post, which is send your report to your PDF printer, wait for the output, grab the file, rename it, and move it as desired.

[GaryB]

Hi
I tried this out and it works in a fashion. I printed a report as postcript file. It asks you at that point to name the file and saves it. I then selected the file and told it to open with Acrobat Distiller and it converted it to a pdf file and opened acrobat. You will still have to name the file, open the directory it is saved in, select the file and then right click and use the open with command. When you do this it will save the file as a pdf file and open Acrobat.

Gary

[geekgirlau]

Try this:

Option Compare Database
Option Explicit
Const mconMOD_NAME = "bas_CreatePDF"
Sub TestPrint()
Dim strSave As String

Const conPROC_NAME = "TestPrint"


On Error GoTo ErrHandler

strSave = "TestReport_" & Format(Now(), "yyyymmdd_hhnnss") & ".PDF"

If PrintToPDF("rpt_Test", strSave) = True Then
MsgBox "The report " & strSave & vbCrLf & " has been created successfully.", _
vbInformation, "Report Completed"
End If

ExitHere:
Exit Sub

ErrHandler:
MsgBox mconMOD_NAME & " - " & conPROC_NAME & ": " & Err.Number & _
vbCrLf & Err.Description
Resume ExitHere
End Sub
Public Function PrintToPDF(strReport As String, strSave As String) As Boolean
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~
' Purpose: Print a report to a PDF file
'
' Inputs: strReport Name of report
' strSave Name of PDF file to create
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~
Dim strPrinter As String

Const conPROC_NAME = "PrintToPDF"


On Error GoTo ErrHandler

' create the registry entry to set PDF path and filename
WriteRegistryEntry strSave

strPrinter = "Acrobat PDFWriter"
Set Application.Printer = Application.Printers(strPrinter)

DoCmd.OpenReport strReport, acViewNormal

PrintToPDF = True


ExitHere:
Application.Printer = Nothing
Exit Function

ErrHandler:
Select Case Err.Number
Case 5
WriteLog "Error", mconMOD_NAME & " - " & conPROC_NAME & ": " & Err.Number & _
vbCrLf & "The printer " & strPrinter & " cannot be located! " & _
"Make sure that Adobe Acrobat has been installed on this PC."

Case Else
MsgBox mconMOD_NAME & " - " & conPROC_NAME & ": " & Err.Number & _
vbCrLf & Err.Description
End Select

Resume ExitHere
End Function
Public Function WriteRegistryEntry(strPDF As String)
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~
' Purpose: Create a registry file in order to set the name and path
' of the PDF file
'
' Assumptions: Registry file is created in same folder as current database,
' then deleted once it has been merged into the registry
'
' Inputs: strPDF Name of PDF file to create
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~
Dim strPath As String
Dim x

Const conPROC_NAME = "WriteRegistryEntry"

' set path for report
strPath = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\", , vbTextCompare))
strPath = strPath & "Reports\"

If Dir(strPath, vbDirectory) = "" Then
MkDir strPath
End If

' registry key needs "\\" in file path
strPDF = strPath & strPDF
strPDF = Replace(strPDF, "\", "\\")
' delete the registry file if it exists
On Error Resume Next
Kill strPath & "CreatePDF.reg"

' create the registry file
On Error GoTo ErrHandler
Open strPath & "CreatePDF.reg" For Append As #1

Print #1, "Windows Registry Editor Version 5.00"
Print #1, ""
Print #1, "[HKEY_CURRENT_USER\Software\Adobe\Acrobat PDFWriter]"
Print #1, """PDFFilename""=" & Chr(34) & strPDF & Chr(34)
Close #1

' merge into registry
x = Shell("regedit.exe /s " & strPath & "CreatePDF.reg", vbHide)


ExitHere:
On Error Resume Next
Close #1
Kill strPath & "CreatePDF.reg"
Exit Function

ErrHandler:
MsgBox mconMOD_NAME & " - " & conPROC_NAME & ": " & Err.Number & _
vbCrLf & Err.Description
Resume ExitHere
End Function

[globetrot]

Ive got it!

What I found was that using a free PDF converter like PDF995 you can get PDF995 to automatically name the PDF based on what is printing it. I noticed that PDF995 was using the caption on the form to name the PDF file so all I do is take the invoice number and set it as the caption for the report and print it out, whala!

Code below;

Private Sub cmdPrintInv_Click()
Dim invnum As Integer
invnum = InvoiceNumber

DoCmd.OpenReport "rptinvoice", acViewPreview
Reports("rptinvoice").Caption = "Invoice" & InvoiceNumber
Set Application.Printer = Application.Printers(0)
DoCmd.PrintOut
End Sub

[Imdabaum]

Our company bought a third party application that plugs into our database. It has a PDF class that creates the pdf object, but is not supported for Adobe 8. With Access 2007 supporting export as PDF or XPS functionality, I'm wondering if there is an easier way to output the report to a specific file as a PDF. Anyone seen anything new on this topic with Access 2007?