VBA to export report still saves report even if user exits out of save dialog (1 Viewer)


Registered User.
Local time
Today, 09:49
Jun 11, 2019
I've got the below VBA to export a report if the user presses a button. It opens up the Windows save dialog box which allows the user to rename the report and save it wherever. I noticed if a user cancels out of the dialog box, it still saves the report in whatever folder they are in. Any idea what to alter in the VBA so that if a user hits 'cancel' or exits out, it doesn't save?

Private Sub btnExportReport_Click()
    Dim reportName As String
    Dim fd As Object
    Dim filename As String
    reportName = "rptCustomerComplaints"

    Set fd = Application.FileDialog(2)
    filename = "Customer Complains" & ".pdf"
    With fd
        .Title = "Save to PDF"
        .InitialFileName = "\Documents\" & filename
        If .Show = -1 Then
            filename = fd.SelectedItems(1)
            If InStr(filename, ".") = 0 Then
                filename = filename & ".pdf"
            ElseIf Right(filename, 4) <> ".pdf" Then
                k = InStrRev(filename, ".") - 1
                filename = Left(filename, k)
                filename = filename & ".pdf"
            End If

        End If
    End With

    DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, filename

    MsgBox "Report saved to " & filename

    Set fd = Nothing
End Sub

Users who are viewing this thread

Top Bottom