Macro of VBA to Export Report as PDF

[buck]

Can anyone advise please how to export a Report as PDF in 2010 either Macro or VBA is fine.

Thanks

Cross-posted below




http://www.mrexcel.com/forum/microsoft-access/746993-macro-export-pdf.html

[AlexHedley]

Have a look into the OUTPUTTO function.

DoCmd.OutputTo acOutputReport, "rptREPORTNAME", acFormatPDF, strPathAndFile, True

[buck]

Hi Alex,


I tried this a couple times prior to the post, but for some reason I'm getting run-time error 2501: outputto action was canceled. I tried a macro as well, but the outputto also canceled. Not sure if it matters, but the report source is a query with a date parameter. It opens fine and allows the date to be entered as it should, but the process then stops.

I tried the strPathAndFile name with and without ""

Private Sub Command22_Click()
DoCmd.OutputTo acOutputReport, "rptLTR1RefundRequest", acFormatPDF, S:\Finance\Accounting\Overpayment-Collections\PENDING REVIEW, True
End Sub

[AlexHedley]

The path needs a filename need file extension.
You will need it in quotes as it is a string.

DoCmd.OutputTo acOutputReport, "rptLTR1RefundRequest", acFormatPDF, "S:\Finance\Accounting\Overpayment-Collections\PENDING REVIEW\filename.pdf", True

[buck]

ok great that worked, can you also tell me how I can add the date to the filename when exporting please?

[AlexHedley]

Dim strPathAndFile As String
strPathAndFile = "S:\Finance\Accounting\Overpayment-Collections\PENDING REVIEW"

Dim fileDate As String
fileDate = Replace(Date(), "/", "")'26/12/2013 => 26122013

strPathAndFile = strPathAndFile & "\filename_" & fileDate & ".pdf"

Replace "filename_" with whatever you want to call it.
I've added "_" to separate the filename from the date.

Then pass strPathAndFile in the correct argument.

[buck]

Alex, thanks, you've been a big help.