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!

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!

hi Robert,

thanks for your reply.

This code does not split each line into 2 lines & nor inserts them into
an array.
It only gets the data into excel sheet under a column & that too
without comparing the column headers in excel with the labels in text
line (on left hand-side).

Rgds,

Sifar

The Readline function returns a string. I've just output it to a worksheet
so you can see how to read info from a file. It should be a simple matter to
manipulate that string into the format that you want using the left, right,
and instr functions.

I could just as easily have done this but that wouldn't have displayed a
result

dim myarray(1 to 100) as string
dim working as string

Do While Not readfile.AtEndOfStream And upto < 100
upto = upto + 1

'I meant that you should manipulate you strings, skip blank lines,
'all that stuff right here. This isn't all of it but something like:

working = readfile.Readline
working = right(working, len(working) - instr(working, ":",
vbTextCompare))

myarray(upto) = working
Loop


I haven't written a complete working solution for you because it's very
difficult to do with seeing the file.

- Rm


"sifar" wrote:

hi Robert,

thanks for your reply.

This code does not split each line into 2 lines & nor inserts them into
an array.
It only gets the data into excel sheet under a column & that too
without comparing the column headers in excel with the labels in text
line (on left hand-side).

Rgds,

Sifar

Hi Robert,

I could send you a copy of the excel workbook what i am trying to
create, if you

provide me with your email address. Also, could send you the Mail
Message from

which i extract data & create the text file for Reading into Excel
Sheet.

Here's the code:
'====================================
Option Explicit


Sub ProcessAll()
Dim rngSubject
Dim retVal

Set rngSubject = shtSetup.Range("A14")
Do While rngSubject.Value < ""
retVal = ProcessOutlookMessages(Trim(rngSubject.Value), _
Trim(rngSubject.Offset(0, 1).Value),
_
Trim(rngSubject.Offset(0, 2).Value))

If retVal = 0 Then
rngSubject.Offset(0, 3).Value = rngSubject.Offset(0, 3).Value +
retVal

Else
'negative return value means error
MsgBox "Could not process mails"
Exit Sub
End If

Set rngSubject = rngSubject.Offset(1, 0)
Loop

End Sub


' Extract information from mail items with defined subject and
attachment
Function ProcessOutlookMessages(MailSubject As String, DataSheet As
String, _
ProcessedFolder As String)

Dim olApp As Outlook.Application
Dim fInbox As MAPIFolder
Dim olFolderArchive As Object
Dim olInboxCollection As Object
Dim olInboxItem As Object

Dim TempPath As String
Dim itemCount, i, n, iCount

'temp files saved here
TempPath = ThisWorkbook.Path & "\TempFile.txt"

'If this is the first time calling the sub - get a ref to Outlook

' (must be already running)
If olApp Is Nothing Then
Set olApp = GetOutlook()
End If

'should have it by now....
If olApp Is Nothing Then
ProcessOutlookMessages = -1
Exit Function
End If

'Get the inbox folder
Set fInbox =
olApp.GetNamespace("MAPI").GetDefaultFolder(olFold erInbox)
Set olInboxCollection = fInbox.Items
itemCount = olInboxCollection.Count

iCount = 0
For n = itemCount To 1 Step -1
Set olInboxItem = olInboxCollection(n)
'check it's an email and not something else
If TypeName(olInboxItem) = "MailItem" Then

If StrComp(Trim(olInboxItem.Subject), MailSubject) = 0 Then

'Look for archive folder and create if doesn't exist.
Set olFolderArchive = EnsureInboxFolder(fInbox,
ProcessedFolder)

olInboxItem.SaveAs TempPath, olTXT
ProcessFile TempPath, DataSheet
'move the mail to the archive folder
olInboxItem.Move olFolderArchive
iCount = iCount + 1
End If
End If
Next n

ProcessOutlookMessages = iCount
Exit Function

haveError:
If Err < 0 Then MsgBox "Error:" & vbCrLf & Err.Description

End Function


Sub ProcessFile(TempFilePath As String, WorkSheetName As String)
'#######################
'code goes here to process the temp file and extract the contents. NEED

CODE FOR THIS SUB...
'#######################
End Sub


Function GetOutlook() As Object
Dim olApp As Object
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
On Error GoTo 0
If olApp Is Nothing Then
MsgBox "Outlook is not running: please open the application first"

End If

Set GetOutlook = olApp
End Function

Function EnsureInboxFolder(oInbox, FolderName) As Object
Dim oFold As Object

'Look for archive folder and create if doesn't exist.
On Error Resume Next 'ignore error
Set oFold = oInbox.Folders(FolderName)
If Err.Number < 0 Then Err.Clear

If oFold Is Nothing Then
Set oFold = oInbox.Folders.Add(FolderName, olFolderInbox)
End If

Set EnsureInboxFolder = oFold
End Function
'=================================

Also, Here's the Source code of the MS Outlook Mail Message in html.
Copy this

into Frontpage & then SaveAs Modem Order.Msg file.

'===================================

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"
<HTML<HEAD

<META content="MSHTML 6.00.2800.1515" name=GENERATOR</HEAD
<BODY
<DIV<FONT face=Arial size=2
<DIV class=style1 align=center<STRONG
<DIV class=style1 align=center<FONT color=#ff0000New Modem Escalation

Form</FONT</DIV
<DIV class=style1 align=center&nbsp;</DIV</STRONG<FONT face=Arial
size=2</FONT</DIV</FONT</DIV
<DIV id=Layer1
<TABLE width=476 align=center border=4
<TBODY
<TR
<TD width=277CSA NAME: </TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005James

Roy</SPAN</FONT</TD</TR
<TR
<TDTeam Leader's name:</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005Cyrus

Broacha</SPAN</FONT</TD</TR
<TR
<TDDate:</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-2809200529/09/2005</SPAN</FONT</TD</TR
<TR
<TDTime:</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-2809200512:00
pm</SPAN</FONT</TD</TR
<TR
<TDCustomer Forename:</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005raj</SPAN</FONT</TD</TR
<TR
<TDCustomer Surname:</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005jutsi</SPAN</FONT</TD</TR
<TR
<TDCustomer Email Address:</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005<A

</A</SPAN</FONT</TD</TR
<TR
<TDAddress 1 (House Number/Name):</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005abc</SPAN</FONT</TD</TR
<TR
<TDAddress 2 (Road):</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005def</SPAN</FONT</TD</TR
<TR
<TDAddress 3 (Town/City):</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005jkl</SPAN</FONT</TD</TR
<TR
<TDAddress 4 (Country):</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005mno</SPAN</FONT</TD</TR
<TR
<TDPostcode:</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005be 23
of1</SPAN</FONT</TD</TR
<TR
<TDFSBB Number:</TD
<TD width=150<FONT face=Arial size=2<SPAN


class=538401720-28092005FSBB1234</SPAN</FONT</TD</TR</TBODY</TABLE</DIV
<DIV<FONT face=Arial size=2</FONT&nbsp;</DIV
<DIV<FONT face=Arial size=2</FONT&nbsp;</DIV
<DIV id=Layer2
<TABLE height=51 cellSpacing=0 cellPadding=0
<TBODY
<TR
<TD width=711 bgColor=#cccccc height=42
<H3 align=centerThe following part has to be pasted in VV as a
part of
the resolution before the modem escalation. Answer Applicable
questions in
Y/N Format. </H3
<DIV
<TABLE width=661 align=center border=4
<TBODY
<TR
<TD width=645Modem light status &amp; Error No :</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005y</SPAN</FONT</TD</TR
<TR
<TDPowercycled the modem :</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005y</SPAN</FONT</TD</TR
<TR
<TDReinstalled the modem software :</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005n</SPAN</FONT</TD</TR
<TR
<TDSwapped the USB ports :</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005y</SPAN</FONT</TD</TR
<TR
<TDTried other USB devices in the same port :</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005y</SPAN</FONT</TD</TR
<TR
<TDUnplugged all other USB devices and then solely plugged
the USB
modem :</TD
<TD width=150<FONT face=Arial size=2<SPAN
class=538401720-28092005y</SPAN</FONT</TD</TR
<TR
<TDMade a manual connection and selected the USB modem (if
applicable) :</TD
<TD width=150<SPAN class=538401720-28092005<FONT face=Arial

size=2y</FONT</SPAN</TD</TR
<TR
<TDChecked for the BB setup (if applicable) :</TD
<TD width=150<SPAN class=538401720-28092005<FONT face=Arial

size=2y</FONT</SPAN</TD</TR
<TR
<TDConnected the modem to the master socket (if applicable)
:</TD
<TD width=150<SPAN class=538401720-28092005<FONT face=Arial

size=2y</FONT</SPAN</TD</TR
<TR
<TDSwapped the filters (if applicable)</TD
<TD width=150<SPAN class=538401720-28092005<FONT face=Arial

size=2n</FONT</SPAN</TD</TR
<TR
<TDEscalated for a woosh, If yes, does it suggest a Faulty
Equipment Issue:</TD
<TD width=150<SPAN class=538401720-28092005<FONT face=Arial

size=2n</FONT</SPAN</TD</TR
<TR
<TDDid a BT Escalation?, If yes, then does the Fault Report
Suggest
a Modem Fault:</TD
<TD width=150<SPAN class=538401720-28092005<FONT face=Arial

size=2n</FONT</SPAN</TD</TR
<TR
<TDChecked for Power Management System: </TD
<TD width=150<SPAN class=538401720-28092005<FONT face=Arial

size=2y</FONT</SPAN</TD</TR
<TR
<TDChecked Device Manager for USB chipset (IF status says
"VIA" or
"SYS", cmr needs to upgrade chipset)</TD
<TD width=150<SPAN class=538401720-28092005<FONT face=Arial

size=2y</FONT</SPAN</TD</TR
<TR
<TDDisabled Firewall :</TD
<TD width=150<SPAN class=538401720-28092005<FONT face=Arial

size=2y</FONT</SPAN</TD</TR
<TR
<TDIf all the above steps are satisfied only then a modem
replacement can be sent for. :</TD
<TD width=150<SPAN class=538401720-28092005<FONT face=Arial

size=2y</FONT</SPAN</TD</TR</TBODY</TABLE</DIV<SPAN

class=538401720-28092005<FONT face=Arial
size=2</FONT</SPAN</TD</TR</TBODY</TABLE</DIV<BR
<DIV<FONT face=Arial size=2</FONT&nbsp;</DIV</BODY</HTML

'=========================================

Now, Here's the text file which gets generated!
'=========================================




'=========================================

Hope This Helps!

Rgds,

Sifar