CODEWITHSUNDEEP
excelvbatextimport
Rose
Rose

Reputation: 203

Importing .txt into Excel with VBA not adding a column

I have this code to import a .txt file to my Excel sheet (Sheet1). In Sheet1 I have the first column with a formula, so I import my text data into B1. This code does it well the first time, however the following ones adds a column to the right, moving the selected cells in the formula of column A. The data is copied in B1 still, but somehow adding a column first. Any help? 

Sheets("Sheet1").Select
Columns("B:F").Select
Selection.ClearContents

Dim Ret

Ret = Application.GetOpenFilename("Text Files (*.txt), *.txt", Title:="Select text file(.txt)")

If Ret <> False Then
    With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;" & Ret, Destination:=Range("$B$1"))
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 2)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
    End With

End If

Upvotes: 1

Views: 823

Answers (1)

jkpieterse
jkpieterse

Reputation: 3006

Change .RefreshStyle = xlInsertDeleteCells to .RefreshStyle = xlOverWriteCells, then run the macro just once. After that you can simply have Excel do the work by hitting the refresh data button on the data tab and get rid of the macro. See: http://jkp-ads.com/articles/importtext.asp

Upvotes: 2

Related Questions