Query text file problems

leocoppens

Board Regular
Joined
Mar 7, 2007
Messages
53
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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 :-
Code:
'=============================================================================
'- 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

Board Regular
Joined
Mar 7, 2007
Messages
53
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,157
Messages
5,835,713
Members
430,381
Latest member
tntcute

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top