asked on

archive a report (as .pdf) to a file directory

Currently, my users click on a button to create a report and then save it as a pdf to a directory. How would i automate this process?

Russell Fox

You can create a macro and give them a button to push when it's time to generate the file. Here are some instructions with images (start around step 8).

vbnetcoder

ASKER

I would prefer VBA code if possible

ASKER CERTIFIED SOLUTION

Russell Fox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vbnetcoder

ASKER

My client is telling me now that they want it in excel format. I am having issues when i do this

DoCmd.OutputTo acOutputReport,"rptYourReport",acFormatXLSX, "C:\Access\rptFoo.xlsx"

Run-time error 2282 The format you are attempting to output the current object is not available

Russell Fox

Are you using Office 2007? There's a bug where you have to have Service Pack 2 installed in order to export to Excel:
https://support.microsoft.com/en-us/kb/934833

vbnetcoder

ASKER

2013

Russell Fox

It looks like you either have to use acFormatXLS or export the query, rather than the report, using DoCmd.TransferSpreadSheet().

vbnetcoder

ASKER

so then the resulting spreadsheet needs to be .xls too?

Russell Fox

Yeah, it looks that way. And since xslx is a completely different xml format, you can't just change the file type. Here's a powershell script that will do a conversion, though, if you can work it into your process. For example, you could send the file to a hidden folder and have a process that runs every few minutes to convert & move any xls file to a folder visible to your users.

vbnetcoder

ASKER