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

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!
Senniger1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
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?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tahir QureshiSystem AnalystCommented:
0
Senniger1Author 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.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
0
Senniger1Author 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!
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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!
0
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
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 :)
0
Senniger1Author 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!
0
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
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?
0
Senniger1Author 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?
0
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
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)
0
Senniger1Author 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?
0
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
I can only guess that maybe the Adobe addin you mention is not working correctly, and that is what is causing the issue.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PDF

From novice to tech pro — start learning today.