copy data from txt file to excel sheet

[vinod9111]

Hi All,

I have one huge text file which i need to copy into excel sheets. The data is huge and may run upto 6 to 7 sheets. I need a macro to copy the data from the text file and paste into the excel sheet till 60000 row and then the remaining data need to be pasted in other sheets till 60000 row of each sheet.

Any help will be appreciated.

regards,

Vinod

 

[VoG]

See if you can adapt this - it imports 4 columns of data.

Sub ImportPostCodes()
Dim iFreeFile As Integer, i As Long, path As String, InputLine
Dim ws As Worksheet, LineArray, t As Date
t = Now
Set ws = ActiveSheet
Reset
iFreeFile = FreeFile
path = ThisWorkbook.path
Open path & "\" & "PostCodeData.txt" For Input As #iFreeFile
Application.ScreenUpdating = False
i = 1
Do While Not EOF(iFreeFile)
    Line Input #iFreeFile, InputLine
    LineArray = Split(InputLine, vbTab)
    ws.Range(Cells(i, 1), Cells(i, 4)).Value = LineArray
    i = i + 1
    If i > 65536 Then
        ThisWorkbook.Save
        Sheets.Add after:=ws
        Set ws = ActiveSheet
        i = 1
    End If
Loop
ThisWorkbook.Save
Close #iFreeFile
Application.ScreenUpdating = True
MsgBox "Time elapsed" & vbTab & Format(Now - t, "hh:mm:ss")
End Sub