We help IT Professionals succeed at work.

Access 2003:  Save report as pdf file

5,594 Views
Last Modified: 2013-11-28
Hi EE,

I need to save report to pdf, and name the pdf based on the student's name.....

found this post on EE
https://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Reports/Q_23225253.html?sfQueryTermInfo=1+pdf+report+save

Would like to do the same thing.

Copied the code in the link mentioned in the above post:
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=35321&lngWId=1

into a public module.

Then I ran:
      Public Sub test()
             Call RunReportAsPDF("rpt_Test", "C:\ARG_09_M4_Graph\", "test.pdf")
      End Sub

Report went to printer.....

Do I have to modify any of the values for XP, and my adobe printer name is "Adobe PDF".
Pasted in code window below the default values:

Are there other techniques to saving access report as pdf.
Also, my access report uses criteria from a form, it's not just a static report....

Your advice is deeply appreciated, tx, sandra





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 (ex. - "c:\data\
    '     ")
    'sPDFName = the name of the pdf file you
    '     are
    'wanting to create (ex. - "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 re
    '     gistry stops file dialog box from appear
    '     ing
    bSetRegValue HKEY_CURRENT_USER, "Software\Adobe\Acrobat PDFWriter", "PDFFileName", sPDFPath + sPDFName
    'Run the report
 
 
    DoCmd.OpenReport rptName, acViewNormal
Exit_RunReport:
        ' Restore default printer
        bSetRegValue HKEY_CURRENT_USER, "Software\Microsoft\WIndows NT\CurrentVersion\Windows", "Device", sMyDefPrinter
        Exit Function
Err_RunReport:
        MsgBox Err.Description
        Resume Exit_RunReport
    End Function

Open in new window

Comment
Watch Question

CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
MIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Good morning,

Thank you Everybody for writing,

I need to try your ideas and get back to you soon,

Re: saving report to pdf
====================

One thing, I would like to say, is late yesterday, when I ran the code from  the link from Planet source
is that I think it shut off my default printer setting bec.

I have a vba script that runs in the morning via Windows scheduler to print an Excel spreadsheet,
and when I came in the morning, the code was paused in the excel spreadsheet waiting for me
to choose a printer.

I went to control panel, and chose my default printer, and my script ran fine.

At home, I was thinking, that I am not sure if the planet source code does a save as to pdf file,
or just converts and displays the pdf file on the screen.

If the planet source code does not do a save as to pdf, I could always tinker with with the SendKeys
to save as, but maybe Jeff is providing an alternate approach, so I have to look into Jeff's approach
this morning....

Re: customizing file name
====================
Helen, thx for the code

Helen and Jeff:
===============
I know this is off topic and I plan on opening a new post.
Can I please let you know when I post the question?

It relates to printing pictures:
=======================
I need to print a report that is 4 pictures across and 4 pictures down.
Currently the user uses Merge feature in Word. User created a doc with a table to hold pictures.
User told me that Word itself cannot merge pictures into a Word doc so they purchased a
Word AddIn call OnMerge.

I'm looking for alternate approaches to produce a "photo album"  by clicking a button on an Access form.

Many years ago, I copied from Helen's website vba to print Word labels.
I am aware that in Access I can print columnar reports.
Our pictures reside in a server folder not in a database.

So since Helen replied here, and Jeff has broad knowledge, I wanted to "flag" you guys here,
if I can alert you when I post this question.

tx and will  get back to you on the above ideas, tx, s

Author

Commented:
Hi Kelvin,

I followed your suggestion and changed printer name to "Adobe PDF" bec. that's what's mentioned
in Control Panel/Printers.

I ran:
Call RunReportAsPDF("rpt_Test", "C:\ARG_09_M4_Graph\", "test.pdf")

and got error msg in the screen shot below.... any thoughts...?
ab-PrinterMsg.GIF

Author

Commented:
Hi Jeff,
thx for your instructions - i go tthe pdfs to print, thx so much!

... i do have questions, though, i'd like to describe my actions so far for any future EE reader:

a) downloaded you mdb to myfolder
b) downloaded zip file    to myfolder
c) extracted dll                to my folder
d) created a folder in c:\  called "YourFolder"
e) changed mdb startup setting to NONE for form, so that form does not show automatically when open
    mdb
f) click on form button, getting the error msg for msgbox, so I commented out the last line
       MsgBox "There was an error executing the command." _
    & vbCrLf & vbCrLf & "Error " & Err.Number & ": "
    '& vbCrLf & vbCrLf & "Error, vbExclamation

g) then got an error msg for Space command, pls see below
     so I went to vba/tools/references   and saw that next to Outlook it says MISSING
     so I unchecked OUTLOOK, bec. we use Lotus Notes here
h) then clicked on button, and saw pdfs flash, and msgbox of Done
i)  checked YourFolder and see 2 pdfs
j) thank you for customizing an mdb for me with a DAO loop for student table which is what I'll have
   to do, as well

k) quest, pls:   and pls advise if you'd like new posts for these quests:
=========================================================
    a) do you keep your pdfs in a sep folder like Your folder   or as a subfolder in the folder that
        your mdb is located in
    b)  what does "avoids the endless loop" mean in this code snippet window?
    c) In second screen shot, you see components of mdb
        1) I need to import the Lebans pdf logic into an exisitng mdb
              do i import the modules that are named "mod"
        2) Is the class module your generic routines that you use for your projects that is not
            required for me to import into my mdb?
        3) I will copy and adjust the code behind the form's button window
     
Pls advise on (k) and if you want (k) in a sep. post,
tx, s
           
   
'Recordset Cleanup Code
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
 
Exit_cmdEmailIndividualCustReports_Click:
    'Avoids the endless loop
    DoCmd.Echo True
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
    Exit Sub

Open in new window

ab-SpaceMsg.GIF
ab-Components.GIF
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi Jeff,

a) It looks like the code does not allow ** subfolder within **  a folder, do you agree?
IOW:
i tried: "C:\ARG_09_M4_Graph\ARG_09_M4_Graph_pdfs\"
                     instead of
           "C:\YourFolder\"
  and the pdfs did not go to subfolder.

if I did a different (you called it "root"?) folder like this: "C:\ARG_09_M4_Graph\"
       pdfs went there successfully.

I have another related question, relating to opening a report hidden?
Can I let you know when it is posted? tx, s

Author

Commented:
Thank you everybody for your help...s


Author

Commented:
Hi Jeff, new post related to this post based on your code
    can u look at if u have a minute? tx! s

https://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Reports/Q_24762463.html

Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
The code worked fine for me going to a subfolder.
Make sure you reference it in all places with the trailing" "\"

Also make sure you have rights/permissions to this subfolder.

Finally, check your "speeling"
;-)

Jeff

Author

Commented:
Thank you so much, Jeff...

You are right it was a typo.... I misspelled my sub-folder name

I imported all the components to my actual mdb and tested it there. Work fine!

I will be off on Mon. I will continue to adapt this solution for business needs.
    First  will work with creating pdf report for an individual student when chosen from combo box
    Then will create a DAO loop/script to create files for an entire class
                   (each student's pdf will live in a target folder)
                               thx again and have a nice weekend, s

mental note for next week... may open a post to ask if I can bulk print these pdf files, to give the user an option of printing them off the folder thru .bat file, scripting commands etc....

tx everybody, have a nice weekend, s



Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
Again, I would like to emphasize how impressed I was that you were able to do most of the work on your own.
Then to follow up by asking great questions that will help you understand how things are working.

So in honor of your efforts, I have updated my sample file and removed all references to "Email".
In this way future generations of askers here will benefit from your wisdom.
;-)

JeffCoachman
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
To adress your "printing PDF" concern.

Please remember that Printing the actual Report or the PDF from that report will result in the *exact same printout*.

It is *Far* easier to print the report than it is to print the pdf.

The main reason why you should need a pdf is that you need to "Give" someone an exact "snapshot" file of the data/report, at a particular time.

But again, I know how you are, ;-) , don't take my word for it, try it out for yourself.
Print the report, then run this code.
Open the PDF and print it from Adobe.
Compare the two...


Batch printing the reports only requires you to modify the code to loop the Report Open command:
DoCmd.OpenReport strReportName, acViewNormal, , "CustomerID=" & "'" & strCustID & "'"

;-)

JeffCoachman

Author

Commented:
Hi Jeff,

a) I was off on Mon. and over the weekend I was thinking of posting a revised mdb
(w/o Outlook reference and commenting out one of the lines of code in the msgbox at
 error handling)

This morning I see that you did the same for your sample mdb.
Perhaps you want to upload yours....

In the meantime, below is modified mdb.

Also for EE reader, is a screen shot of windows explorer. I highlighted the components
that you need (the rest of window explorerer you can ignore)

Mr. Lebans approach to pdf is wonderful and is quoted alot in EE. G-d bless him.
G-d bless Jeff for his very clear way of explaining how to use Mr. Lebans approach and for
Jeff's sample mdb which is very clear as well.

b) Jeff, will be back shortly to comment on your print-pdfs comment.
    I have to digest your remarks.

    tx, s

   
   


Access-BulkConvertIndividualCust.mdb
ab-Screen.GIF

Author

Commented:
Hi Jeff,

In June 2009, I asked a similar question FROM THE EXCEL side about printing the file and
saving as a pdf.

In that post you explained as you do in this post, that it's EASIER to print from EXCEL than to print
from pdf screen, after one has save Excel to pdf. I tried to print from pdf using Sendkeys and it
did not work properly, so I ended up printing spreadseeht using Excel print command.

So, without having testing printing Access report after it has been converted to pdf, I will agree
with you, that it's probably EASIER to print from within Access.

The reason for my quest. is a bit diff, and should open a new post:
======================================================

You see, at this time, my users are not really Access users.  This is an Oracle environment and
they use a very expensive web app. to do their work. I transferred from another dept bec. they
had an opening. I was working with Access 2000 in old dept.

So for their missing functionality, I am approaching with Access 2003.
One day, I may put all my functions on an Access menu and install on users machines.

Right now, I'm running off my pc.

And so:
=======
Once I get the DAO routine to work to bulk print to pdf files.
I will have 200 pdfs living in a folder on a server.

The user is used to going to server "for things" so if they need a student's report they can go to the
server.

I want to post, if there is a way to code a .bat file or script language to loop thru the folder
and print ALL 200 pdfs w/o the user doing manually, if adobe allows parameters for printing like
i'm making this up:
    shell c:\program files\adobe\adobe.exe   "pdf file name" -p  (where -p would be to print)

tx, s




Author

Commented:
oh Jeff,

this is the link that i forgot to post above where you advised about printing from Excel and not from
Adobe:  tx, s

https://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24530934.html

Author

Commented:

Author

Commented:
Hi Jeff,

I have a related question here:

https://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26485626.html#a33717762

If you have a minute, can you take a look, pls?

tx, sandra

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.