Read a single column text file

[mluetkem]

Looking for a fast easy way to efficiently read a text file into a Exce
worksheet. The text file is a single column of real numbers. Size o
the text file can be 20,000+ lines long. I want to read in the tex
file and place into column B starting at row 7. The text file an
Excel workbook are in the same directory. The size (length) of tex
file will vary from run to run, but usually around 20,000 lines i
length.

Mik

 

[Tom Ogilvy]

Dim rng as Range, rng1 as Range
set rng = Activesheet.Cells(7,2)
workbooks.Open Filename:=thisWorkbook.Path & "\text1.txt"
With Activesheet
set rng1 = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End With
rng1.copy Destination:=rng
activeworkbook.close savechanges:=False

 

[Dana DeLouis]

Would any ideas here help? The file "C:\junk.txt" would be your text file.

Sub Demo()
'// Dana DeLouis
With ActiveSheet.QueryTables.Add( _
Connection:="TEXT;C:\junk.txt", _
Destination:=Range("B7"))

.RefreshStyle = xlOverwriteCells
.AdjustColumnWidth = True
.TextFileParseType = xlDelimited
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

 

[Tom Ogilvy]

Just some added info: this doesn't work in Excel 97 or earlier. The
ability to import directly into a worksheet was added in Excel 2000.