Access VBA - Export Access Form to PDF then Close the Adobe Reader

vba pdf ms-access-2013 adobe-reader auto-close
Grrly picture Grrly · Oct 23, 2014 · Viewed 10.5k times · Source

I have a VBA code in Access that exports/saves 3 seperate Access Forms to a PDF for record purposes. However, upon completion, Adobe Reader opens the Forms that were saved, requiring the processor to manually close all 3 forms. 3 forms doesn't sound like much but they will be completing this process continuously for all 239 of our entities! This means they will have to manually click Close over 700 times a day! So much for efficiency.

Is there a VBA code to close the PDF in Adobe Reader?

Below is the code that I am currently using:

Private Sub Command4_Click()
DoCmd.SetWarnings False

DoCmd.OpenQuery "Add to Completed", acViewNormal
DoCmd.OpenQuery "Clear from Master", acViewNormal
DoCmd.OpenQuery "Completed Totals", acViewNormal
DoCmd.OpenQuery "Update AB Totals", acViewNormal
DoCmd.OpenQuery "Update CD Totals", acViewNormal
DoCmd.OpenQuery "Update EF Totals", acViewNormal
DoCmd.OpenQuery "Update YTD Total", acViewNormal

DoCmd.OpenForm "Form123-pg1", acPreview
DoCmd.PrintOut acPrintAll
**DoCmd.OutputTo acOutputForm, "Form123-pg1", acFormatPDF, "Z:\Corporate\SubProcess\2014\" & Format(Date - 30, "mmyy") & " - " & [Forms]![Deal_Nav]![cbo_UnitNo] & " ReportName Pg1.pdf", True**
DoCmd.Close acForm, "Form123-pg1", acSaveNo
DoCmd.OpenForm "Form123-pg2", acPreview
DoCmd.PrintOut acPrintAll
**DoCmd.OutputTo acOutputForm, "Form123-pg2", acFormatPDF, "Z:\Corporate\SubProcess\2014\" & Format(Date - 30, "mmyy") & " - " & [Forms]![Deal_Nav]![cbo_UnitNo] & " ReportName Pg2.pdf", True**
DoCmd.Close acForm, "Form123-pg2", acSaveNo
DoCmd.OpenForm "Form123-pg3", acPreview
DoCmd.PrintOut acPrintAll
**DoCmd.OutputTo acOutputForm, "Form123-pg3", acFormatPDF, "Z:\Corporate\SubProcess\2014\" & Format(Date - 30, "mmyy") & " - " & [Forms]![Deal_Nav]![cbo_UnitNo] & " ReportName Pg3.pdf", True**
DoCmd.Close acForm, "Form123-pg3", acSaveNo

Me.Requery
Me.Refresh
DoCmd.SetWarnings True

End Sub

Answer

RudiBoy picture RudiBoy · Oct 23, 2014

Acrobat opens after creating your pdf because you tell it to. 

DoCmd.OutputTo acOutputForm, "Form123-pg1", acFormatPDF, "Z:\Corporate\SubProcess\2014\" & Format(Date - 30, "mmyy") & " - " & [Forms]![Deal_Nav]![cbo_UnitNo] & " ReportName Pg1.pdf", True

If you change the AutoStart Flag from True to False then it won't and you don't have to close anything.

DoCmd.OutputTo acOutputForm, "Form123-pg1", acFormatPDF, "Z:\Corporate\SubProcess\2014\" & Format(Date - 30, "mmyy") & " - " & [Forms]![Deal_Nav]![cbo_UnitNo] & " ReportName Pg1.pdf", False

Now Docmd.OutputTo will just create the file.

Related questions

VBA Print to PDF and Save with Automatic File Name

I have a code that prints a selected area in a worksheet to PDF and allows user to select folder and input file name. There are two things I want to do though: Is there a way that the PDF …

Read More
Save multiple sheets to .pdf

I have a reporting spreadsheet that grabs some data from a database and forms three sheets of summary report information. I want to run the spreadsheet automatically from the command line, and have it automatically save all three reporting sheets …

Read More
Save Excel range as pdf

I'd like to 'save as' a specific sheet or specific range to pdf. I tried implementing a range into my code. Here is what I've been working with: ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "c:\Book1.pdf", Quality:= _ xlQualityStandard, IncludeDocProperties:=True, …

Read More