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!

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



<[email protected]> wrote in message news:[email protected]...
> 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!
>

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(Before:=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



<[email protected]> wrote in message
news:[email protected]...
> 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!
>