473,372 Members | 953 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

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 4443
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

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

Similar topics

3
by: Nicola | last post by:
Hi Everyone, I am new to programming and would like to know how to open an access Report from within vb 6. I am trying to write a program to organise cross stitch threads. I have found out how...
15
by: Mark C | last post by:
All, I have exhaustingly been looking through the newsgroups in search of a way to systemically output an Access 97 report to a pdf file using the full version of Adobe Acrobat. I want the user...
1
by: mar10a | last post by:
I am trying to insert a .gif. file into an Access report - using Insert Picture. The original image is 8X11, but access changes the size to 4X6.5. When I change the size of the image in access...
0
by: neoteny2 | last post by:
I need MS Access to automatically create reports/subreports based on specific criteria. I am building a database in Access 2003 with different locations/sites. I have the "sites" table created...
1
by: ellenh | last post by:
I have read postings on the similar subject including the posting from 2003 shown below. This process works fine to display a single page snapshot report in PowerPoint. I need to display...
3
by: Birky | last post by:
I have a report which I am opening from a command button within a form. I am using the “DoCmd.OpenReport stDocName” command to open the report but it is automatically printing instead of popping the...
6
by: DeniseY | last post by:
I have an Access report that is created on the fly by the user selecting the fields to be included. The Access report comes out fine, but I want it to automatically output to an Excel spreadsheet....
1
by: sonicfun2006 | last post by:
I have SQL Server Database and MS Access 2003 is connected with ODBC. I have very large amount of records in various tables. The database is very dynamic as it changes every minute. I’m trying to...
1
by: billelev | last post by:
Here is some code that I have adapted slightly. It allows a report to be printed to a specific location. It works by calling SaveReportAsPDF and specifying the access report name, and the root...
3
by: Bay0519 | last post by:
Hi, I'm using access 2003. Is there a way for me to open an access report with zoom to 82% automatically? Right now, I set it up as maximize, using docmd.maximize but I want it to zoom to 82%...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.