Sub Gettext()
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TxtDirectory = "C:\temp\"
Const ReadFileName = "test.txt"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Set fsread = CreateObject("Scripting.FileSystemObject")
ReadPathName = TxtDirectory + ReadFileName
Set fread = fsread.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)
RowCount = 1
Do While tsread.atendofstream = False
InputLine = Trim(tsread.Readline)
NewDate = Trim( _
Left(InputLine, InStr(InputLine, " ") - 1))
NewDate = Left(NewDate, 2) & "/" & Mid(NewDate, 3, 2) & _
"/" & Mid(NewDate, 5, 4)
NewDate = DateValue(NewDate)
InputLine = Trim( _
Mid(InputLine, InStr(InputLine, " ") + 1))
Vendor = Trim(Left(InputLine, InStr(InputLine, "$") - 1))
InputLine = Trim( _
Mid(InputLine, InStr(InputLine, "$") + 1))
Firstamount = Val(Trim( _
Left(InputLine, InStr(InputLine, "$") - 1)))
SecondAmount = Val(Trim( _
Mid(InputLine, InStr(InputLine, "$") + 1)))
Range("A" & RowCount) = NewDate
Range("B" & RowCount) = Vendor
Range("C" & RowCount) = Firstamount
Range("D" & RowCount) = SecondAmount
RowCount = RowCount + 1
Loop
tsread.Close
End Sub
"Bob Phillips" wrote:
This might help more than anything I could offer
http://www.mrexcel.com/tip077.shtml
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
"Biochemist" wrote in message
...
I am sorry can you give me more information as I am new to using macro and
the recorder, thanks
"Bob Phillips" wrote:
Turn the macro recorder on, do a DataText To Columns with a space
delimiter, this will give you the code.
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my
addy)
"Biochemist" wrote in message
...
I would like to know how to write a specific macro that handles the
following
format:
download a series of alpha and numbers, the alpha values are always
second
in the location, example would be
Cell A1 = 10102007 truckstop $15.32 $4449.31
Cell A2 = 95102007 office stop store center $22.32 $3465.89
I would like to have the macro able to place dates into A column dates,
B
column vendor name, C column the first $ value, and in the D column the
last
$ value, and have this run for the complete spreadsheet.
Thanks in advance