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
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,193
Messages
5,594,767
Members
413,933
Latest member
Msar5586

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