VBA to set filename/location in Save As dialog

[Big Pat]

Hi,

The code below sets my printer to "Cute PDF writer" and then opens a report in print view, which creates a PDF version of my report. So far so good. But just before the last step, it brings up the Save As dialog box, asking for a filename and location.

Three points are important:

1. I always want to call the PDF document "Specialty Report"
2. I always want to save it to C:\Documents and Settings\80435\Desktop\
3. I always want to overwrite the previous version.

Is there some code I can insert that would achieve this automatically with no further user interaction i.e. user clicks a button and the PDF file is created/overwritten?

'Produce the report
Dim defPrinter As String, NewPrinter As Printer
defPrinter = Application.Printer.DeviceName                'Get the default printer name
Set NewPrinter = Application.Printers("CutePDF Writer")    'Create a new printer object
Set Application.Printer = NewPrinter                       'Set the default printer to the new printer
DoCmd.OpenReport "Specialty Report", acViewPrint           'Open the report in print view (which will PDF it)
Set NewPrinter = Application.Printers(defPrinter)          'Reset the printer back to the default printer

Thank you

 

[BlueIshDan]

Try this out...

Private Sub SaveSpecialtyReport()

    Dim MyPath As String: MyPath = "C:\Documents and Settings\80435\Desktop\"
    Dim MyFilename As String: MyFilename = "Specialty Report.pdf"
    
    
    
    'Let's print and save. Once you see it works, you can change True to False so that the file created is not opened after completion.
    DoCmd.OpenReport "Specialty Report", acViewPreview
    DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyPath & MyFilename, True
    
    'Let's close our previewed report
    DoCmd.Close acReport, "Specialty Report"

End Sub

 

[Big Pat]

Thank you. That works great.

I never knew there was such a thing as acFormatPDF and I had cobbled my code together from something I picked up on this forum several years ago.

We have recently upgraded to Access 2010 and I wonder if it's a new feature.

Thanks again!!

 

[Gasman]

yep, it is not in 2003.

I had to save a copy of my report in the name of the file I required, then output report to Cutepdf, then delete the object.