access print report to pdf vba

30 day money back guarantee

How to Export Your Access Report to a PDF File via VBA

Learn how you can create a VBA function that will allow you to save your Access Report as PDF with just a click of a button. No need to go to the Access Ribbon every time!

• Open the report.
• Go to the Access Ribbon: External Data > PDF or XPS.
• Select the filename and folder path where you would like it to be saved.
• Click Publish and you’re done.

Easy, right? However, if there is a need for you to repeatedly export reports, say on a daily basis, this can be a bit taxing.

Don’t worry. There’s an easier way. With just one click of a button, your report can be immediately exported to your designated folder. This can be done with the help of VBA. Just follow the steps below.

Download Now

If you want to start to use the feature as soon as possible, then you can:

Download the Sample Database with VBA Codes Now

Otherwise, if you want to DIY, you can read the contents below.

Steps to Creating a VBA Function that Saves Access Report as PDF File

1. add a command button inside your report.

Rename the button as cmd_exportPDF . To do this:

• While in design view, select the button.
• Open the Property Sheet (ALT + Enter).
• Go to the Other tab > Name.
• Change the Name value to cmd_exportPDF.

2. Add code for the command button

To add the code, you have to first add an On Click event to the button.

• With the command button selected, go again to the Property Sheet.
• Click on the Event tab.
• Look for On Click and select …
• When the Choose Builder menu pops up, select Code Builder and click OK.
• This will open the VBA Editor.

Now, copy the code below.

Don’t forget to update values for filename and fldrPath. They each have asterisk (*) in the comment section.

These lines of code will:

• First check if a PDF file with the same filename already exists in the specified folder. If it does, a prompt will appear asking if you would like to replace it with a new one.

If you select yes, the report will continue with the export process. If not, the file will not be converted to PDF.

• If you entered a folder path that does not exist, a prompt will appear to ask you to update the folder path specified in the code. Export process will not continue until this is corrected.
• Once the report has been successfully exported, a prompt will appear indicating where the PDF file was saved.

That’s it! Now it’s time to test your button.

Unable to Open Access Database

The most terrifying thing that can happen to any Access user is to wake up one day and realize that the database could no longer be opened. It has been corrupted. What’s even worse is that you realize that you were not able to back up your data. Don’t worry. DataNumen Inc. created a software that can fix Access database. Your database can still be brought back to life.

Author Introduction:

Jayme Stack is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including sql recovery and excel recovery software products. For more information visit www.datanumen.com

9 responses to “How to Export Your Access Report to a PDF File via VBA”

Wonderful blog! Do you have any recommendations for aspiring writers? I’m planning to start my own website soon but I’m a little lost on everything. Would you advise starting with a free platform like WordPress or go for a paid option? There are so many options out there that I’m completely confused .. Any ideas? Thanks!

This was very useful for exporting a full file. Do you have sample code that would allow you export an report to multiple PDFs based on a grouping?

Hello I need this help, I can’t run using the query(qryCliente) to export only the list of customers. Only the nameCliente field. In the .docx file I have the Bookmark of the customer nameCliente field.

Error: Undefined variable

Public Sub ExportToWord() Dim wApp As Word.Application Dim wDoc As Word.Document Dim rs As DAO.Recordset

Set wApp = New Word.Application Set wDoc = wApp.Documents.Open(CurrentProject.Path & “\DBClientes\listCliente.docx”) Set rs = CurrentDb.OpenRecordset(CST_CLIENTE, “nameCliente”)

If Not rs.EOF Then rs.MoveFirst Do Until rs.EOF wDoc.Bookmarks(“nameCliente”).Range.Text = Nz(rs!nameCliente, “”)

wDoc.SaveAs2 CurrentProject.Path & “” & rs!ID & “_listCliente2.docx”

Set wDoc = wApp.Documents.Open(CurrentProject.Path & “\listCliente2.docx”) If Not wDoc Is Nothing Then MsgBox “open file!” Else MsgBox “file not open!” End If

wDoc.Bookmarks(“nameCliente”).Range.Delete wdCharacter, Len(Nz(rs!nameCliente, “”))

rs.MoveNext Loop

wDoc.Close False

wApp.Quit Set wDoc = Nothing Set wApp = Nothing Set rs = Nothing

Thanks for posting this. I’ve tried this code, making the asterisked changes, but I keep getting the message saying the file path is invalid. It’s definitely correct. Can you help me understand what I’m missing? Thanks very much.

Another question: my report has definitely only one page. But the export (with the commands as shown above) has 3-4 pages, only the first page shows the page from MSAcess, the rest is empty. How comes? How can I avoid the empty pages?

Many thanks too – through this answer, my problem solved.

This is very helpful. However, I do have another issue. The report I have contains multiple records. Is there something I can add that will tell it to cycle through a particular table and look for a “Plan number” and publish each of the records to separate pdf files. and name it according to a field in the same table? Instead of having one report that contains all the records.

Many thanks for this post – very useful

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

VBA to open Access report and print it to a PDF

Starting with Access 2007, Access has a built-in easy way to export an Access report to a PDF file:

Public Sub PrinttoPDF(strFilePDF As String)

Dim RptName As String

RptName = "rptReport"

DoCmd.OpenReport RptName, acViewDesign, , , acHidden

DoCmd.Close acReport, RptName, acSaveYes

DoCmd.OutputTo acOutputReport, RptName, acFormatPDF, strFilePDF

Printing Access Reports To A PDF File

• Categories: Access , PDF Creator , VBA

This article contains code examples to print a Microsoft Access report to a PDF file.

This code examples is built for PDFCreator, an open source PDF writer utility. Unlike Adobe Acrobat and CutePDF, which both require pro versions to create PDF's via code, PDFCreator is completely free! Download PDF Creator from Sourceforge here . Please note that this code will NOT work with Adobe Acrobat.

It should also be noted that this code example uses an Early Bind. If you are not familiar with the difference between Early and Late Binding, please read our article on Early vs Late binding .

Versions Tested: This routine was tested successfully using PDFCreator 0.9.1, GPLGhostscript.exe download package, on Windows XP Pro (SP2), using Microsoft Access 2003.

NOTE: Before you "go it alone" with trying to adapt this routine, you may want to read this article , which shares some of the idiosyncrasies discovered in the development of the PDFCreator code samples.

Code Required: This code goes in a standard module. You will need to set a reference to PDFCreator, and change the name of the report. You may also want to update the directory to save the file into (sPDFPath), as it will currently save the file into the database's directory.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed .

Latest Posts

Proving excel’s box and whisker chart calculations.

Now I know that not everyone is going to want to do this, but one of the things that I find which really helps me understand complex calculations or visuals,

Building a Box and Whisker Plot in Excel

Introduction I’m a big fan of building Box and Whisker plots (also known as Box plots to those in the statistics world) to interpret certain data sets. While I tend

Reading Excel’s Box and Whisker Chart

Introduction I’m a big fan of Excel's Box and Whisker chart (also known as Box plots to those in the statistics world). While they can be used in a variety

Convert Julian Dates to Gregorian Dates with Power Query

The question of how to convert Julian dates to Gregorian dates using Power Query came up today in a course I was teaching. Although I haven't ever used JD Edwards

Get Album and Track Data from Spotify

After writing up my article on connecting to Spotify using Power Query, I thought it would be helpful to provide some functions that will allow you to get album and

Connect to Spotify using Power Query

Spotify has a massive music database, and in this article we're going to look at how to connect to Spotify using Power Query. Step 1: Get API credentials from Spotify

• Monkey Tools
• Privacy Policy

Copyright © 2022-present Excelguru Consulting Inc. All rights reserved.

• [email protected]
• British Columbia, Canada

• Search forums
• Microsoft Access Discussion
• Modules & VBA

Solved   Saving separate reports to PDF using VBA

• Thread starter neilsolaris
• Start date May 24, 2021

neilsolaris

• May 24, 2021

Enthusiastic Amateur

Walk through the code with F8. I am viewing on phone, but it looks correct to me.?  

Ok, is Invoice Number text?, if so it needs to be surrounded by ' at the least. Using the immediate window and Intellisense I get Code: Copy to clipboard docmd.OpenReport "rptHours",acpreview,,"employeeid=" & 4,acWindowNormal and it works fine.  

Uncle Gizmo

Nifty access guy.

Generate Multiple Reports - Nifty Access

Gasman said: Ok, is Invoice Number text?, if so it needs to be surrounded by ' at the least. Using the immediate window and Intellisense I get Code: Copy to clipboard docmd.OpenReport "rptHours",acpreview,,"employeeid=" & 4,acWindowNormal and it works fine. Click to expand...

Uncle Gizmo said: I blogged an answer to a specific question on MS Access world forums on my website here:- Generate Multiple Reports Generate Multiple Reports - Nifty Access Generate Multiple Reports This blog came about because of an answer I gave to a particular OP's question. Therefore the code isn't generic, it is designed for a specific situation, like most examples! However I hope there's enough information here for you to follow what's happening. Multiple... www.niftyaccess.com It might provide you some inspiration, or possibly perspiration! Click to expand...

Ok, I feel like I'm making a little progress. My invoice numbers run from 1064 to 1088. As an experiment I changed the above line of code to... Code: Copy to clipboard DoCmd.OpenReport "R_Invoices_PDF", acViewPreview, , "Invoice_Number" = "1087", acHidden What happens now is it loops from 1064, 1065 etc (I stopped it at this point), and it saves PDF invoice files (1064 & 1065) to the correct location. However, when I open the PDF files, they are blank (when I say blank, the labels are the, as is the logo, page footer etc, just not the invoice data). Is there anything obvious I'm doing wrong? Thanks again.  

No, that will not work Look at what I typed in the immediate window to concatenate the criteria value to the criteria.? Plus those are not text, but numeric, so why have them as text? Plus if you have spaces in a field you need to surround that with [], so I am surprised anything came out TBH Try Code: Copy to clipboard DoCmd.OpenReport "R_Invoices_PDF", acViewPreview, , "[Invoice_Number] = '" & "1087" &"'", acHidden So get the criteria correct, then we will worry about the data, but that is the likely cause of no data on the reportI would have thought? Edit: You had the field correct in the original code? Code: Copy to clipboard DoCmd.OpenReport "R_Invoices_PDF", acViewPreview, , "[Invoice_Number]=" & ![Invoice_Number], acHidden so try Code: Copy to clipboard DoCmd.OpenReport "R_Invoices_PDF", acViewPreview, , "[Invoice_Number]='" & .[Invoice_Number] & "'", acHidden  

Thanks Gasman. I can't remember exactly why I originally decided to put the invoice numbers in as text, but it was probably in case I wanted to insert, say, 1087.5 for example, but I didn't want the other invoices showing as 1087.0, if that makes sense. But probably I won't have any reason to insert a non-integer invoice, so I could probably change that field to numbers now. Shall I change the field type now? I just don't want to complicate things while you've already suggested code for a text field. I tried the codes you suggested. Code: Copy to clipboard DoCmd.OpenReport "R_Invoices_PDF", acViewPreview, , "[Invoice_Number] = '" & "1087" &"'", acHidden That one works, except that it saves all of the invoices in a single PDF file. I pressed F8, and stopped before the loop. Code: Copy to clipboard DoCmd.OpenReport "R_Invoices_PDF", acViewPreview, , "[Invoice_Number]='" & .[Invoice_Number] & "'", acHidden This one brings up a compile error "Method or data member not found". Should there be a full stop before the 2nd "Invoice_Number" perhaps? I'm not sure how to test it with the intermediate window, but I'll see if I can work it out. Thanks again.  

Hi Gasman, Sorry for my slow reply, I just got back home now. What I've done is attached a practice copy version of my database (just containing a small sample of the invoice and supplier table data). Is that more helpful? By the way, if you wanted to play around with it, feel free to change the data type of the invoice number to a number. I think that would be better, as you said. Thanks again for your help.  

Attachments

• PracticeCopy.zip 174.1 KB · Views: 343

Well one thing I was thinking of is, that if you leave it as alpha, 1 and 11 through 19 will sort before 2 and the same with the other numbers? So if you need them sorted in numerical order, best to have a number.?  

Gasman said: Well one thing I was thinking of is, that if you leave it as alpha, 1 and 11 through 19 will sort before 2 and the same with the other numbers? So if you need them sorted in numerical order, best to have a number.? Click to expand...

That's interesting, but I'm sure mine are in numerical order, even though it's set as text. But I'll double check though when I'm back on my computer. Anyway, I might as well set it to number format though.  

That's weird, it's working fine for me now as well! I don't know why it wasn't working before. By the way, if I go ahead and change the data type to number, can I copy one of your above lines? Thanks for your help.  

Gasman said: See this. I added to invoices manually into table View attachment 91813 I then created a query Code: Copy to clipboard SELECT T_Invoices.Invoice_Number FROM T_Invoices ORDER BY T_Invoices.Invoice_Number; Result View attachment 91814 Just remove the single quotes from rs!Invoice_Number Code: Copy to clipboard DoCmd.OpenReport "R_Invoices_PDF", acViewPreview, , "[Invoice_Number]=" & ![Invoice_Number], acHidden Click to expand...

While you are at it, test for EOF before the MoveFirst, in case you ever get an empty recordset.  

Similar threads

• Nov 24, 2023
• Dec 30, 2023

• Jan 14, 2024

• Jan 29, 2024
• Nov 20, 2023

Users who are viewing this thread

How to automatically print out an Access Report using VBA

Applies to:  access 2007, 2010 (untested in earlier versions).

The following MS Access tutorial shows you how you can use VBA to create a button to print out a MS Access report and how to print out an MS Access report automatically based on a specific data.  Both of these tasks can of course be done easily using a macro; however the benefit of being able to do them using VBA is that you can build them into other functions that you may be writing.

This MS Access tutorial assumes that you have created a report called rptCustomerInvoice that you want to print out automatically.  You can of course change the invoice name to the name of your own report.

1. Create your button on your form and call it btnPrintDoc

2. With your new button selected click in the tools section of the “ Design ” tab click on the item called “ Property Sheet ”

3. Select the “ Event Tab ”

4. On the “ On Click ” property click on the little black down arrow / triangle

5. Select “ Event Procedure ”

6. Click on the three dots next to the little black down arrow / triangle – this will open up the VBA window and you should see the following:

Private Sub btnPrintDoc_Click()

7. On a new line directly between the two lines above add the following:

DoCmd.OpenReport  “rptCustomerInvoice”, acViewPreview

DoCmd.PrintOut , , , , 1

(the number 1 is the number of copies to print)

8. Save your changes and test your button.  Your report should now open in preview mode and automatically print.

9. If as with an invoice you want the report to only print data for a specific customer you can replace the code in item 7 with the following:

DoCmd.OpenReport “rptCustomerInvoice”, acViewPreview, , “invoice_id = ” & Me.invoice_id

(the above assumes that your report is based on a table or query that has a field called invoice_id and that the form your button is located on also has a field called invoice_id).

10. Finally if the report you want to print can differ depending on the circumstances, you can replace the report name with a string variable as demonstrated in the code below:

Dim strDocName as String

If Me.Dealer = True Then

strDocName = “rptDealerInvoice”

DoCmd.OpenReport strDocName, acViewPreview, , “invoice_id = ” & Me.invoice_id

DoCmd.PrintOut , , , , 2

strDocName = “rptCustomerInvoice”

Please feel free to contact us at [email protected] for help or advice on any aspect of this tutorial or MS Access in general, we love feedback and are always happy to help.

DEVelopers HUT

Ms access – print individual pdfs of a report.

Here’s another common request.

Say you have a contacts report and like to print each client’s individual report separately as a PDF. How can this be done exactly? A concrete example might be a billing database in which you have an Invoice report (which would be comprised of all the records) and you’d like to print each invoice separately. Or just the outstanding invoices… you get the idea.

Generating PDFs on the Hard Drive

Approach 1 – individual reports, the general concept.

The general concept is pretty straightforward:

• Defined a recordsource that you’re going to use to filter the report to get those records you seek
• Open the report filtered based on the above
• Print the report as a PDF
• Close the report
• Repeat for each record in 1

A Concrete Example

Enough with the blah blah, below is some sample code to inspire yourself from

Approach 2 – A Single Filtered Report

• Open the report to include all the records
• Apply a filter to limit the report
• Repeat 3 & 4 for each record in 1

I haven’t had a chance to validate which approach is fastest. I suspect the 2nd approach would be best.

Some Important Things to Notes

What’s important to understand is that by controlling the line

You control which pages of the report you generate as PDF.

Say you wanted only the Active Contacts you might do

Or you wanted just the Canadian client

You get the idea.

Using This Technique With SendObject

You can adapt the above to even work with SendObject. By iterating over the recordset, we can then simply change the caption for each e-mail.

11 responses on “ MS Access – Print Individual PDFs of a Report ”

THX for the code. I want every distinct pdf to have a specific name ex. 1.pdf , 2.pdf, etc. The solution I came up with is DoCmd.CopyObject , M_distinct_name, acReport, M_original_report_name. DoCmd.OpenReport M_distinct_name, acViewPreview, , M_where Is there an other solution?

Second question: I would like to store the pdf in a SQL-database. Anybody who has experience with this?

Kind regards, Geert.

codecabinett.com means to that:

open the report first (hidden) then print as pdf

Public Sub ExportFilteredReportToPDF()

Dim reportName As String Dim fileName As String Dim criteria As String

reportName = “rptYourReportName” fileName = “C:\tmp\report_export_file.pdf” criteria = “SomeTextField = ‘ABC’ AND SomeNumberField = 123”

DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName DoCmd.Close acReport, reportName, acSaveNo

End Sub mfg Klaus

You are great! I’ve resolved my problem with this script that works very well. Thank you very much!

How do I loop this code based on a single entry start and end date?

It’s kind of hard to give you an answer without context here, but you should be able to simply alter the SQL statement to include whatever WHERE clause filters your data as you need it to be.

SELECT ContactID, FirstName FROM Contacts WHERE [ActivationDate] BETWEEN #2000-01-01 00:00:00# AND #2022-08-12 14:30:00#

Basically, build a normal query that gives you the results you are after and then simply use it in the function.

Thank you for the response, let me see if I can explain it better. I currently have a report and in the query I have it asking for the date range criteria “Between [Start Date] And [End Date]” in the “HQ Posted” field. This works when you run the repot normally but when I plug the report into the code above and run it that way it asks for the criteria on every loop. I’d like to set the criteria within the code instead of the query and have it run the loops retaining the single input range.

Here is the current code

Now I get the issue, and I should have coded for that. The issue is explained in my article MS Access – VBA – Run Parameter Query in VBA . Implement that approach and everything should run smoothly.

Now to learn how to do that next, Sadly I know enough VBA to be dangerous.

Do I run that in the current sub before the rs or within it?

Try something like:

or, your other choice would be to alter the underlying query itself, so it use Eval() directly eliminating the need to do this in VBA.

Good luck, I’ve gotta run.

Thanks for posting that sample code. I was able to get things working in my access database with only a couple of minor tweaks. I was initially having some troubles getting the filter to work, but when I switched from using a text value over to an integer number value for the record ID, that seem to make all the difference.

I am having a little bit of trouble still with getting the sub reports to show up in the PDF files that get pushed out to my network drive. My main report is seven pages long, and there are sub reports on each of the individual pages that display or hide based on a variable on the main report. It all works properly when I preview the print job in Access, but now when I’m pushing them out as individual PDF files, the sub reports don’t show up.

DevHut is provided graciously by CARDA Consultants Inc. © 2007-2024 – Daniel Pineault – All Rights Reserved All content may only be used by live human beings. This site does not authorize any Automated and/or AI content harvesting.

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

DoCmd.PrintOut method (Access)

• 8 contributors

The PrintOut method carries out the PrintOut action in Visual Basic.

expression . PrintOut ( PrintRange , PageFrom , PageTo , PrintQuality , Copies , CollateCopies )

expression A variable that represents a DoCmd object.

Use the PrintOut action to print the active object in the open database. You can print datasheets, reports, forms, data access pages, and modules.

The following example prints two collated copies of the first four pages of the active form or datasheet.

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Was this page helpful?

Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback .

Submit and view feedback for

Additional resources

Microsoft Learn Q&A needs your feedback! Learn More

May 20, 2024

Microsoft Learn Q&A needs your feedback!

Want to earn $25 for telling us how you feel about the current Microsoft Learn Q&A thread experience? Help our research team understand how to make Q&A great for you.

Find out more!

Contribute to the Microsoft 365 and Office forum! Click  here  to learn more  💡

April 9, 2024

Contribute to the Microsoft 365 and Office forum!

Click  here  to learn more  💡

Access Forum Top Contributors: GroverParkGeorge  -  Tom van Stiphout  -  Ken Sheridan  -  Scottgem   ✅

May 10, 2024

Access Forum Top Contributors:

GroverParkGeorge  -  Tom van Stiphout  -  Ken Sheridan  -  Scottgem   ✅

• Search the community and support articles
• Microsoft 365 and Office
• Search Community member

Ask a new question

Access report saved as pdf file using vba

I normally use the export function to save reports to a specific location but I need to use vba this time.

I'm not sure how to go about making a module that will save the report I have to a specific location with a specific name and in the name have the current date, and save as a pdf.

I know the date part. "folder directory\Name" & format(today()), "MMDDYYYY")  & ".pdf"

Report abuse

Reported content has been submitted​

For example:

Create a form.

Place a command button on the form, and name it cmdExportReport.

With the command button selected, activate the Event tab of the Property Sheet (press F4 if you don't see the Property Sheet).

Click in the On Click event and select [Event Procedure] from the dropdown menu.

Click the builder dots ... to the right of the dropdown arrow.

Make the code look like this:

Private Sub cmdExportReport_Click()     DoCmd.OutputTo acOutputReport, "rptMyReport", acFormatPDF, _         "folder directory\Name" & Format(Date, "mmddyyyy")  & ".pdf" End Sub

where rptMyReport is the report you want to export to PDF. Make sure that you supply a valid folder path.

2 people found this reply helpful

Was this reply helpful? Yes No

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

Thanks for your feedback.

Replies (2) 

Question info.

• Norsk Bokmål
• Ελληνικά
• Русский
• עברית
• العربية
• ไทย
• 한국어
• 中文(简体)
• 中文(繁體)
• 日本語