|
Subject:
|
Text Import- How to import the first 5 lines.
|
|
Posted By:
|
nickzhang.zn
|
Post Date:
|
8/4/2007 5:54:05 AM
|
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(TargetAddress)
' 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
|
|
Reply By:
|
nickzhang.zn
|
Reply Date:
|
8/4/2007 5:06:09 PM
|
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
|
|
