Pages

Saturday, June 26, 2010

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



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.

6 comments:

  1. This comment has been removed by a blog administrator.

    ReplyDelete
  2. Hi.

    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?

    ReplyDelete
  3. you can make few changes in the code and make it work according to as per your requirement

    like using find function at"strLine"

    ReplyDelete
    Replies
    1. Hi,

      can i use the same macro to extract the data from Outlook mail.

      Thanks,
      KRRA

      Delete
  4. Hi,

    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.

    Thanks,
    KRRA

    ReplyDelete