Avatar of Senniger1
Senniger1
 asked on

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!
PDFVBAMicrosoft Access

Avatar of undefined
Last Comment
Anders Ebro (Microsoft MVP)

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Anders Ebro (Microsoft MVP)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Tahir Qureshi

Senniger1

ASKER
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.
Scott McDaniel (EE MVE )

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Senniger1

ASKER
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!
SOLUTION
Scott McDaniel (EE MVE )

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Anders Ebro (Microsoft MVP)

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 :)
Senniger1

ASKER
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!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Anders Ebro (Microsoft MVP)

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?
Senniger1

ASKER
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?
Anders Ebro (Microsoft MVP)

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)
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Senniger1

ASKER
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?
Anders Ebro (Microsoft MVP)

I can only guess that maybe the Adobe addin you mention is not working correctly, and that is what is causing the issue.