Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
reading txt file and copying the lines in new excel file
Hello all,
I have a txt file from which I have to make separate excel files depending upon the code in the starting of the line. For example: if the txt file looks like: DFW 10 15 14 67 78 DFW 12 18 00 19 68 DFW 11 33 66 56 79 DFW 12 01 55 34 24 NYK 98 00 34 NYK 58 78 00 15 45 NYK 78 34 46 34 56 CID 12 45 77 19 34 Then, I have to make 3 separate excel files as: File one should look like: DFW 10 15 14 67 78 DFW 12 18 00 19 68 DFW 11 33 66 56 79 DFW 12 01 55 34 24 File two should look like: NYK 98 00 34 NYK 58 78 00 15 45 NYK 78 34 46 34 56 File three should look like: CID 12 45 77 19 34 any help would be highly appreciated. Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
reading txt file and copying the lines in new excel file
Hi
Open the txt file in Excel and use this to create seperate sheets http://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message oups.com... Hello all, I have a txt file from which I have to make separate excel files depending upon the code in the starting of the line. For example: if the txt file looks like: DFW 10 15 14 67 78 DFW 12 18 00 19 68 DFW 11 33 66 56 79 DFW 12 01 55 34 24 NYK 98 00 34 NYK 58 78 00 15 45 NYK 78 34 46 34 56 CID 12 45 77 19 34 Then, I have to make 3 separate excel files as: File one should look like: DFW 10 15 14 67 78 DFW 12 18 00 19 68 DFW 11 33 66 56 79 DFW 12 01 55 34 24 File two should look like: NYK 98 00 34 NYK 58 78 00 15 45 NYK 78 34 46 34 56 File three should look like: CID 12 45 77 19 34 any help would be highly appreciated. Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
reading txt file and copying the lines in new excel file
Shrestha,
Open your text file in Excel, then run this macro. Assumes you have a row of headers. HTH, Bernie MS Excel MVP Sub ExportDatabaseToSeparateFiles() 'Export is based on the value in the desired column Dim myCell As Range Dim mySht As Worksheet Dim myName As String Dim myArea As Range Dim myShtName As String Dim KeyCol As Integer myShtName = ActiveSheet.Name KeyCol = 1 Set myArea = ActiveCell.CurrentRegion.Columns(KeyCol).Offset(1, 0).Cells Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1) For Each myCell In myArea On Error GoTo NoSheet myName = Worksheets(myCell.Value).Name GoTo SheetExists: NoSheet: Set mySht = Worksheets.Add(Befo=Worksheets(1)) mySht.Name = myCell.Value With myCell.CurrentRegion .AutoFilter Field:=KeyCol, Criteria1:=myCell.Value .SpecialCells(xlCellTypeVisible).Copy _ mySht.Range("A1") mySht.Cells.EntireColumn.AutoFit .AutoFilter End With Resume SheetExists: Next myCell For Each mySht In ActiveWorkbook.Worksheets If mySht.Name = myShtName Then Exit Sub Else mySht.Move ActiveWorkbook.SaveAs "Workbook " & ActiveSheet.Name & ".xls" ActiveWorkbook.Close End If Next mySht End Sub wrote in message oups.com... Hello all, I have a txt file from which I have to make separate excel files depending upon the code in the starting of the line. For example: if the txt file looks like: DFW 10 15 14 67 78 DFW 12 18 00 19 68 DFW 11 33 66 56 79 DFW 12 01 55 34 24 NYK 98 00 34 NYK 58 78 00 15 45 NYK 78 34 46 34 56 CID 12 45 77 19 34 Then, I have to make 3 separate excel files as: File one should look like: DFW 10 15 14 67 78 DFW 12 18 00 19 68 DFW 11 33 66 56 79 DFW 12 01 55 34 24 File two should look like: NYK 98 00 34 NYK 58 78 00 15 45 NYK 78 34 46 34 56 File three should look like: CID 12 45 77 19 34 any help would be highly appreciated. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying certain lines from a text file | Excel Discussion (Misc queries) | |||
reading from another file and pasting to current file, "combobox" | New Users to Excel | |||
reading data from 2nd file in 1st file using Combobox | Excel Programming | |||
reading from text file to excel file | Excel Programming | |||
how do you skip lines when reading a text file | Excel Programming |