CODEWITHSUNDEEP
exceltextimportvba
skyline01
skyline01

Reputation: 2101

importing multiple text files into separate Excel sheets in 1 Excel book using VBA

I have written a VBA macro for importing many text files (from 1 folder) onto separate sheets in 1 Excel workbook. All files read into each separate sheet just fine. However, I am seeing a field placement issue. The headers on each text file are the same. But, the field values themselves sometimes get pushed down by a few fields. So, not all fields values line up under their proper headers. Can anybody suggest to me why this is happening? I have tried seeing if it's a tab-delimited or a pipe-delimited issue, but that doesn't seem to be the problem.

Sub MultipleTextFilesIntoExcelSheets()
    Dim i As Integer 'a counter to loop through the files in the folder
    Dim fname As String, FullName As String 'fname is the name of the file, and FullName is the name of its path
    Dim ws As Worksheet 'a workbook object for the workbook where the current macro is running

i = 0 'seed the counter

'get the name of the first text file
fname = Dir("C:\dummy_path\*txt")

'loop through the text files to put them onto separate sheets in the Excel book
While (Len(fname) > 0)
    'get the full path of the text file
    FullName = "C:\dummy_path\" & fname
    i = i + 1 'get ready for the next iteration

    Set ws = ThisWorkbook.Sheets("Sheet" & i) 'the current sheet

    With ws.QueryTables.Add(Connection:="TEXT;" & FullName, Destination:=ws.Range("A1"))
        .Name = "a" & i
        .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    'we are using a tab-delimited file
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
        fname = Dir
    End With
Wend

End Sub

Upvotes: 0

Views: 11770

Answers (2)

Ruth
Ruth

Reputation: 11

The "treating F as delimiter" problem is because of this line:

.TextFileOtherDelimiter = False

Removing it makes the VBA work as expected. The default value for TextFileOtherDelimiter should be "null" rather than False I think.

Upvotes: 1

Bernard Saucier
Bernard Saucier

Reputation: 2270

Change .ConsecutiveDelimiter = False to .ConsecutiveDelimiter = True

SOME DETAILS : This probably happens due to the fact that there might be more than one tab delimiting the "columns". Changing this parameter will allow for multiple tabs to be accepted as one.

Upvotes: 0

Related Questions