Macro function to read text file

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

hmm - 16 Sep 2007 14:08 GMT

I am looking for a macro function that will read tab-separted pairs of x-y
data from a text file. For example, it could be ReadLine(filename,
line_number), which would return a line of text that I can manipulate using
worksheet functions to get the x and y values. It could, but does not have
to be, smarter, and return actual x and y values, i.e, {ReadPoint(filename,
line_number)}, a two-element array returning x and y values. This is all for
the purpose of eventually charting the data.

Reply to this Message

Gary''s Student - 16 Sep 2007 14:18 GMT

Right from the macro Recorder:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/16/2007 by jim ravenswood
'

'
ChDir "C:\"
Workbooks.OpenText Filename:="C:\sample.txt", Origin:=437, StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter _
:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _
Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
TrailingMinusNumbers:=True
End Sub

By using the Recorder, you can capture all the information you supply the
Import Wizard manually.

Signature

Gary''s Student - gsnu200745

> I am looking for a macro function that will read tab-separted pairs of x-y
> data from a text file. For example, it could be ReadLine(filename,
[quoted text clipped - 3 lines]
> line_number)}, a two-element array returning x and y values. This is all for
> the purpose of eventually charting the data.

Reply to this Message

Joel - 16 Sep 2007 14:58 GMT

If you want to read the values in VBA without using the worksheet then use
this code

Sub TextStreamTest()
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fs, f, ts, s

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile("c:\temp\abc.txt")
Set ts = f.OpenAsTextStream(ForReading, TristateUseDefault)
Do While ts.atendofstream <> True
InputLine = ts.ReadLine
x = Val(Left(InputLine, _
InStr(InputLine, ",") - 1))
y = Val(Mid(InputLine, _
InStr(InputLine, ",") + 1))
Loop
ts.Close
End Sub

> Right from the macro Recorder:
>
[quoted text clipped - 24 lines]
> > line_number)}, a two-element array returning x and y values. This is all for
> > the purpose of eventually charting the data.

Reply to this Message