I have on my task list the same objective. This is my plan but I haven't actually done it yet:
Instead of outputing the query/report, set the default printer to the PDF writer and then printout the query/report. I think you should be able to set/change the default printer in the procedure and then print the object you wish.
I don't have a problem getting my reports to PDF. My problem is figuring out how to automate the process so I can open my reports, output them to PDF using the writer, then save the reports with a name based on certain criteria passed to a form. Similar to the process I used with my query to Excel files in the code I posted above.
Hope that clarifies more what I am trying to achieve.
Well, there were several steps to the "process". Probably not the most efficient way, but this was a work in progress and I can always tidy it up a bit.
1) I setup my PDF port as my working/default directory.
2) I setup the Adobe Distiller defaults to save and overwrite without prompting.
3) I setup my reports to use Adobe Distiller as the Specific Printer.
4) I ran a macro which opened the reports in Print view, sending them in PDF format to my working/default directory.
5) I called this function to copy them to where I needed them (only one report is listed, I'll add the rest):
Function CopyPDFs() As String
Dim SourceFile As String
Dim DestinationFile As String
Dim FolderName As String
Set FSO = CreateObject("scripting.filesystemobject")