Link to home
Start Free TrialLog in
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!
ASKER CERTIFIED SOLUTION
Avatar of Anders Ebro (Microsoft MVP)
Anders Ebro (Microsoft MVP)
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Senniger1
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.
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:

User generated image
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 :)
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)
User generated image
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?
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)
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.