Solved: Auto save a report in pdf format

[andrew93]

Hello

I regularly read posts here but I seldomly post my own topics given I often find what I am looking for in this forum. But this time I'm stumped.

What I would like to do is to create a pdf version of a report and have Access automatically save it in a specific directory.

I found some code in this thread (http://www.vbaexpress.com/forum/showthread.php?t=5561) that doesn't quite work for me. The final post includes code that brings up the 'Save As' window. I can get this to work but the user is then prompted with a 'Save' window. Is it possible to modify this code such that it automatically save the report as a pdf?

As an alternative, I tried the code in the 2nd to last post by geekgirlau. After a very small amount of tweaking to suit my report names etc, the routine goes through the motions, the directory is created, I can see the report in preview mode, I get a MsgBox stating the report has been saved but there is no report. (I have Adbode PDF writer installed) A search of the hard drive doesn't reveal the report that was supposedly saved so I guess something isn't quite right. Has anyone else tried the code posted by geekgirlau? Is there an obvious issue that I am missing? (Late edit : there is no print or output command for a start....so that goes some way to explaining why there is no output)

Or does anyone have alternative methods of automatically saving pdf versions of reports?

I am happy to post the modified code if required.

TIA, Andrew

[andrew93]

Update
I found some code here (http://www.tek-tips.com/viewthread.cfm?qid=1119207&page=1) that works beautifully! The only issue I now have is how do I alter the code to save the report into (let's say) the directory where the database is located?

I'm not a VBA expert but I believe the relevant part of the latest code is this part :

Public Function RunReportAsPDF(prmRptName As String, prmPdfName As String, UniqueID As Integer) As Boolean

' Returns TRUE if a PDF file has been created

Dim AdobeDevice As String
Dim strDefaultPrinter As String

'Find the Acrobat PDF device

AdobeDevice = GetRegistryValue(HKEY_CURRENT_USER, "Software\Microsoft\WIndows NT\CurrentVersion\Devices", "Adobe PDF")

If AdobeDevice = "" Then ' The device was not found
MsgBox "You must install Acrobat Writer before using this feature"
RunReportAsPDF = False
Exit Function
End If

' get current default printer.
strDefaultPrinter = Application.Printer.DeviceName

Set Application.Printer = Application.Printers("Adobe PDF")

'Create the Registry Key where Acrobat looks for a file name
CreateNewRegistryKey HKEY_CURRENT_USER, "Software\Adobe\Acrobat Distiller\PrinterJobControl"

'Put the output filename where Acrobat could find it
SetRegistryValue HKEY_CURRENT_USER, "Software\Adobe\Acrobat Distiller\PrinterJobControl", Find_Exe_Name(CurrentDb.Name, CurrentDb.Name), prmPdfName

On Error GoTo Err_handler

'Run the report (Note : I amended this code to filter the report for the unique ID)
DoCmd.OpenReport prmRptName, acViewNormal, , "[Unique_id] = " & UniqueID

While Len(Dir(prmPdfName)) = 0 ' Wait for the PDF to actually exist
DoEvents
Wend

RunReportAsPDF = True ' Mission accomplished!

Normal_Exit:

Set Application.Printer = Application.Printers(strDefaultPrinter) ' Restore default printer

On Error GoTo 0

Exit Function

Err_handler:

If Err.Number = 2501 Then ' The report did not run properly (ex NO DATA)
RunReportAsPDF = False
Resume Normal_Exit
Else
RunReportAsPDF = False ' The report did not run properly (anything else!)
MsgBox "Unexpected error #" & Err.Number & " - " & Err.Description
Resume Normal_Exit
End If

End Function

TIA, Andrew

[geekgirlau]

I believe the path for the pdf file is passed to the function RunReportAsPDF in the variable prmPdfName. Basically the principle is the same as in my earlier version of the code, however the registry key differs with different versions of Acrobat.

[andrew93]

Hi and thanks for replying!

I'm not sure why I couldn't get your code to work, it appeared to do everything correctly except create the pdf file! I would rather have used your code given it was shorter and much 'cleaner'.

In any case, thanks for the suggestion, if I append the directory name to the front of the file name that is passed through the prmPDFName field, then it works perfectly.

Thanks again!

Andrew :thumb

[geekgirlau]

I'm glad it worked for you!

Personally I struggled with the concept that the only way to set the path was to make a registry entry, but hey, as long as it works! :bug:

[andrew93]

Hi

I too couldn't understand why it was necessary to interact with the registry but so long as it works....

One thing I have noticed is that if I change the path where the file is saved, then Acrobat opens after the file is created (it opens the saved file). Whereas if I leave the directory alone (as the Distiller default) then Acrobat doesn't open. I might use a work-around to deal with this.

Thanks
Andrew

[geekgirlau]

There is a registry setting to do this - I'll have a search and see if I can find it.

[geekgirlau]

Check this out - http://www.tek-tips.com/viewthread.cfm?qid=687458