eBethany
asked on
How do I print Access report to PDF, give PDF unique name based on report content, and save it to a special folder on my computer?
Hi, all!
SOFTWARE INVOLVED: MS Access 2007, Adobe Acrobat Professional 8, and I also have the MS Print to PDF add-in that came out with Office 2007.
BACKGROUND: I am the registrar for a teeny-tiny, private school. I made an Access database to maintain student grades and contact information (which means I am the IT department, too). I would rate my "skills" with Access at about intermediate to low-intermediate. I have had no formal training and can only figure out VB tweaks if the instructions are explicit.
ISSUE: In an effort to scale back costs I have started uploading reports with specific names as PDF files to our website for the parents to view/print. I am looking for a way to do the following in one click of a button: 1) print the report with my Adobe PDF printer, 2) name the resulting PDF with the student whose record is open on the form (so, DoeJaneReportName2008-09SY .pdf), and 3) save the file in a folder on my computer that is uploaded to our website every night. Right now I am clicking the print button, selecting the Adobe PDF printer (because it makes smaller files than the Microsoft PDF maker add-in and a good deal of the families in the school are still on dial-up), copying and pasting a field on the form into the Adobe naming dialog box (the field might read SmithJohnPermissionSlip200 8-09SY), and saving the pdf to the folder I need it in. I do these one at a timeI dont need to change thatbased on reports that open/print because of a single student record I have open on the contact form.
Thank you so much for reading! I know it was long, but I hope I included all of the pertinent information.
SOFTWARE INVOLVED: MS Access 2007, Adobe Acrobat Professional 8, and I also have the MS Print to PDF add-in that came out with Office 2007.
BACKGROUND: I am the registrar for a teeny-tiny, private school. I made an Access database to maintain student grades and contact information (which means I am the IT department, too). I would rate my "skills" with Access at about intermediate to low-intermediate. I have had no formal training and can only figure out VB tweaks if the instructions are explicit.
ISSUE: In an effort to scale back costs I have started uploading reports with specific names as PDF files to our website for the parents to view/print. I am looking for a way to do the following in one click of a button: 1) print the report with my Adobe PDF printer, 2) name the resulting PDF with the student whose record is open on the form (so, DoeJaneReportName2008-09SY
Thank you so much for reading! I know it was long, but I hope I included all of the pertinent information.
I have heard that MS Access 2007 natively supports pdf. If that is the case, all you need to do is launch the Access report in preview mode and store the report name in variable, for example strAccessRptName.
Then, put the following code behind a button.
Private Sub YourButton_Click()
DoCmd.OutputTo acOutputReport, strAccessRptName, acFormatPDF, "c:PDF Reports\" & strAccessRptName & ".pdf", False
End Sub
Then, put the following code behind a button.
Private Sub YourButton_Click()
DoCmd.OutputTo acOutputReport, strAccessRptName, acFormatPDF, "c:PDF Reports\" & strAccessRptName & ".pdf", False
End Sub
If you already have Adobe installed, and Access's native pdf add-in does not work, try using the ShellExecute method. It may not give you everything you want, but it is easy to implement and has to be better than what you have now.
The following example uses ShellExecute on a button click event. You need to replace the strPdfWriterPath with the path to the Acrobat.exe on your machine.
Private Sub PrintPdf_Click()
Dim strPdfWriterPath As String
Dim strFilePath As String
'Open File
'strPdfWriterPath is the path to the application object
'strFilePath is the path to the file you want to open
strPdfWriterPath = "C:\Program Files\Adobe\Acrobat 5.0\Acrobat\Acrobat.exe"
strFilePath = Me.FilePath
Call Shell(strPdfWriterPath & " " & strFilePath, vbMaximizedFocus)
End Sub
The following example uses ShellExecute on a button click event. You need to replace the strPdfWriterPath with the path to the Acrobat.exe on your machine.
Private Sub PrintPdf_Click()
Dim strPdfWriterPath As String
Dim strFilePath As String
'Open File
'strPdfWriterPath is the path to the application object
'strFilePath is the path to the file you want to open
strPdfWriterPath = "C:\Program Files\Adobe\Acrobat 5.0\Acrobat\Acrobat.exe"
strFilePath = Me.FilePath
Call Shell(strPdfWriterPath & " " & strFilePath, vbMaximizedFocus)
End Sub
ASKER
fcfang: Thank you for the resource! Those links have a lot to explore.
puppydogbuddy: Wow, it looks so simple. I will try your solution this evening and get back to you.
puppydogbuddy: Wow, it looks so simple. I will try your solution this evening and get back to you.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Bethany,
One more thing that just came to my mind.....if the pdf for Access 2007 is an activeX add-in and not a native Access object (as I was told), you may have to set a reference to MicrosoftPdf or something similar in the VB library references before Access can expose the pdf object and properties for you to use.
pdb
One more thing that just came to my mind.....if the pdf for Access 2007 is an activeX add-in and not a native Access object (as I was told), you may have to set a reference to MicrosoftPdf or something similar in the VB library references before Access can expose the pdf object and properties for you to use.
pdb
Hi Bethany,
If you want to use PDF995 (which I would recommend) you will have to do some fiddling with the PDF995 INI file to get it to write to a different file name each time.
I've attached the code I used last time I worked with this utility.
I tried Acrobat and found I couldn't completely automate the printing process (at that time, a couple of years ago).
Have you considered privacy of the pupils and parents? If you are really uploading a file like that then anyone who can figure out the system (ie the pupil's classmates) can download any other pupil's report.
You might be better emailing each report as an attachment.
Hope this helps!
If you want to use PDF995 (which I would recommend) you will have to do some fiddling with the PDF995 INI file to get it to write to a different file name each time.
I've attached the code I used last time I worked with this utility.
I tried Acrobat and found I couldn't completely automate the printing process (at that time, a couple of years ago).
Have you considered privacy of the pupils and parents? If you are really uploading a file like that then anyone who can figure out the system (ie the pupil's classmates) can download any other pupil's report.
You might be better emailing each report as an attachment.
Hope this helps!
Declare Function nWritePrivateProfileString Lib "kernel32" Alias "WritePrivateProfileStringA" (ByVal lpApplicationname As String, ByVal lpKeyname As Any, ByVal lpString As Any, ByVal lpFileName As String) As Long
Sub PrintToPDF(r As Report, fn As String)
Const PROC_LOC = 9192
On Error GoTo proc_err
Dim sPDFINI As String
Dim s As String
Dim i As Integer
Dim t0 As Long
Dim stg As String
stg = "Getting location of INI file"
sPDFINI = "c:\pdf995\res\pdf995.ini"
stg = "Setting Printer for report " & r.Name
r.Printer = Printers("PDF995")
nWritePP fn, sPDFINI, "Parameters", "Output File"
stg = "Opening report " & r.Name
Screen.MousePointer = 11
DoCmd.OpenReport r.Name, acViewNormal
t0 = Timer
stg = "Waiting for report to print "
For i = 1 To 10
DoEvents
Next
For i = 1 To 10
If Len(fn) > 0 And Len(Dir(fn)) > 0 Then
Exit For
End If
If Timer - t0 > 10000 Then
If vbYes = MsgBox("PDF " & fn & " not created yet, continue waiting?", vbYesNo) Then
t0 = Timer
Else
Exit For
End If
End If
DoEvents
Next
stg = "Finished waiting for report to print to " & fn
i = MsgBox("PDF " & fn & " created: do you want to view it?", vbYesNo)
If i = vbYes Then
ShellExecute 0, "open", fn, "", "", vbNormalFocus
Else
End If
stg = "Resetting INI file"
proc_exit: 'Single exit point
stg = "Exiting"
Screen.MousePointer = 0
Exit Sub
proc_err: 'Error trapping
If Err = 5 And stg Like "Setting Printer*" Then
'Your cases here
MsgBox "Couldn't set printer to PDF995. Is PDF995 installed on this workstation?" & stg
GoTo proc_exit
Else
End If
msgbox error
resume proc_exit
End Sub
Function nWritePP(ByVal Value As String, ByVal AppName As String, ByVal Section As String, ByVal Key As String) As Integer
On Error GoTo nWritePP_Err
If UCase$(AppName) Like "*.?*" Then
Else
AppName = AppName & ".ini"
End If
nWritePP = nWritePrivateProfileString(Section, Key, Value, AppName)
nWritePP_Exit:
Exit Function
nWritePP_Err:
MsgBox "Error: " + Error$, 0, "nWritePP"
Resume nWritePP_Exit
End Function
ASKER
plodernator:
Thank you, too! I will fiddle around with this as well.
We do have privacy measures on our site for the parents. I have been emailing documents to the parents but they often delete them and then request them from me again (*sigh*) so we set up private document libraries for each family on our private site so they have access to the documents for six months or so.
One of these days I hope to migrate our database to the web with SQL or the like and have the parents access records & documents as needed but I have many classes to take before I'll have the skill to do that (the development costs of outside hires are often 1/3 of our annual gross. Yikes!)
Thank you, too! I will fiddle around with this as well.
We do have privacy measures on our site for the parents. I have been emailing documents to the parents but they often delete them and then request them from me again (*sigh*) so we set up private document libraries for each family on our private site so they have access to the documents for six months or so.
One of these days I hope to migrate our database to the web with SQL or the like and have the parents access records & documents as needed but I have many classes to take before I'll have the skill to do that (the development costs of outside hires are often 1/3 of our annual gross. Yikes!)
ASKER
Okay, here is what I did and it works like a charm!
On the open form I made a field named "FormName" and the control source:
=([Student_LName] & [Student_FName] & "QtrRptFrm" & [RptCrdStudentID] & "_" & (Format([FormDate],"yyyy-m m-dd")))
Then, I made the button and on click it runs the following code:
Private Sub Command184_Click()
DoCmd.OutputTo acOutputReport, "REPORT_GradeReport_HS", acFormatPDF, "C:\Users\MyName\Desktop\P DF Reports\" & Forms!ReportCardsF!FormNam e & ".pdf", False
End Sub
In the folder I specified I get the following PDF:
DoeJaneQtrRpt12345_2008-06 -13.pdf
Is there any reason why I shouldn't keep taking this route? I was in over my head with the modules and .ini files. The other was was rather easy.
Oh, you have no idea how happy I am. Puppydogbuddy, you were right on, thanks so much! Thank you plodernator and fcfang for taking the time out to review my plight.
Well, I am bleary-eyed and exhausted. Sweet dreams, all!
On the open form I made a field named "FormName" and the control source:
=([Student_LName] & [Student_FName] & "QtrRptFrm" & [RptCrdStudentID] & "_" & (Format([FormDate],"yyyy-m
Then, I made the button and on click it runs the following code:
Private Sub Command184_Click()
DoCmd.OutputTo acOutputReport, "REPORT_GradeReport_HS", acFormatPDF, "C:\Users\MyName\Desktop\P
End Sub
In the folder I specified I get the following PDF:
DoeJaneQtrRpt12345_2008-06
Is there any reason why I shouldn't keep taking this route? I was in over my head with the modules and .ini files. The other was was rather easy.
Oh, you have no idea how happy I am. Puppydogbuddy, you were right on, thanks so much! Thank you plodernator and fcfang for taking the time out to review my plight.
Well, I am bleary-eyed and exhausted. Sweet dreams, all!
ASKER
All the extra minutes in my work day from here on out will be gratefully dedicated to you :) Thank you sooo much for your time!
http://www.mvps.org/access/reports/rpt0011.htm
Here's another link of resources for your question from other apps. Most recently I have been using PDF995 to do the PDF creation.
http://www.granite.ab.ca/access/pdffiles.htm
Hope it helps.