'Importing txt file into excel; QueryTables

Im trying to import data's from TXT file to my Excel sheet.

It works fine with below VBA macro but the only problem is after re-opening the excel file macro is trying to look the txt directory and when it can not find it, it is giving an error.

I didnot mean to put such command there but now i do not know how to disable it. Do you guys have an idea about what should i change to disable that function?

Sub test_9()
Dim jess916 As Variant, FullPath As String
Set jess916 = Application.FileDialog(msoFileDialogFilePicker)
With jess916
    .InitialView = msoFileDialogViewDetails
    .InitialFileName = ThisWorkbook.Path
    .Filters.Add "Open File ", "*.txt", 1
    .ButtonName = "Import file"
    .Title = " jess916c Search for .txt file to Import"
    If .Show = -1 Then
        FullPath = .SelectedItems(1)
Else:
        Exit Sub
    End If
End With
With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;" & FullPath, Destination:=Range("A2"))
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = True
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = False
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 850
    .TextFileStartRow = 9
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With
End Sub

vba excel

Solution 1:^[1]

I use the On Error Goto to handle that in my code. See below.

Sub ImportData()
    Application.ScreenUpdating = False
    Dim intChoice As Integer
    Dim strPath As String

    'only allow the user to select one file
    Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
    'make the file dialog visible to the user
    intChoice = Application.FileDialog(msoFileDialogOpen).Show
    'determine what choice the user made
    If intChoice <> 0 Then
        'get the file path selected by the user
        strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
    End If
    'Import data from file
    On Error GoTo errorHandler
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & strPath, Destination:=Range("$A$1"))
        .Name = "MemoQ Data Range"
        .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 = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

    Application.ScreenUpdating = True
    Exit Sub

errorHandler:
    Exit Sub
End Sub

Solution 2:^[2]

Set RefreshOnOpenFile to False and that will solve your issue.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Chau Nguyen
Solution 2	Jeremy Caney

'Importing txt file into excel; QueryTables

Solution 1:[1]

Solution 2:[2]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]