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-
Dim FileName As String
Dim i As Long
FileName = "C:\Documents and Settings\user\Desktop\sample_file.txt"
i = 1
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
i = 1
Set objFSO = Nothing
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.ReplyDelete
I 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 requirementReplyDelete
like using find function at"strLine"
can i use the same macro to extract the data from Outlook mail.
Can 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.