469,317 Members | 1,905 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,317 developers. It's quick & easy.

Save Access Report Automatically after printing to PDF

Hi!

First off, bear with me; I've just started to learn VBA and this is my first post on this site. My question could be extremely basic, but I haven't been able to find anything via the internet that fits my needs exactly.

I need a way to save a report to a specific path after I have printed it as a PDF, so that I can send it as an email attachment. The idea is that an associate would be able to press a button and the report would print to PDf and send. I have the printing and the email code, but where I'm stuck is that I don't know how to save the report automatically to a path after using the PDF Printer. Right now it prints but then opens a "Save As" dialogue box. I'd like it to print and then save itself to a path.

This is the code I'm using at the moment to print the report:


Expand|Select|Wrap|Line Numbers
  1. Private Sub Command0_Click()
  2.  
  3. Dim defPrinter As String, NewPrinter As Printer
  4.  
  5. defPrinter = Application.Printer.DeviceName
  6.  
  7. Set NewPrinter = Application.Printers("Nationwide PDF Printer")
  8.  
  9. Set Application.Printer = NewPrinter
  10.  
  11. DoCmd.OpenReport "Weekly Application Status Update", acViewNormal
  12.  
  13. DoCmd.Close acReport, "Weekly Application Status Update", acSaveNo
  14.  
  15. Set NewPrinter = Application.Printers(defPrinter)
  16. Set Application.Printer = NewPrinter
  17. End Sub
  18.  
Thanks in advance!
Feb 10 '15 #1

✓ answered by jforbes

I think if you are using a Print Driver to generate the PDF, you are at the mercy of the Print Driver when it comes to it's options. So if it has a way to automatically save to a directory based on a settings file or something similar then that is the route you will need to take.

If you are using Access 2007 or newer, you can use DoCmd.OutputTo to have Access create the PDF for you and use the options that is supplies to save the PDF to a hard drive.

Here is some code from a project I worked on recently:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport sReportName, acViewPreview, , "ID='" & sID & "'"
  2. DoCmd.OutputTo acOutputReport, "", acFormatPDF, sFileName, True
  3. DoCmd.Close acReport, sReportName
In my example, I supply a Filter for the Report. Which requires opening the report first and then outputting to a PDF instead of just producing the output. The Report could be based on a Query to Filter the Report records and then the above code would be simpler, but there were other reasons that I decided against it.

So if you don't need to Filter on open, which it doesn't look like you do, you should be able to getaway with just the single DoCmd.OutputTo command; something like this:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OutputTo acOutputReport, "Weekly Application Status Update", acFormatPDF, sFileName, True

2 4001
jforbes
1,107 Expert 1GB
I think if you are using a Print Driver to generate the PDF, you are at the mercy of the Print Driver when it comes to it's options. So if it has a way to automatically save to a directory based on a settings file or something similar then that is the route you will need to take.

If you are using Access 2007 or newer, you can use DoCmd.OutputTo to have Access create the PDF for you and use the options that is supplies to save the PDF to a hard drive.

Here is some code from a project I worked on recently:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport sReportName, acViewPreview, , "ID='" & sID & "'"
  2. DoCmd.OutputTo acOutputReport, "", acFormatPDF, sFileName, True
  3. DoCmd.Close acReport, sReportName
In my example, I supply a Filter for the Report. Which requires opening the report first and then outputting to a PDF instead of just producing the output. The Report could be based on a Query to Filter the Report records and then the above code would be simpler, but there were other reasons that I decided against it.

So if you don't need to Filter on open, which it doesn't look like you do, you should be able to getaway with just the single DoCmd.OutputTo command; something like this:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OutputTo acOutputReport, "Weekly Application Status Update", acFormatPDF, sFileName, True
Feb 10 '15 #2
That worked like a dream! And so simple too! Thank you for the help!
Feb 11 '15 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by CARIGAR | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.