Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you save an excel file to be read as IBM-type text file ? | Excel Worksheet Functions | |||
how to create a macro/vba to read an text file and filled colluns on excel+vloockp | Excel Programming | |||
read text file | Excel Programming | |||
read text file | Excel Programming | |||
Read text file | Excel Programming |