Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transposing text in text file to Excel Sheet.
Hi,
I am working on a code which will process a text file (TempFile.txt), which contains a data copied from an Outlook .msg file containing a table (single table-2 Columns data). The text file contains text extracted from various MS Outlook .msg files & copied to a TempFile.txt. The data in generated textfile looks like : Agent's Name Thomas Rody Gender Male etc.... What i want to do is to transpose the data under the same labels used as Column Headers in an Excel sheet. Code will look something like.... Sub ProcessTempFile(TempFileName as String, Datasheet as Worksheet) .... I donot know how to extract data from a text file & tranpose horizontally under each header column e.g. Column A Column B Column C Column D --------------------------------------------------- Agent's Name Thomas Rody Gender Male Please Help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transposing text in text file to Excel Sheet.
see if this will get you going. i put my tempfile.txt in c:\files. look for
that in the code and change it to your location. also, watch for outlook express truncating any lines of code Dim RowNdx As Integer Dim ColNdx As Integer Dim WholeLine As String Dim FName As Variant Dim sRow As Integer Dim lRow As Range Dim fil As Variant Dim FileDir As Variant Dim x As Integer Dim FilesInPath As String Dim MyFiles() As String Dim NumberOfFiles As Long Dim lastrow As Long Dim LastCol Dim xR As Integer Sub ImportText() Application.ScreenUpdating = False FileDir = "C:\Files\" Sheets("sheet1").Range("A1").Select ColNdx = ActiveCell.Column RowNdx = ActiveCell.Row sRow = RowNdx ' test for existing data If Range("a1") "" Then MsgBox "Append And Clear Before" & Chr(10) & "Performing Another Import" Exit Sub End If ' determine # of files FilesInPath = Dir(FileDir & "\*.txt") NumberOfFiles = 0 If FilesInPath = "" Then MsgBox "No files found" Exit Sub End If ' perform import of email files Do While FilesInPath < "" Open FileDir & FilesInPath For Input Access Read As #1 While Not EOF(1) Line Input #1, WholeLine Cells(RowNdx, ColNdx).Value = WholeLine ColNdx = ColNdx + 1 Wend RowNdx = RowNdx + 1 Close #1 NumberOfFiles = NumberOfFiles + 1 ReDim Preserve MyFiles(1 To NumberOfFiles) MyFiles(NumberOfFiles) = FilesInPath FilesInPath = Dir() Loop Application.ScreenUpdating = True End Sub -- Gary "sifar" wrote in message oups.com... Hi, I am working on a code which will process a text file (TempFile.txt), which contains a data copied from an Outlook .msg file containing a table (single table-2 Columns data). The text file contains text extracted from various MS Outlook .msg files & copied to a TempFile.txt. The data in generated textfile looks like : Agent's Name Thomas Rody Gender Male etc.... What i want to do is to transpose the data under the same labels used as Column Headers in an Excel sheet. Code will look something like.... Sub ProcessTempFile(TempFileName as String, Datasheet as Worksheet) ... I donot know how to extract data from a text file & tranpose horizontally under each header column e.g. Column A Column B Column C Column D --------------------------------------------------- Agent's Name Thomas Rody Gender Male Please Help! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transposing text in text file to Excel Sheet.
Hi Gary,
The TempFile looks like this: Start of text file....................................... Subject: Modem Order ! Fill in the dotted boxes ! -------------------------------------------------------------------------------- MODEM ORDER [FAULTY MODEM] 1. CSA Name jazz melano 2. Team Leader's name Mani Ratnam 3. Date 23/09/2005 4. Time 0:13:00 PM GMT 5. Customer Forename Wendy 6. Customer Surname dorchester 7. Customer Email Address test.gonet.co.uk 8. Address 1 (House Number/Name) 22, looney circle 9. Address 2 (Road) FairyLand 10. Address 3 (Town/City) Sussex 11. Address 4 (Country) Rodeo 12. Postcode SR2 0AL 13. FSBB Number FSBB21112214 14. Account Number 15. NLD Number 16. Additional Notes End of text file -------------------------------------------------- Now the Text file not only contains just one Escalation data extracted from MSOutlook Inbox but many with the same Subject. Each label value is placed under same label columns in an Excel sheet named "Modem Order" starting from the 2nd Row (as 1st Row contains the Labels). e.g Column Labels in 1st Row of Excel sheet are : 1. CSA Name 2. Team Leader's name 3. Date So, need to compare somehow the data in textfile with Excel Column Labels & then paste their values under them in Excel Worksheet. e.g. 1. CSA Name 2. Team Leader's name 3. Date Mani Ratnam Mani Ratnam 23/09/2005 Jazz Melano James Hadley 25/09/2005 Hosey Rathod Uri Geller 01/10/2005 Anthony Gonalves Johnny Liver 03/10/2005 Etc.... How to do this???? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Split text file into Excel sheet and separate the final results intoa new sheet | Excel Worksheet Functions | |||
Exporting Excel Sheet With Additional Info Added To A Text File | Excel Discussion (Misc queries) | |||
Problems Autosaving an excel file/sheet as Text | Excel Discussion (Misc queries) | |||
Excel VBA - open text file, replace text, save file? | Excel Programming | |||
How do I read text file in an EXCEL SHEET | Excel Programming |