Systemically OutPut Access Report To PDF

[Mark C]

All,

I have exhaustingly been looking through the newsgroups in search of a way
to systemically output an Access 97 report to a pdf file using the full
version of Adobe Acrobat. I want the user to be able to click a button and
have the Access report saved as a pdf in a directory specified by me the
programmer.

I have seen a posting by John Baker promoting a web-sight that has an mde
that one can reference in any database but after 30 days requires a
registration fee. Someone must have done some automation of this type. Any
help would be much appreciated.

Regards,

Mark C.

[Tony Toews]

"Mark C" <he******@yahoo .com> wrote:

I have exhaustingly been looking through the newsgroups in search of a way
to systemically output an Access 97 report to a pdf file using the full
version of Adobe Acrobat. I want the user to be able to click a button and
have the Access report saved as a pdf in a directory specified by me the
programmer.

Creating PDF files from within Microsoft Access
http://www.granite.ab.ca/access/pdffiles.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[david epsom dot com dot au]

Access can do three things with a report:
It can print/preview, it can 'OutputTo', it can export the design as text.

Print/Preview means drawing the report on a windows canvas, and sending the
metafile either to a printer driver or to a screen driver.

OutputTo means (meant) a wizard loops through the report and exports the
details in a different format.

SaveAsText only export the design information, not data.

Since there is no PDF export wizard built into Access, the only ways you can
get PDF from a report are
(a) Write your own code to make PDF/Postscript documents.
(b) Use a printer driver that writes to a PDF/Postscript document.
(c) Use a PDF creator that will accept a Text/Rich Text/HTML/Excel/Word
document

(a) is unheard of, and (c) is really dodgy because the OutputTo wizard
notoriously miscalculates the size of text and truncates fields.

That leaves only (b). The full version of Acrobat comes with Distiller.
Distiller is one of the more difficult programs to automate, because Acrobat
is cross platform.

(david)
"Mark C" <he******@yahoo .com> wrote in message
news:zN******** ************@co mcast.com...

All,

I have exhaustingly been looking through the newsgroups in search of a way
to systemically output an Access 97 report to a pdf file using the full
version of Adobe Acrobat. I want the user to be able to click a button and
have the Access report saved as a pdf in a directory specified by me the
programmer.

I have seen a posting by John Baker promoting a web-sight that has an mde
that one can reference in any database but after 30 days requires a
registration fee. Someone must have done some automation of this type. Any
help would be much appreciated.

Regards,

Mark C.

[Chuck]

On Sat, 17 Jul 2004 21:11:41 -0700, "Mark C" <he******@yahoo .com> wrote:

Add a new printer in the control panel printer section. Get the driver from
Acrobat. Name it PDF printer.

Make a button that runs a macro that opens the report in print mode.
In my case, the first time I ran the macro a window opened and gave me a choice
of which printer to use. Chose PDF printer. Click *print*. When the printing
is done, Acrobat will open, give the file a name and save it or use the
default name which is the name of the report. Close Acrobat. With the report
still open in Access, click file/save. The next time you click on the macro
button, the only thing you will have to do is give the PDF file a name.

Chuck
....

All,

I have exhaustingly been looking through the newsgroups in search of a way
to systemically output an Access 97 report to a pdf file using the full
version of Adobe Acrobat. I want the user to be able to click a button and
have the Access report saved as a pdf in a directory specified by me the
programmer.

I have seen a posting by John Baker promoting a web-sight that has an mde
that one can reference in any database but after 30 days requires a
registration fee. Someone must have done some automation of this type. Any
help would be much appreciated.

Regards,

Mark C.

[Sal Cifone]

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[dgs5150]

"Mark C" <he******@yahoo .com> wrote in message news:<zN******* *************@c omcast.com>...

All,

I have exhaustingly been looking through the newsgroups in search of a way
to systemically output an Access 97 report to a pdf file using the full
version of Adobe Acrobat. I want the user to be able to click a button and
have the Access report saved as a pdf in a directory specified by me the
programmer.

I have seen a posting by John Baker promoting a web-sight that has an mde
that one can reference in any database but after 30 days requires a
registration fee. Someone must have done some automation of this type. Any
help would be much appreciated.

Regards,

Mark C.

Mark - I do have a sample access 97 database where I have put together
and automated the different components necessary to programatticall y
send an Access 97 report to a PDF file. This does require the full
version of Adobe Acrobat installed as custom because you need the
Acrobat PDFWriter printer that is not installed by default (at least
it was this way in Acrobat 5.0)

Here is an overview of what it does,
1. determine the current default printer
2. change the current printer to Acrobat PDF Writer
3. Modify the registry to save PDF to supplied path and document name
so PDF writer does not prompt for file name and location
4. change back to the default printer

If you are interested I can send you a copy.

[Tony Toews]

dg*****@yahoo.c om (dgs5150) wrote:

Mark - I do have a sample access 97 database where I have put together
and automated the different components necessary to programatticall y
send an Access 97 report to a PDF file.

If you like I'd be happy to host that file on my website. Along with credit and your
email address. Or not if you'd prefer.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Mark C]

I would differently like a copy of that sample database. I have talked to
the big bosses at my company and we have decided to go with the
http://ourworld.compuserve.com/homep...cg/acgsoft.htm method. I
downloaded and tested the Access 2000 version and it works wonderfully with
no user interaction other then them clicking a button. Sometimes you just
have to admit defeat.

Regards,
Mark C.
he******@yahoo. com


"dgs5150" <dg*****@yahoo. com> wrote in message
news:52******** *************** ***@posting.goo gle.com...

"Mark C" <he******@yahoo .com> wrote in message

news:<zN******* *************@c omcast.com>...

All,

I have exhaustingly been looking through the newsgroups in search of a way to systemically output an Access 97 report to a pdf file using the full
version of Adobe Acrobat. I want the user to be able to click a button and have the Access report saved as a pdf in a directory specified by me the
programmer.

I have seen a posting by John Baker promoting a web-sight that has an mde that one can reference in any database but after 30 days requires a
registration fee. Someone must have done some automation of this type. Any help would be much appreciated.

Regards,

Mark C.

Mark - I do have a sample access 97 database where I have put together
and automated the different components necessary to programatticall y
send an Access 97 report to a PDF file. This does require the full
version of Adobe Acrobat installed as custom because you need the
Acrobat PDFWriter printer that is not installed by default (at least
it was this way in Acrobat 5.0)

Here is an overview of what it does,
1. determine the current default printer
2. change the current printer to Acrobat PDF Writer
3. Modify the registry to save PDF to supplied path and document name
so PDF writer does not prompt for file name and location
4. change back to the default printer

If you are interested I can send you a copy.

[James Fortune]

"Mark C" <he******@yahoo .com> wrote in message news:<3p******* *************@c omcast.com>...

I would differently like a copy of that sample database. I have talked to
the big bosses at my company and we have decided to go with the
http://ourworld.compuserve.com/homep...cg/acgsoft.htm method. I
downloaded and tested the Access 2000 version and it works wonderfully with
no user interaction other then them clicking a button. Sometimes you just
have to admit defeat.

Regards,
Mark C.
he******@yahoo. com

I admit defeat on creating standard Access Reports in PDF. Only
Microsoft can make money competing with free ;-). But, for those who
want to think outside the box of Access Report limitations my approach
is new and worthwhile. If you don't use the pdf printer drivers you
can do a lot more with presentation than standard Access Reports can.
I might still do code to create standard Access Reports directly in
pdf just for fun. I like Access Reports, but I'm all for any
techniques that stretch Access' abilities.

James A. Fortune

[Taylor Bryant]

Caution: excessive windiness ahead

Maybe it is a moot point at this junction, but to anyone else who is
following this, I have just gone through this struggle. This is a summary of
what probably appears on the above links, but hopefully it helps. This uses
visual basic, so hopefully it is not an inappropriate post, but if anyone is
adventurous...

Two ways- PDFWriter, which in some ways was a bit easier for me. When you
print (i.e. DoCmd.OpenRepor t [defaults to printing unless you specify
acViewPreview) anything with PDFWriter selected as your default printer, it
first looks in the registry to see if there is a key indicating the name of
the next PDFWriter File to be output. So the trick is to write that
information in the registry beforehand (it is automatically deleted later.)

The problem I encountered with PDFWriter is the way it draws graphics
(non-postscript method, can't remember the name) makes it unacceptable if
the report includes tables or graphs.

So we move to PDF Distiller, which has a different methodology. I created a
new PDFDistiller printer so that I could have the files dropped off in a
general temp directory, which I then snatch the file from, rename it and put
it in an appropriate folder. (i.e. Add a new printer, choose as printer port
a PDF port pointing to c:\TempReports, choose any old printer driver, and
name the printer ProjReportsPDFW riter or ProjReportsDist iller. Then go to
the properties, and under the advanced tab go to drivers, and change it to
either the PDFWriter driver or AdobePS Acrobat Distiller depending on which
method you decide on.)

For both methods in access, I chose to specify an printer specific to the
reports I am printing. This way, I did not need to worry about default
printers or anything of that sort. This is accomplished by opening the
report, clicking "Setup" from the toolbar, which makes a "Page Setup"
dialogue box pop up. Click on the "Page" tab, and where it says "Printer for
YourReportNameH ere" click "Use Specific Printer", then click the "Printer"
button to choose specifics.

Damn, I'm long winded.

The trick here is that this essentially creates it's own instance of the
printer that exists only for that report, so any changes you want to make to
the Printing preferences after that point need to be approached through this
dialogue, rather than the usual Printers and Faxes Control Panel access.
(Either that, or make the changes in the Printers and Faxes window, change
it the Report to select a different printer, then change it back to the
original so it loads the newest settings). The PDFWriter preferences are
self explanatory, but for distiller to be automated, you must deselect the
following checkboxes in the Printing Preferences\"Ad obe PDF Settings" tab:
Do not send fonts to distiller, View Result in Acrobat, Prompt for the PDF
Filename, Ask to Replace existing PDF File (All unchecked!).

My code follows.

Hopefully this helps.
Taylor

P.S. I am using Acrobat 5.0- I installed 6.0 but too much bloat, had me
running back.

*************** *************** *************** *************** *************** *
PDFWriter method: I kind of jump into the code (skip the declarations), but
hopefully this helps. This is originally for word, and I tried to adapt it
for Access, but I really did not go nuts testing it. I use the Distiller
method.
*************** *************** *************** *************** *************** *

DocSaveName = "C:\Path\PDFNam eStringHere" 'This was declared earlier as
a string

' Create Registry Key that tells acrobat the PDF Save name
Call RegistryValue(D ocSaveName) 'This function follows, skip to it to
see what happens

DoCmd.OpenRepor t "2005 Packets Coversheet"

'Note: registry key is automatically deleted after use
'Quit

End Function

'This creates the registry key, needs a reference to (I think...) Windows
Script Host Object Model

Sub RegistryValue(D ocSaveName As String)
Dim WshShell As IWshRuntimeLibr ary.WshShell
Dim AcroRegVal As String
Dim AcroName As String
AcroName = DocSaveName & ".pdf"

Set WshShell = CreateObject("W script.Shell")
AcroRegVal = "HKEY_CURRENT_U SER\Software\Ad obe\Acrobat
PDFWriter\PDFFi leName"
WshShell.RegWri te AcroRegVal, AcroName

' I used the following functions when I was in my test phase to make sure it
was writing to the reg correctly
'strValue = WshShell.RegRea d(AcroRegVal)
'MsgBox strValue

End Sub

*************** *************** *************** *************** *************** *
****PDF Distiller method: Much cleaner looking reports, bit more trouble.
Again, I am jumping in...
*************** *************** *************** *************** *************** *
Sub PrintReports(Co ordinatorName As String)

'CoordinatorNam e is criteria I am passing to the report, so it opens only
the
'reports corresponding to a specific coordinator

Dim ReportFormInfo As String 'Title passed from Report to PDF Distiller -
'not necessarily the name of the Report itself,
'but rather the caption field in report properties. '
'It is automatically passed, I only need it in order to rename the PDF
later...

Dim CurPath As String 'Where the database is located now
Dim OldPath As String 'Temporary path where Distiller dumps the PDF
Dim SavePath As String 'Where I want the renamed PDF to end up

OldPath = "C:\Temp\" 'That is where PDF Distiller dumps all my files after
they are made

ReportFormInfo = "2005 Survey Info For Forms.pdf" 'Default name Distiller
saves the Report as

CurPath = Application.Cur rentProject.Pat h & "\" 'Say the db is located on
"C:\ReportD B\"

SavePath = CurPath & CoordinatorName & "\" 'I will ultimately save the new
PDF
'in "C:\ReportDB\Jo hn Smith" (the name of the coordinator)

DoCmd.OpenRepor t "2005 Envelopes Summary Form", , , "[Coordinator] = " &
CoordinatorName 'pass the criteria

Call CreateFolder(Sa vePath, OldPath, ReportFormInfo)

End Sub

*************** *************** *************** *************** *************** *
This renames the PDF meaningfully, creates folders for it, etc.
*************** *************** *************** *************** *************** *

Sub CreateFolder(Sa vePath As String, OldPath As String, ReportFormInfo As
String)
Dim fso As IWshRuntimeLibr ary.FileSystemO bject
Dim OldFile As String
Dim NewFile As String

Set fso = CreateObject("S cripting.FileSy stemObject")
If Not fso.FolderExist s(SavePath) Then 'checks to see if
"C:\ReportDB\Jo hn Smith" folder exists
fso.CreateFolde r (SavePath) 'If not, Creates the folder
"C:\ReportDB\Jo hn Smith"
End If

If fso.FileExists( OldPath & ReportFormInfo) Then
'checks for "C:\Temp\20 05 Survey Info For Forms.pdf"
fso.CopyFile OldPath & ReportFormInfo, SavePath & ReportFormInfo
'copies it to "C:\ReportDB\Jo hn Smith\2005 Survey Info For Forms.pdf"
End If

End Sub