reading txt file and copying the lines in new excel file

[shresthaprateek]

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!

 

[Ron de Bruin]

Hi

Open the txt file in Excel and use this to create seperate sheets
http://www.rondebruin.nl/copy5.htm

 

[Bernie Deitrick]

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