Error canceling file import in Excel

Question

I’m building a Macro for importing a file in Excel. At some point, when the file location is requested, in case the Worksheet cancel import option, the following error message is returned:

Excel cannot find the text file to refresh this External data range

After that the macro execution is terminated. Checking the Debug. the error occurs in the parameter .Refresh BackgroundQuery:=False of the procedure.

Below the import code:

Public Sub btnImportarTXT_Click()

    ' Sub para importar arquivo .TXT

    Dim Dir As String

    dirArquivo = Application.GetOpenFilename(FileFilter:="Text File, *.txt")

    With Sheets("Extract").QueryTables.Add(Connection:="TEXT;" & dirArquivo, Destination:=Range("Extract!$A$1"))
        .Name = "extract"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

    ' Worksheets("Extract").Protect UserInterfaceOnly:=True

    MsgBox "Arquivo importado!", vbOKOnly, "Sucesso!"

End Sub

Answer 1

The method Application.Getopenfilename returns a value of type Variant, which can be a file, a file array or the boolean value false if the operation is canceled, then you can do a check before starting the import, something like this:

Public Sub btnImportarTXT_Click()

    ' Sub para importar arquivo .TXT

    Dim Dir As String

    dirArquivo = Application.GetOpenFilename(FileFilter:="Text File, *.txt")

    If dirArquivo <> False Then ' verifica se o usuário cancelou a importação
        With Sheets("Extract").QueryTables.Add(Connection:="TEXT;" & dirArquivo, _
        Destination:=Range("Extract!$A$1"))
            .Name = "extract"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 1252
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With

        ' Worksheets("Extract").Protect UserInterfaceOnly:=True

        MsgBox "Arquivo importado!", vbOKOnly, "Sucesso!"
    Else
        MsgBox "Importação cancelada!", vbOKOnly, "Aviso!"
    End If

End Sub

Answer 2

Your comment explains that "cancel" is about the user canceling the dialog box that asks for a file to be selected.

In this case where the user decided not to import the file, you must "skip" the code that does the import.

The method Application.GetOpenFilename returns the file name if it is selected and returns Fase if the user clicked on "cancel" in the dialog box (it’s, I know, kind of weird).

Then you can test the return of Getopenfilename and only run the import code if the return is different from False. Sort of like this:

dirArquivo = Application.GetOpenFilename(FileFilter:="Text File, *.txt")

if dirArquivo <> False then
    'código para importar aqui.
end if