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

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

Open in new window

Ernest GroggSecurity Management InfoSecAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick67Commented:
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)"
Else
    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"

Try

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

DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, strFileName, True
0
Ernest GroggSecurity Management InfoSecAuthor 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
0
Eric ShermanAccountant/DeveloperCommented:
What happens if you leave the Output File parameter blank???

ET
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Ernest GroggSecurity Management InfoSecAuthor Commented:
I get the Dialog box (Output to), no problems...
0
Nick67Commented:
I don't understand then.
What do you want to have happen?
0
Ernest GroggSecurity Management InfoSecAuthor 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
0
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.

ET
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nick67Commented:
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

TryAgain:
'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

0
Ernest GroggSecurity Management InfoSecAuthor 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?
0
Nick67Commented:
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.
0
Ernest GroggSecurity Management InfoSecAuthor Commented:
That worked....

I just get a syntex error here:

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

Open in new window

0
Nick67Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.