I seem to be having a random but frustrating problem with one of my macros. My macro simply opens a .txt file in a new sheet with comma and space delimitation. The problem is that I don't have any sort of colon delimiter code in my code, but when importing the data it sometimes decides it needs to treat the colons in my .txt file as a delimiter (Example: Each line of .txt file has a time stamp of HH:MM:SS that should all be in one cell, but it splits it into 3 cells). I do not understand why this is and cannot have this as other code depends on the location of cells within the sheet when the colons are not delimited.

Here is the code I am using.

    Set NewSheet = Sheets.Add
    NewSheet.Name = "TEMP"
    Sheets("TEMP").Select
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & ThisWorkbook.Path & "\" & FileName, _
        Destination:=Range("A1"))
        .Name = "DATA"
        .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 = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(3, 2, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
BTW the timestamp is suppose to be in the second column, which I have set to text format if that matters. (Columns should go Date, Timestamp, AM/PM, Data, Data, Data......)

Anybody have any ideas on why this is happening?

Is there any .TextFileColonDelimeter type function I can set to = False? (I can't find such thing)

I noticed if you did want a colon delimiter the code is .TextFileOtherDelimiter = ":"... would something like .TextFileOtherDelimiter = "NULL" be beneficial in anyway?

I am stumped...

Thanks in advance!

Eric