Access Report SaveToPDF, Name and SaveTo Dialog

I have the following code that saves to PDF and saves (on my Computer) directly to my Desktop  without a SaveTo/SaveAs Dialog
Is there a way to have the Dialog to SaveTo with the Naming convention and AutoStart set to True?

On Error GoTo OutputPDF_Error

strReport = "My_Report"
strFileName = Year([Auto_Date]) & "-" & "My Report" & ".pdf"

DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, strFileName, True

     If Err.Number = 2501 Then
         'MsgBox "Export was cancelled!"
     End If

Open in new window

Ernest GroggAsked:
Who is Participating?
Eric ShermanAccountant/DeveloperCommented:
"Dialog box (Output to) comes up with the name:  2015-My Report.pdf "

You can specify the file name or you can leave the Output File Parameter blank and get the Dialog Box ... can't do both at the same time.

OutputFile = A string expression that's the full name, including the path, of the file you want to output the object to. If you leave this argument blank, Access prompts you for an output file name.

I am not sure what you are asking.
My PDF's save for users of Access 2007+ without prompting using the following code

Private Sub SaveAsOfficePDF(stDocName As String)

Dim FormatValue As String
If Application.Version > 11 Then
    FormatValue = "PDF Format (*.pdf)"
    FormatValue = acFormatRTF
End If

DoCmd.OpenReport stDocName, acPreview
DoCmd.OutputTo acOutputReport, stDocName, FormatValue, "c:\tempPdf\" & Reports(stDocName).Caption & ".pdf"
DoCmd.Close acReport, stDocName, acSaveYes

End Sub

Open in new window

Since you aren't supplying a full path, just a filename, you are getting prompted.
So you need the path to the desktop
Environ("USERPROFILE") & "\Desktop"


strReport = "My_Report"
strFileName = Year([Auto_Date]) & "-" & "My Report" & ".pdf"
strFileName = Environ("USERPROFILE") & "\Desktop\ & strFileName

DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, strFileName, True
Ernest GroggAuthor Commented:
I don't see any prompt, but goes straight to "print" and Opens the PDF, no, Dialog Box whatsoever?

I would Like to have the User see the Dialog Box...putting the name of the file in the Dialog

I am using Access 2013 on Win8
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Eric ShermanAccountant/DeveloperCommented:
What happens if you leave the Output File parameter blank???

Ernest GroggAuthor Commented:
I get the Dialog box (Output to), no problems...
I don't understand then.
What do you want to have happen?
Ernest GroggAuthor Commented:
I want the Dialog box but Have the Name that I have setup in the strFileName

Is there a way to have it?

Click:  Save to PDF

Dialog box (Output to) comes up with the name:  2015-My Report.pdf
Too bad you accepted a solution.
etsherman is right, you can't get THE output dialog box to start with a default filename.
But that doesn't mean that you can't display UI elements to permit the user choice.

If you have a PDF printer installed, you can set the Report caption, and sending the report to the PDF printer will get that filename along with whatever the PDF printer will throw up for a dialog.  That's one way.

There's our friend the filedialog object, with the correct references in the VBA References.
Which is how I would do it.

Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
Dim fd As FileDialog

'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
'Declare a variable to contain the path
'of each selected item. Even though the path is a String,
'the variable must be a Variant because For Each...Next
'routines only work with Variants and Objects.
Dim vrtSelectedItem As Variant

'Use a With...End With block to reference the FileDialog object.
With fd
    .InitialView = msoFileDialogViewDetails ' msoFileDialogViewThumbnail
    .Title = "Folder Selector"
    '.InitialFileName = Environ("USERPROFILE") & "\Desktop
    .ButtonName = "Create Here"
    .AllowMultiSelect = False

    'The user pressed the action button.
    If .Show = True Then
        strFileName = Year([Auto_Date]) & "-" & "My Report" & ".pdf"
        strFileName = fs.GetAbsolutePathName(.SelectedItems(1)) & '\" strFileName
       DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, strFileName, True 
    end if
end with

Open in new window

Ernest GroggAuthor Commented:
Hello Nick,

I have tried this to no avail.  I get the same error I have seen on the internet that the Dim fd as FileDialog is not defined.  

I have read that one needs to install Object Library 14.0 but I have Access 2013 on a clean install, and I don't even know how to find out what Object Library I have and even to install another one.

What would I do?
with the correct references in the VBA References.
Nothing needs to be installed.
Just open Tools | References and find and check off Microsoft Office xx.0 Object Library.
In your case
Microsoft Office 14.0 Object Library

That's all.
Ernest GroggAuthor Commented:
That worked....

I just get a syntex error here:

strFileName = fs.GetAbsolutePathName(.SelectedItems(1)) & '\" strFileName

Open in new window

Play with it.
This may go
strFileName = .SelectedItems(1) & '\" strFileName
Msgbox out this .SelectedItems(1) and have a look at what you are getting.
It should be a path to a folder.
Does it have an ending slash?
Use it to construct a full filename and path you want.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.