Print to PDF

[hughess7]

Hi all

I have spent most of the day reading the newsgroups and google etc to
automate printing an access report to PDF but have not managed to get
exactly what I want to achieve.

We have Office Prof 2003 (inc Access and Outlook) and Adobe Acrobat 6.

I want to be able to press a button on a form and have a particular report
produced in PDF format then emailed to a group of users which remain the same
each week (can create distribution group in Access for this if best way?). I
want it all automated without user intervention and the naming convention to
be "Itinerary - Date". Date being the current date or preferably the next
Monday following the current date.

I know products exist such as PDF995 but I would prefer to use what we
already have if possible? I can set the report in Access 2003 to print to a
specific printer and change this to the Adobe PDF device but this only works
if I leave the 'prompt for filename' selection turned on ie the user gets a
dialog box and has to specify a location and filename. If I turn this off it
tries to print but fails for some reason, the report is sent to the Adobe PDF
printer with filename defaulting to the Caption of the report (which I have
tried changing programmatically via report_activate event too with no joy
either) but just produces a failure error after approx 1 minute of sitting in
the print queue. Not sure if this is due to how the print device was
installed as 3rd party IT suppliers set it up (Port is mydocuments\*.pdf).

I have tried using code for the PDF creation (trying to get that right
before moving on to attempting the email!) that I have found such as
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=35321&lngWId=1

but this refers to Adobe 5 and I can't seem to get it to work so far. Has
anyone else had any joy with this? The comments on the website infer it is
possible and others have got working - can anyone help before I ditch and
attempt alternative solutions.

Thanks in advance for any help.
Sue

 

[Lynn Trapp]

Sue,
Unfortunately, there is not CURRENTLY any way to do that other than using
some "print to PDF" driver. Steven Lebans is working on an Access to Report
PDF converter for A97 through A2003, but it isn't finished yet. Also, though
this is probably too late for you, Microsoft has just announced they they
are going to be providing native PDF support in all the MS Office
applications in the next version, which is due out in about 11 months.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[hughess7]

Thanks for this info. Sorry, just to clarify - the Adobe PDF device which is
installed with the full version of Adobe Acrobat 6 is a print to PDF driver.
It works but only with the user having to choose location and filename via
the save as dialog box. Is there no way to get the path and filename entered
via code? Tried editing the registry with the PDFFilePath value but didn't
work.

Thanks
Sue

 

[Lynn Trapp]

You ought to be able to enter the path and filename as a VBA String
parameter.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[(PeteCresswell)]

Per hughess7:

I have tried using code for the PDF creation (trying to get that right
before moving on to attempting the email!) that I have found such as:
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=35321&lngWId=1

but this refers to Adobe 5 and I can't seem to get it to work so far. Has
anyone else had any joy with this?

Dunno how much joy I got out of it, but I've done it via Acrobat 4.0/PDF Writer.

You're going through the registry, right? ... passing it the file path/name...

For me, it was a *lot* of coding - a good bit of it getting to the registry.

 

[hughess7]

Hi, yes the web site I listed has all the code for editing the registry, I'm
just not sure it is correct for Adobe 6. The PDF printing device names have
changed totally.

It does write the entry to my registry HKEY Current
User\Software\Adobe\Acrobat PDFWriter - PDFFileName = c:\temp\05-oct-2005
which is taken from the onclick of a button event:
Call RunReportAsPDF("rpt Itinerary Weekly", "c:\temp\", Format(Date,
"dd-mmm-yyyy"))

No dialog box appears for the filename but that is because I removed the
tickbox from printer properties for prompt for filename, but it tries to
print then fails after 30-60 secs :-(



Public Function RunReportAsPDF(rptName As String, sPDFPath As String,
sPDFName As String)
'---------------------------------
'rptName = Microsoft Access report name you want to create pdf from
'sPDFPath = the directory path where you want to create the pdf file (eg
"c:\data\")
'sPDFName = the name of the pdf file you are wanting to create (eg.
"file001.pdf")
'---------------------------------
Dim sMyDefPrinter As String
On Error GoTo Err_RunReport
'Save current default printer
sMyDefPrinter = bGetRegValue(HKEY_CURRENT_USER,
"Software\Microsoft\WIndows NT\CurrentVersion\Windows", "Device")
' Set default printer to PDF Writer
bSetRegValue HKEY_CURRENT_USER, "Software\Microsoft\Windows
NT\CurrentVersion\Windows", "Device", "Acrobat PDFWriter"
'Setting value for PDFFileName in the registry stops file dialog box
from appearing
bSetRegValue HKEY_CURRENT_USER, "Software\Adobe\Acrobat PDFWriter",
"PDFFileName", sPDFPath + sPDFName
'Run the report

DoCmd.OpenReport rptName, acViewNormal