Macro to Import Data from notepad to Excel

If you want to import data from notepad file to Excel  where no of rows of data in notepad exceeds the no of rows in Excel file .  Try this macro-



Sub import_to_notepad()

Dim FileName As String
Dim i As Long

FileName = "C:\Documents and Settings\user\Desktop\sample_file.txt"

i = 1

Sheets.Add After:=Sheets(Sheets.Count)
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(FileName, 1)

Do Until objFile.AtEndOfStream
    strLine = objFile.ReadLine
    If strLine <> "" Then
    Cells(i, 1) = strLine
    i = i + 1
    If i = 65356 Then ' change the last row you want to choose here
        Sheets.Add After:=Sheets(Sheets.Count)
        i = 1
    End If
    End If
Loop


objFile.Close
Set objFSO = Nothing


End Sub

Download Working File

Steps to Use

Copy the below code
Press Alt+F11 to open VBA editor
Paste it in any public module or module 1
Change the path of notepad file and Run the macro.