Export to PDF variable filename (prompt for location)

[miicker]

Hello everyone,



I have a report which is an invoice, there is a field called InvoiceNr, and I want the filename to be the InvoiceNr. When I use code like "DoCmd.OutputTo" the filename is the same as report name.
The only way to do this is to specify the "Output File", but then I have to specify the whole location, and I want the user to be prompted for the location, but that the name is changed to the Invoice Number.

Changing the caption of the form doesn't work.

Hopefully you guys can help me.

[ranman256]

There's a hole....if you let the user enter the folder, then they can also change the name, thus ruining your file.

[RuralGuy]

You may find this link useful: http://access.mvps.org/access/api/api0002.htm

[ranman256]

code to save the pdf
usage: SaveFile "inv1542"

Public Sub SaveFile(ByVal pvInvoice)
Dim vDir, vFile
Dim i As Integer

vDir = "c:\"
vFile = UserPickFile(vDir)
If vFile <> "" Then
  i = InStrRev(vFile, "\")
  If i > 0 Then
    vDir = Left(vFile, i) 'the folder
    vFile = vDir & pvInvoice
    DoCmd.OutputTo acOutputReport, "report", acFormatPDF, vFile
  Else
     MsgBox "no file saved"
  End If
End If
End Sub

Public Function UserPickFile(Optional pvPath)
Dim strTable As String
Dim strfilepath As String
Dim sDialog As String, sDecr  As String, sExt As String

If IsMissing(pvPath) Then pvPath = "c:\"

With Application.FileDialog(msoFileDialogFilePicker)       'MUST ADD REFERENCE : Microsoft Office 11.0 Object Library
    .AllowMultiSelect = False
    .Title = "Locate a file to Import"
    .ButtonName = "Import"
    .Filters.Clear
    .Filters.Add "All Files", "*.*"
    .InitialFileName = pvPath
    .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail
    ''.AllowMultiSelect = True
     
        If .show = 0 Then
           'There is a problem
           Exit Function
        End If
    
    'Save the first file selected
    UserPickFile = Trim(.SelectedItems(1))
End With
End Function

[miicker]

There's a hole....if you let the user enter the folder, then they can also change the name, thus ruining your file.

It's not a problem that the user can change the filename, I just want the default filename to be the invoice number, not "rptInvoice"

I will try the code in a few hours.

[ItsMe]

I would probably do something like the following. There is not any need to add references. The default references should include the Office Object thing.

Dim strPath As String
Dim strFileName As String
    With Application.FileDialog(msoFileDialogFolderPicker)
    
        .Title = "Locate an export to location"
        .ButtonName = "Choose"
        .InitialView = msoFileDialogViewThumbnail
        
            If .Show = 0 Then
                'TODO create some error handle stuff here
                Exit Sub
            End If
            
        strPath = Trim(.SelectedItems(1))
        
    End With
strFileName = "MyFileName.pdf"
DoCmd.OutputTo acOutputReport, "ReportName", acFormatPDF, strPath & "\" & strFileName

[miicker]

It's not a problem that the user can change the filename, I just want the default filename to be the invoice number, not "rptInvoice"

I will try the code in a few hours.

When I run your code i get the following error:

Run-time error '-2147467259 (80004005)'

Method 'FileDialog' of object '_Application' failed.

When I hit debug it marks this line:

With Application.FileDialog(msoFileDialogFolderPicker)

When I looked at the Microsoft website I found this:
' Requires reference to Microsoft Office 11.0 Object Library.
How do I do this in Access 2016?

[ItsMe]

What code are you referring to?

You can add references via the VBA editor. Tools>References
If you are using Access 2010, the version number will be 14

[miicker]

I'm referring to your code.
Thanks i'm using Access 2016, and enabled number 16, and that worked.

I'm not verry expirienced with VBA, so the code ranman provided does not make any sense to me, I don't know how to use it, sorry.

With the code that was provided by "ItsMe" its working now, the user gets prompted for the location.

Two questions:
Is it possible to set a default location (for exemple, the windows that lets the user select the location opens My Documents istead of My Computer?)
Is it possible to give the user an message if the file already exists, now the file just gets overwritten.

[ItsMe]

In my example, I am using the folder picker thing vs. the file picker. I believe the default folder is the initial view property. You would add it with the other assignments within the With block of code.

        .Title = "Locate an export to location"
        .InitialView = \\ServerName\FolderName
        .ButtonName = "Choose"

To get the path for the User's desktop you could use Window's Special Folder thing.

Dim strPath As String
strPath = CreateObject("WScript.Shell").SpecialFolders("MyDocuments")

To avoid overwriting a file, you can do something like the following.

If Dir(strPath & "\" & strFileName, vbDirectory) <> "" Then
'perform the Outputo
else
msgbox "That report already exists!"
end if

[miicker]

In my example, I am using the folder picker thing vs. the file picker. I believe the default folder is the initial view property. You would add it with the other assignments within the With block of code.

        .Title = "Locate an export to location"
        .InitialView = \\ServerName\FolderName
        .ButtonName = "Choose"

This code does not work for me. How can I fix this? (I changed the servername to the server I use).

I am now using the following code:

Private Sub ButtonPDF_Click()

Dim strPath As String
Dim strFileName As String
Dim ReportName As String
Dim strBeginPath As String
ReportName = Me.Name


    With Application.FileDialog(msoFileDialogFolderPicker)


        .Title = "Locate an export to location"
        .InitialView = msoFileDialogViewThumbnail
        .ButtonName = "Choose"




            If .Show = 0 Then
                'TODO create some error handle stuff here
                Exit Sub
            End If


        strPath = Trim(.SelectedItems(1))


    End With


strFileName = [pdfName]


If Dir(strPath & "\" & strFileName & ".pdf", vbDirectory) <> "" Then
    Dim LResponse As Integer
    LResponse = MsgBox("File already exists, do you want to overwrite the file?", vbYesNo, "Warning")
        If LResponse = vbYes Then
            DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, strPath & "\" & strFileName & ".pdf"
        Else
        End If
    Else
    DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, strPath & "\" & strFileName & ".pdf"
End If


End Sub

This code gives the user the warning that the file already exists and asks to overwrite the file.

[ItsMe]

What is the name of the file you are trying to use? Is it [pdfName]?

You need to include the file extension, .pdf. Also, are you really using brackets around the file name? I suppose this is a valid file name. However, Windows and or VBA may not appreciate the special characters.

[miicker]

What is the name of the file you are trying to use? Is it [pdfName]?


You need to include the file extension, .pdf. Also, are you really using brackets around the file name? I suppose this is a valid file name. However, Windows and or VBA may not appreciate the special characters.

I use a field in the report as name, the name of that field is [pdfName], and i declair it as a variable in:
strFileName = [pdfNaam]

I give it the file extension in:
DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, strPath & "\" & strFileName & ".pdf"


The code works just fine, I just want to change the default location (that is now just My Pc).

[ItsMe]

The code works just fine, I just want to change the default location (that is now just My Pc).

I had made a mistake when I guesed the property for default folder. It is the same as it is for msoFileDialogFilePicker. So the following settings should ensure that it defaults to the User's MyDocuments folder.

        .Title = "Locate an export to location"
        .ButtonName = "Choose"
        .InitialView = msoFileDialogViewThumbnail
        .InitialFileName = CreateObject("WScript.Shell").SpecialFolders("MyDocuments")

[miicker]

I had made a mistake when I guesed the property for default folder. It is the same as it is for msoFileDialogFilePicker. So the following settings should ensure that it defaults to the User's MyDocuments folder.

        .Title = "Locate an export to location"
        .ButtonName = "Choose"
        .InitialView = msoFileDialogViewThumbnail
        .InitialFileName = CreateObject("WScript.Shell").SpecialFolders("MyDocuments")

Thank you! That did the trick, thank you very much for your help!