[COLOR="Navy"]Sub[/COLOR] Foo()
[COLOR="Navy"]Dim[/COLOR] FSO [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR] [COLOR="SeaGreen"]'//FileSystemObject[/COLOR]
[COLOR="Navy"]Dim[/COLOR] ws [COLOR="Navy"]As[/COLOR] Worksheet
[COLOR="Navy"]Dim[/COLOR] ts [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR] [COLOR="SeaGreen"]'//TextStream[/COLOR]
[COLOR="Navy"]Dim[/COLOR] lngCount [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] sCellToStartAt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] s [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] a [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Variant[/COLOR]
[COLOR="Navy"]Dim[/COLOR] sFilePath [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]GoTo[/COLOR] Handler:
[COLOR="SeaGreen"]'------------------------------------------------------[/COLOR]
[COLOR="SeaGreen"]'//Source text file[/COLOR]
sFilePath = "C:\myTemp\100731_SENSOR_Test_PM.txt"
[COLOR="SeaGreen"]'//Worksheet to write to[/COLOR]
[COLOR="Navy"]Set[/COLOR] ws = Workbooks("Book2.xls").Worksheets(1)
[COLOR="SeaGreen"]'//Range to start at[/COLOR]
sCellToStartAt = "$A$1"
[COLOR="SeaGreen"]'------------------------------------------------------[/COLOR]
[COLOR="SeaGreen"]'//Read file[/COLOR]
[COLOR="Navy"]Set[/COLOR] FSO = CreateObject("Scripting.FileSystemObject")
[COLOR="Navy"]Set[/COLOR] ts = FSO.OpenTextFile(sFilePath, 1, False, -2) [COLOR="SeaGreen"]'//ForReading, Create=False, TristateUseDefault[/COLOR]
[COLOR="SeaGreen"]'//Write rows[/COLOR]
lngCount = 0
[COLOR="Navy"]Do[/COLOR] [COLOR="Navy"]While[/COLOR] [COLOR="Navy"]Not[/COLOR] ts.AtEndOfStream
s = ts.ReadLine
[COLOR="SeaGreen"]'//Only read lines on or after column headers[/COLOR]
[COLOR="Navy"]If[/COLOR] Left(s, 3) = "Box" [COLOR="Navy"]Or[/COLOR] Left(s, 3) = "FDU" [COLOR="Navy"]Then[/COLOR]
a = Split(s, Chr(9)) [COLOR="SeaGreen"]'//break into "cells" on tab delimiters[/COLOR]
ws.Range(sCellToStartAt).Offset(lngCount, 0).Resize(1, UBound(a) + 1) = a
lngCount = lngCount + 1
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]Loop[/COLOR]
My_Exit:
[COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] ts [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
ts.Close
[COLOR="Navy"]Set[/COLOR] ts = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] FSO [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]Set[/COLOR] FSO = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Handler:
MsgBox "Error: " & Err.Description
[COLOR="Navy"]Resume[/COLOR] My_Exit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]