VBA Code to Save Access Report to PDF File Using Acrobat 11

Senniger1
Senniger1 used Ask the Experts™
on
I have Access 2010 and Adobe Acrobat 11 Pro.

When I was using Access 2010 with Adobe Acrobat 10 Pro, I used the following code to save my report to a PDF file.
   DoCmd.OutputTo acOutputReport, "rptCalendar_Month", acFormatPDF, "c:\TimeOff.pdf", False

Now that I'm using Access 2010 with Acrobat 11, my code no longer works and I get the following runtime error:

   VB Runtime Error 2282: The format in which you are attempting to output the current object is not available

I need some VBA code that will save my Access report to a PDF file.

Can anyone help me?

Thanks in advance!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
The code doesn't use acrobat at all. I think something else is going on. If you manually open an access report and save as PDF from the ribbon, does that work?

Author

Commented:
Anders Ebro - I don't have an Acrobat tab on my ribbon, so No, I cannot save the report as a PDF.

Tahir Qureshi - I found that link when I researched yesterday and found nothing I could use from it that worked.

Note I don't have the ACROBAT PDFMAKER OFFICE COM ADDIN in Access and when I go to add the PDFMOfficeAddin.dll file under COM Add-Ins, I can locate the file, but when I click OK it doesn't add.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Top Expert 2014

Commented:
You don't need Adobe or anything else installed to create the PDF from Access. Just open the report in print preview mode and click the "PDF or XPS" button on the Data ribbon group:

PDF button

Author

Commented:
Hi Scott,

When I preview my report, my data tab doesn't have the choice "PDF or XPS" so I added it to my ribbon, but it's just greyed out.

Also, even if we can get this working, I need this to be automated with code so it saves as the PDF.

Thanks much!
Most Valuable Expert 2012
Top Expert 2014
Commented:
Then something's wrong with your installation. The PDF functionality is built into 2010. Try repairing your installation of Office/Access.

If you get the PDF functionality working, it's very simple to save as PDF - the code you posted in your question will do it!
I realize you want it to work from code. I was just trying to "debug" the problem, and step 1 is testing whether it works manually :)

Author

Commented:
I have uninstalled and reinstalled Office 2010 and Acrobat 11.  I don't have an Acrobat tab.   Also under Save & Publish, Save Object As, Database File type PDF or XPS is greyed out.

I did more research and found Acrobat XI's PDFMaker does not support MS Access 2010 which may be this issue here.
   https://helpx.adobe.com/acrobat/kb/compatible-web-browsers-pdfmaker-applications.html

Through my research I found the following code works, but it still requires user intervention.  Basically it leaves you at the "Save As" window and you have to manually click Save.
   Set myPrinter = Application.Printer
   Set Application.Printer = Application.Printers("Adobe PDF")
   DoCmd.OpenReport "rptCalendar_Month", acViewPreview, , , acWindowNormal
   DoCmd.PrintOut acPrintAll, 1, , acHigh, 1
   Set Application.Printer = myPrinter

Perhaps someone knows how to code the save part to my code here or if anyone has any other code based on the limitations I'm faced with I would be very grateful.  Thanks!
If you open a report in Print preview, the standard ribbon should look something like this (I am using 2016)
BlogImage.png
Note that that ribbon is not available if you are using Report view.

This is the code I personally use for exporting to PDF. I've found it works best to open the report in Print Preview hidden, and then export to PDF. I don't actually recall why at the moment.
 DoCmd.OpenReport "ReportName", acViewPreview, , "SiteID=" & SiteID, acHidden
   DoCmd.OutputTo acOutputReport, "ReportName", acFormatPDF, "C:\SomeFolder"\& "InspectionDetailedReport.pdf", True, , , acExportQualityPrint
  docmd.Close acreport,"ReportName"
   

Open in new window


Note that some versions of windows no longer allows you to save files directly to the root of C:
Have you checked if you can do that?

Author

Commented:
Hi Anders,

My "PDF or XPS" button wasn't in my Data group so I added it and it's greyed out as I stated above.  Your code is giving me the same runtime error I initially reported I was getting with my code.

What version of Acrobat are you using?
In office 2010 MS added built in PDF support, so acrobat is not required. Whether it might be interfering is another matter. If you open excel or word, are you able to save to PDF there using the built in functions (i.e. not any found in the acrobat ribbon)

Author

Commented:
Okay I figured out if I open Access in Safe Mode and create a new database, I have the "PDF or XPS" button on the Print Preview tab in the Data Group and it works perfectly.

The moment I exit the newly created database and open it back up (even in safe mode), I loose that function and it is greyed out.  

I've checked Add-ins, etc. and cannot figure out what is causing the issue.

Does anyone have any ideas?
I can only guess that maybe the Adobe addin you mention is not working correctly, and that is what is causing the issue.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial