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

[gojets1721]

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

 

[bastanu]

Move the Docmd.OutputTo line inside the If .Show statement. See link for details:

FileDialog.Show method (Office)

Office VBA reference topic

docs.microsoft.com

Cheers,

 

[gojets1721]

That worked. THanks!