Query text file problems

[leocoppens]

Hi,

I'm running a query that collects the data from a text file. The code is the following one:

Set shtX = Sheets("Sheet4")
shtX.Select
Range("DSet3_Range").Select
Selection.Clear
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Ssm\Dataset\SSM459200806MData.txt", Destination:=Range("A1"))
.TextFileTabDelimiter = True
.Refresh
End With

The code runs just fine. The only problem I found is that excel is automatically changing some of the values retrieved.

This happens for example when excel finds the value "11.99". In these kind of cases it changes it to 01.11.1999 and it's impossible for me to "go back" and get the original value as it appears already changed.

Is there any way I can disable this annoying excel feature?

Thanks a lot for your help!

Leo

 

[BrianB]

Often a problem. To bypass the Excel "feature" we have to import the text file line by line and convert the text field(s) as required. Something like this :-

'=============================================================================
'- IMPORT TAB DELIMITED TEXT FILE LINE BY LINE & PROCESS
'- USEFUL WHEN NEEDING TO REFORMAT A FIELD - especially dates
'- This example explicitly converts text format dd/mm/yyyy  to a Date (format cell as required)
'- NB. Can use other delimiters (eg. "," or "|")in the SPLIT() function
'- Brian Baulsom June 2008
'=============================================================================
Sub IMPORT_TAB_DEL()
    '- text file
    Dim MyFile As String
    Dim TextLine As String          ' Line of text
    Dim TextLineSplit As Variant    ' zero base array (fields' contents)
    '-------------------------------------------------------------------------
    '- date conversion
    Dim DateText As String
    Dim MyDateSerial As Variant     ' = DateSerial(year,month,day)
    '-------------------------------------------------------------------------
    '- sheet
    Dim ToSheet As Worksheet
    Dim ToRow As Long
    '-------------------------------------------------------------------------
    '- IMPORT TEXT FILE
    MyFile = "F:\TextTestTab.txt"
    Set ToSheet = Worksheets("TabSplit")
    ToRow = 1
    Open MyFile For Input As #1
    '-------------------------------------------------------------------------
    '- LOOP EACH LINE OF TEXT FILE
    Do Until EOF(1)
        Line Input #1, TextLine
        Application.StatusBar = ToRow
        '- Split tab delimited line into array
        TextLineSplit = SPLIT(TextLine, vbTab, -1, vbTextCompare)
        '----------------------------------------------------------------------
        '- first field is a date
        DateText = TextLineSplit(0) ' get text string
        '- first line contains field headers
        If ToRow = 1 Then
            ToSheet.Cells(ToRow, 1).Value = DateText
        Else
            MyDateSerial = DateSerial(Right(DateText, 4), Mid(DateText, 4, 2), Left(DateText, 2))
            ToSheet.Cells(ToRow, 1).Value = MyDateSerial
        End If
        '-----------------------------------------------------------------------
        '- other columns
        ToSheet.Cells(ToRow, 2).Value = TextLineSplit(1)
        ToSheet.Cells(ToRow, 3).Value = TextLineSplit(2)
        ToSheet.Cells(ToRow, 4).Value = TextLineSplit(3)
        ToSheet.Cells(ToRow, 5).Value = TextLineSplit(4)
        ToRow = ToRow + 1
    Loop
    '-------------------------------------------------------------------------
    '- FINISH
    Close #1
    MsgBox ("Imported " & ToRow - 1 & " rows.")
    Application.StatusBar = False
End Sub
'=============================================================================

 

[leocoppens]

Hi Brian,

I'm taking my time to look at the code, it's very interesting.

I have temporarily changed the query to "add" a couple of zeros in front of the problematic field so excel doesn't take it as a date anymore, but it's a temporary solution until I figure out exactly how your code works.

Thank you very much for your help!

Leo