This will open a text file and read the data. If you want to process multiple
files then have a look at my post in microsoft.public.excel.programming -
Writing directory info to individual cells. Some combination of the two will
do this for you.


Public Sub outputTextFile()

Dim upto As Integer

'Create and object that can access the file system.
Set filesys = CreateObject("Scripting.FileSystemObject")
Set readfile = filesys.OpenTextFile("c:\1.TXT", 1, False)

upto = 0

'loop through each line
Do While Not readfile.AtEndOfStream
upto = upto + 1
'this call returns a string of the next line in the file.
'Do what you want with the info here.
Range("A" & upto) = readfile.Readline
Loop

End Sub



- Rm


"sifar" wrote:

Hi,

I am trying to read data from a no of Text files (usually of same
format, i.e. 1 table of 2 columns.)into an excel sheet.

e.g. The Data in TEXT file looks like:

Fill Across dotted lines.....
Blank
Mail Subject: FlashLite Order
Blank
Blank
Agent Name: | Smokey Joe
Teams Name: | Mavericks
Date : | 07/10/2005
Time : | 10:00 PM GMT
Blank
Blank
Subscriber#:| FS2212WE
Work ID : | 1234

So, if you see, the Label & Value seperator is a ":" (except for Time).
Also, there are blanks in between the lines.


Now, i have a worksheet with Similar Labels (except Mail Subject which
will be the name of the worksheet to Activate)for Column Headers
starting from "A1".

What i am trying to do is to read each line into a variable (Except
First Line "Fill across dotted lines..."). Discard any Blank lines.
So variable will contain both Label & Value. Then Split & Trim this
variable into 2 parts at delimiter ":", & then get it into an Array.
So Array will contain Label first & then Value e.g. & will not contain
any Blank values.

MyArray(0)=Label1
MyArray(1)=Value1
MyArray(2)=Label2
MyArray(3)=Value2
etc....

Once Array is created, Activate the worksheet with name "FlashLite
Order" from MailSubject value in TEXT File, Compare Labels in Array
with Column Headers in Excel Sheet, & then dump corresponding values
for text file labels under the Excel Column Headers.

Please Help!