Results 1 to 14 of 14
  1. #1
    brharrii's Avatar
    brharrii is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Seattle, WA
    Posts
    63

    VBA to Export report as PDF with calculated file name and user defined file path

    I just figured out how to export a report with a formulated file name, but when I implemented the new code it eliminted the navigation window that allowed the user to pick where they wanted the file to be saved. Is it possible to keep the automatic naming of the file itself but still allow the user to define where they want the file saved each time?



    This is the code I'm using now:

    Code:
    Private Sub cmdSave_Click()
        DoCmd.OutputTo acOutputReport, "rptEmailREport", acFormatPDF, Me.LabelNumber & ".pdf", False, "", 0
    End Sub
    Thank you for your help,

    bruce

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes. That would require opening the FileDialog object and saving the selected folder path to a variable and concatenating the variable into the destination file name.

    http://msdn.microsoft.com/en-us/libr...ffice.11).aspx
    http://msdn.microsoft.com/en-us/libr...ffice.10).aspx
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    brharrii's Avatar
    brharrii is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Seattle, WA
    Posts
    63
    Thanks for the reply June

    I'm afraid that the articles you provided are a little bit beyond my reading level, I read them both, but I'm not sure I fully understood the big picture.... I copied the code from the first link and assigned it to my command button. It did generate the filedialog object, but that was where I got stuck.... Where do I put the code to tell it how to determine the name that the report should be saved as?

    Thanks again for your help

    Code:
    Sub Main()
    
    
        Dim fd As FileDialog
        Set fd = Application.FileDialog(msoFileDialogFilePicker)
        Dim vrtSelectedItem As Variant
        With fd
    
            If .Show = -1 Then
    
                For Each vrtSelectedItem In .SelectedItems
    
                    MsgBox "The path is: " & vrtSelectedItem
    
                Next vrtSelectedItem
       
            Else
            End If
        End With
    
        
        Set fd = Nothing
    
    End Sub
    

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You want to pick a folder, not a file.
    Also might want to set the default initial folder location.
    Instead of the MsgBox, set the value of a variable.
    Code:
    Sub Main()
        Dim fd As FileDialog
        Dim strPath As String
        Set fd = Application.FileDialog(msoFileDialogFolderPicker)
        Dim vrtSelectedItem As Variant
        With fd
            fd.InitialFileName = "C:"
            If .Show = -1 Then
                For Each vrtSelectedItem In .SelectedItems
                    strPath = vrtSelectedItem
                Next vrtSelectedItem
            Else
            End If
        End With
        Set fd = Nothing
        DoCmd.OutputTo acOutputReport, "rptEmailREport", acFormatPDF, strPath & "\" & Me.LabelNumber & ".pdf", False, "", 0
    End Sub
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    brharrii's Avatar
    brharrii is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Seattle, WA
    Posts
    63
    ooh, I got it, so the user then selects the folder and then the pdf is saved for them in that directory with the formulated name.

    This is almost exactly what I'm looking for, there is still one thing though. I would like the user to retain the ability to change the file name if necessary, very similar to when you output a report without manipulating the name. the filedialog window should open with the formulated name pre populated (me.labelnumber) in the text box at the bottom but still allow the user to navigate their hard drive / server folders to save the report in a place of their choosing. If the user decides they want to change the file name at that point, they can change the pre-populated value at their discression.

    Is this possible?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I don't think so.

    The file picker will error because the file doesn't exist.

    And save file dialog won't work because there isn't a file to save. OutputTo has to do that.

    Use a textbox for user input of file name and concatenate the textbox. Validate before running code.

    If Not IsNull(Me.textbox) Then
    ...
    End If

    or just

    strPath & "\" & Nz(Me.textbox, Me.LabelNumber) & ".pdf"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    brharrii's Avatar
    brharrii is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Seattle, WA
    Posts
    63
    where me.textbox is the file path chosen by the user? me.LabelNumber is the textbox that stores the file name.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Where does the value for LabelNumber come from? I assumed this was a bound textbox.

    Me.textbox would be an unbound textbox where user inputs just the name they want for the document, not the entire path. The folder path will be determined by the folder picker.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    brharrii's Avatar
    brharrii is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Seattle, WA
    Posts
    63
    me.LabelNumber is the item number of whatever record is being viewed on the form at the time. It is a bound textbox, but it is also generally the desired file name.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Okay, so does the suggested code do what you asked for?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    brharrii's Avatar
    brharrii is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Seattle, WA
    Posts
    63
    I apologize I didn't understand what change you were suggesting I make. Initially I was using the OutputTo command:

    Private Sub cmdSave_Click()
    DoCmd.OutputTo acOutputReport, "rptEmailREport", acFormatPDF, Me.LabelNumber & ".pdf", False, "", 0
    End Sub
    I understood from your post that you are suggesting I put a check to make sure my textbox is populated before running the code, so something like this:

    If Not IsNull(Me.textbox) Then
    Private Sub cmdSave_Click()
    DoCmd.OutputTo acOutputReport, "rptEmailREport", acFormatPDF, Me.LabelNumber & ".pdf", False, "", 0
    End Sub
    End If
    Did I missunderstand?

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Post 6 offers two options. An If Then structure or Nz().

    Regardless, use the folder picker for user to select destination path.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    brharrii's Avatar
    brharrii is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Seattle, WA
    Posts
    63
    I guess I did missundertand, I thought when you said:

    The file picker will error because the file doesn't exist.
    That you were advising that it wouldn't do what I was hoping it would. I'll go back and play with it some more, Thanks

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can pick the folder, because I assume it already exists - see code in post 4. Can't pick file because purpose of this code is to create a file.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Export each page of the report to a seperate PDF file
    By naeemahmad in forum Programming
    Replies: 14
    Last Post: 12-04-2013, 09:18 AM
  2. Replies: 1
    Last Post: 05-14-2013, 11:49 AM
  3. Replies: 2
    Last Post: 11-08-2012, 11:04 AM
  4. Replies: 3
    Last Post: 07-30-2012, 02:16 PM
  5. Replies: 10
    Last Post: 03-04-2012, 12:17 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums