Search code examples
excelexcel-2007vba

Error after closing file explorer


I created a pretty good working vba code for importing a csv file from the windows file explorer. However, when I close the explorer before I open a file, a 1004 error dialog pops up. It says the text file to refresh the external range can't be found. The line line at the bottom should be the cause:

.Refresh BackgroundQuery:=False

Does anyone have an idea how to get rid of this error?

Dim ClickCount As Integer

Sub CommandButton1_Click()

Dim sht As Worksheet
Dim LastRow As Long
Dim begin As String
Dim myInput As Long

ClickCount = ClickCount + 1

If ClickCount > 1 Then GoTo Line1 Else GoTo Line2

Line1:
myInput = MsgBox("Gebruikers zijn reeds geimporteerd. Records worden mogelijk dubbel opgeslagen. Wilt u toch doorgaan met importeren?", vbCritical + vbYesNo, "Import error")
    If myInput = vbYes Then
    GoTo Line2
    Else
    Exit Sub

Line2:

Set sht = ActiveSheet

  LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
begin = "$A" & "$" & LastRow + 1

Dim fileName
  fileName = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv),*.csv")

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & fileName, _
        Destination:=range(begin))
        .Name = "User import 1.0"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
  End If
End Sub

Solution

  • Dim fileName
    fileName = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv),*.csv")
    
    If fileName = False then Exit Sub