Handling user actions when exporting range as .txt file

[edith88beatrix]

Hi!
I have a code to export a selected range from a sheet as .txt file. It works fine. It opens a save as window.
If the user saves the file, a confirmation message appears "File saved successfully".
However, if the user chooses Cancel, the same message appears.
How can I edit the code, so that when the user chooses Cancel, nothing happens (no prompt)? The Excel itself should remain open so the user can continue to work on it.

Sub SaveToText()
Dim myFile As String, rng As Range, cellValue As String, i As Integer, j As Integer
currentDateAndTime = Now()
myFile = Application.GetSaveAsFilename(fileFilter:="Text (*.txt), *.txt")
Set rng = Selection
Open myFile For Output As #1
For i = 1 To rng.Rows.Count
 For j = 1 To rng.Columns.Count
 cellValue = rng.Cells(i, j).Value
 If j = rng.Columns.Count Then
 Print #1, cellValue
 'Write #1, If you want the data as comma separator
 Else
 Print #1, cellValue,
 'Write #1, If you want the data as comma separator
 End If
 Next j
Next i
Close #1
MsgBox ("File saved successfully.")
End Sub

Thank you!

 

[duggie33]

I left your code largely intact. There is an added check to see if the Cancel button was selected...myFile = "False" if the Cancel button is selected.

Sub SaveToText()

    Dim myFile As String, rng As Range, cellValue As String, i As Integer, j As Integer

    myFile = Application.GetSaveAsFilename(fileFilter:="Text (*.txt), *.txt")

    If myFile <> "False" Then
        Set rng = Selection

        Open myFile For Output As #1

        For i = 1 To rng.Rows.Count
            For j = 1 To rng.Columns.Count
                cellValue = rng.Cells(i, j).Value
                If j = rng.Columns.Count Then
                    Print #1, cellValue
                    'Write #1, If you want the data as comma separator
                Else
                    Print #1, cellValue,
                    'Write #1, If you want the data as comma separator
                End If
            Next j
        Next i

        Close #1

        MsgBox ("File saved successfully.")
    End If

End Sub

I hope this helps,

Doug

 

[edith88beatrix]

Thank you very much for your help, Doug!

 

[duggie33]

Glad to help. Thanks for the feedback.

Doug