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
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.
This comment has been removed by a blog administrator.
ReplyDeleteHi.
ReplyDeleteI want to find XYZ Text in text file
If this is present in text file then copy entire line
and past it to my excel sheet.
Is this possible?
you can make few changes in the code and make it work according to as per your requirement
ReplyDeletelike using find function at"strLine"
Hi,
Deletecan i use the same macro to extract the data from Outlook mail.
Thanks,
KRRA
Hi,
ReplyDeleteCan i use the same macro to extract the data from Outlook.
My basic requirement is.. in email i have some table, every time i copy them in and paste manually in excel sheet. instead of that can i use this macro so that it can reduce my work.
Thanks,
KRRA