asked on

Access 2003: Save report as pdf file

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/questions/23225253/Save-report-to-PDF-in-access-2003.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

SOLUTION

Kelvin Sparks

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Helen Feddema

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mytfein

ASKER

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

mytfein

ASKER

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

mytfein

ASKER

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

SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mytfein

ASKER

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

mytfein

ASKER

Thank you everybody for your help...s

mytfein

ASKER

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/questions/24762463/Access-2003-Save-report-as-pdf-file-post-2.html

Jeffrey Coachman

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

mytfein

ASKER

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 Coachman

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 Coachman

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

mytfein

ASKER

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

mytfein

ASKER

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

mytfein

ASKER

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/questions/24530934/print-pdf-to-a-printer-picking-up-on-another-member's-post-of-the-last-week.html

mytfein

ASKER

Hi Everyone,

related post:
https://www.experts-exchange.com/questions/24770676/Is-it-possible-to-bulk-mass-print-a-windows-folder-containing-EXCEL-files-using-shell-bat-vba-vbs.html

tx, s

mytfein

ASKER

Hi Jeff,

I have a related question here:

https://www.experts-exchange.com/questions/26485626/Windows-path-question-creating-the-files-from-Access-2003.html?anchorAnswerId=33717762#a33717762

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

tx, sandra