none
Vba for report to pdf. RRS feed

  • Question

  • Hi

    I am putting some code behind a button on a form that will export the report for that record to a pdf.

    Using the report to file macro I can get the filename to default to the report name but what I really want is the filename to default to a combination of field names from the form / report.

    I have also played with some vba and can get the button to export using fields as the filename to a specified path but what I want is for the users to be prompted with a dialogue box as above to be able to specify their own path.

     Private Sub Command61_Click()
       vFile = "C:\Users\User\Documents\" & Forms![quoteview]![CustName] & "STQ000" & Forms![quoteview]![QuoteNo] & " .pdf"
        DoCmd.OutputTo acOutputReport, "QuoteView", acFormatPDF, vFile

    End Sub

    Has anyone got any ideas.

    Assistance would be greatly appreciated.

    Julie


    • Edited by JulieMei Saturday, February 11, 2017 3:14 PM Duplicated
    Saturday, February 11, 2017 2:28 PM

Answers

  • You might like to take a look at InvoicePDF.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file works slightly differently to what I think you have in mind, as the user selects a main folder at start-up, and a sub-folder and the filename is then added whenever a PDF is created.  The folder is selected using the following function:

    Function GetFolder() As String

        Const FOLDER_PICKER = 4
        
        With Application.FileDialog(FOLDER_PICKER)
            .Title = "Select Folder"
            If .Show Then
                GetFolder = .SelectedItems(1)
            End If
        End With

    End Function

    The filename, and a sub-folder for the customer if one doesn't already exist, is added in the module of the main invoice form when the PDF file is created, using a combination of the customer name and invoice number.  It would not be difficult to change the code so that, rather than selecting a folder at start-up, this was done when the PDF file is created.  It would merely be necessary to call the above function in the cmdPDF_Click procedure in the invoice form's module rather than in that of the initial dialogue form.

    Ken Sheridan, Stafford, England

    • Marked as answer by JulieMei Sunday, February 12, 2017 8:22 AM
    Saturday, February 11, 2017 6:30 PM

All replies

  • Hi

    Try this:

    On Error GoTo Button_Err
    
       Dim FileNameAndPath As String    
        FileNameAndPath = "C:\Users\User\Documents\" & Forms![quoteview]![CustName] & "STQ000" & Forms![quoteview]![QuoteNo] & " .pdf"
    
        DoCmd.OutputTo acOutputReport, "QuoteView", "PDFFormat(*.pdf)", FileNameAndPath, True, "", , acExportQualityPrint
    
    Button_Exit:
        Exit Sub
    
    Button_Err:
        MsgBox Error$
        Resume Button_Exit:
    End Sub
    


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Saturday, February 11, 2017 2:39 PM
  • Hi

    Thanks I really appreciate you taking the time to respond but what I really want is a file dialog box to display so different users can select different file paths. Like when you save as in excel or word but I want the file name to default to the Customer Name and the Quote Number from the form and the file type to be pdf.

    Thx

    Julie

    Saturday, February 11, 2017 2:53 PM
  • Hi Julie,

    You might check out the FileDialog Object.

    Hope it helps...

    Saturday, February 11, 2017 6:10 PM
  • You might like to take a look at InvoicePDF.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file works slightly differently to what I think you have in mind, as the user selects a main folder at start-up, and a sub-folder and the filename is then added whenever a PDF is created.  The folder is selected using the following function:

    Function GetFolder() As String

        Const FOLDER_PICKER = 4
        
        With Application.FileDialog(FOLDER_PICKER)
            .Title = "Select Folder"
            If .Show Then
                GetFolder = .SelectedItems(1)
            End If
        End With

    End Function

    The filename, and a sub-folder for the customer if one doesn't already exist, is added in the module of the main invoice form when the PDF file is created, using a combination of the customer name and invoice number.  It would not be difficult to change the code so that, rather than selecting a folder at start-up, this was done when the PDF file is created.  It would merely be necessary to call the above function in the cmdPDF_Click procedure in the invoice form's module rather than in that of the initial dialogue form.

    Ken Sheridan, Stafford, England

    • Marked as answer by JulieMei Sunday, February 12, 2017 8:22 AM
    Saturday, February 11, 2017 6:30 PM
  • Thanks to everyone who took the time to respond.

    I don't have exactly what I was hoping for but can definitly work with a solution now after all your feedback & advice.

    Many many thanks.

    Sunday, February 12, 2017 8:25 AM