Import Text File by VBA

Question

this is the problem every time i run it instead of update the old data that i import, it push old data to right side.

anyone can help me on this i want to import text file to the particular area also contain text delimited after import as you can see from below

like import file from to column A3 to z3 something like that i cant find right word to explain it

Sub import()
    rPaht = Sheet5.Range("a1")
    rFileName = Sheet5.Range("b1")
    Sheet5.Range("a4").CurrentRegion.Offset(500, 0).Resize(, 40).Clear
    With Sheet5.QueryTables.Add(Connection:= _
        "TEXT;" & rPaht & "\" & rFileName & ".txt", Destination:=Sheet5.Range("$A$4"))
        .Name = Sheet5.Range("b1").Value
        .TextFilePlatform = 874
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileOtherDelimiter = "?"
        .Refresh BackgroundQuery:=True
    End With
    Sheet5.Range("a1") = rPaht
    Sheet5.Range("b2") = rFileName
End Sub

if you can suggest any new kind of code i would be thankful

explaination include is the best gift for me to study coding

Answer 1

I tried queryTable by recording macro in read a txt file into excel

I think you are missing one critical line

.RefreshStyle = xlOverwriteCells

I tried WITH and WITHOUT this line, the behavior is replace and SHIFT data to right

The default value for RefreshStyle is xlInsertDeleteCells -- Partial rows are inserted or deleted to match the exact number of rows required for the new recordset.

Reference