Macro function to read text file
 

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.

Macro function to read text file
 

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.
--
Gary''s Student - gsnu200745


"hmm" wrote:

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.

Macro function to read text file
 

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

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200745


"hmm" wrote:

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.