Read a single column text file

  • Thread starter Thread starter mluetkem
  • Start date Start date
M

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
 
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
 
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
 
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.
 
Back
Top