Hi all,

This is the code which can import all the data from a text file to Excel 2003. I am trying to only import the first 10 lines, it will be very appreciated if someone can help.

Sub Import()
    'ImportTextFile FName:="C:\Nick\1.txt", Sep:=" "
    'ImportRangeFromDelimitedText SourceFile = "C:\Nick\1.txt", SepChar = " ", TargetWB = ThisWorkbook.Name, TargetWS = "ImportSheet", TargetAddress = "A3"
    'ImportRangeFromDelimitedText "C:\Nick\1.txt", _ " ; ", ThisWorkbook.Name, "ImportSheet", "A3"
    ImportRangeFromDelimitedText "C:\Nick\1.txt", _
    " ", ThisWorkbook.Name, "ImportSheet", "A1"
End Sub
Sub ImportRangeFromDelimitedText(SourceFile As String, SepChar As String, _
    TargetWB As String, TargetWS As String, TargetAddress As String)
' Imports the data separated by SepChar in SourceFile to
' Workbooks(TargetWB).Worksheets(TargetWS).Range(Tar getAddress)
' Replaces existing data in Workbooks(TargetWB).Worksheets(TargetWS)
' without prompting for confirmation
' Example:
' ImportRangeFromDelimitedText "C:\FolderName\DelimitedText.txt", _
    ";", ThisWorkbook.Name, "ImportSheet", "A3"


Dim SC As String * 1, TargetCell As Range, TargetValues As Variant
Dim r As Long, fLen As Long
Dim fn As Integer, LineString As String
Dim coll As Variant

    ' validate the input data if necessary
    If Dir(SourceFile) = "" Then Exit Sub ' SourceFile doesn't exist
    If UCase(SepChar) = "TAB" Or UCase(SepChar) = "T" Then
        SC = Chr(9)
    Else
        SC = Left(SepChar, 1)
    End If

    ' perform import
    Workbooks(TargetWB).Activate
    Worksheets(TargetWS).Activate
    Set TargetCell = Range(TargetAddress).Cells(1, 1)
    On Error GoTo NotAbleToImport
    fn = FreeFile
    Open SourceFile For Input As #fn
    On Error GoTo 0
    fLen = LOF(fn)
     r = 0
    While Not EOF(fn)
        Line Input #fn, LineString

        TargetValues = Split(LineString, SC, -1, vbBinaryCompare) ' Excel 2000 or later
        UpdateCells TargetCell.Offset(r, 0), TargetValues
        r = r + 1
    Wend
    Close #fn
    Application.Calculation = xlCalculationAutomatic
NotAbleToImport:

    ' clean up
    Set TargetCell = Nothing
    Application.StatusBar = False
End Sub



Sub UpdateCells(TargetRange As Range, TargetValues As Variant)
' Writes the content of the variable TargetValues to
' the active worksheet range starting at TargetRange
' Replaces existing data in TargetRange without prompting for confirmation
Dim r As Long, c As Integer
    If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
    r = 1
    c = 1
    On Error Resume Next
    c = UBound(TargetValues, 1)
    r = UBound(TargetValues, 2)
    Range(TargetRange.Cells(1, 1), _
        TargetRange.Cells(1, 1).Offset(r - 1, c - 1)).Formula = TargetValues
    On Error GoTo 0
End Sub

Cheers

And I found some tips, but I cant use it properly, can anyone help? Thank you very much!

" If you want to read a known count of lines from the start or within a text file, use a variable to store the line count you have read:
E.g.:
Set coll = New Collection
i = 0
Do While Not EOF(fn) And i < 10
Line Input #fn, coll.Add(strLine) ' add line to a collection
i = i + 1
Loop"


Cheers,
Nick