Copy Data from Notepad to ExcelScenario:-

How we can copy data from Notepad(Text File) into Excel.

Solution:-
We used very simple approach, firstly we’ll copy all the data from Notepad and than paste that to newly created excel.

Pre-requisites:- 
Create Notepad file and add data.(Change the path to your file in below code)
Excel used for adding data should not exists (Name conflict)

Code:-

Dim sData,sExcelPath,sNote
‘Path of Existing Notepad
sNote = “C:UsersDhesiDesktopTestNotepad.txt”
‘Path and Name for creating Excel file
sExcelPath = “C:UsersDhesiDesktoptest1.xls”

‘Calling function for Reading Data from Notepad
sData = fnReadData(sNote)

‘Calling function for Writing data into Excel
fnWriteData sData,sExcelPath
‘******************************Function for Reading********************

‘Function for Reading data from Notepad
Public Function fnReadData(sNote)
    ‘NotePad file already exists with data, we need to get all the present data
    Dim objFSO,objPad
    set objFSO =CreateObject(“Scripting.FileSystemObject”)
    ‘Opening and Reading the Data
    Set objPad = objFSO.OpenTextFile(sNote)
    sData = objPad.ReadAll()
    ‘Destroying the objects
    Set objFSO = Nothing
    Set objPad = Nothing
    fnReadData = sData
End Function
‘******************************Function for Writing********************
‘Function for Writing data into newly created Excel
Public Function fnWriteData(sData,sExcelPath)
    ‘Create an Excel file and paste data into this
    Set objExcel = createobject(“Excel.Application”)
    Set objWB = objExcel.Workbooks.Add()
    objExcel.Cells(1,1) = sData
    ‘Saving the workbook 
    objWB.saveAs(sExcelPath)
     ‘Quit the Excel and destroying the Excel object
    objExcel.Quit
    Set objWB = Nothing
    set objExcel=nothing

End Function


More Examples:-
Working with Notepad
Excel object Model

2 Replies to “How to copy text from Notepad and Paste in Excel?”

  1. No, as per our knowledge,that we have any such command like bulk copy in notepad.

    Just an idea:-
    Approach 1:- Better to take your data files in one excel file with different sheets, as we can better control the updates in excel sheet with one Excel object.
    Approach 2:- Keep your data in notepad itself but take the notepad paths and changes to be made in one sheet and create a logic to run it on notepad.

    Not sure whether it helps you , do keep us posted what approach you followed.

    Thanks, uftHelp team..keep learning

  2. We create batch files using notepad (which contains test data for individual scenarios). We have many of these batch files from where we pass test data for our test cases. Suppose the test data change at a later point of time, is there a way to do a bulk update on each of these notepads instead of opening each notepad and making the changes there.

Leave a Reply

Your email address will not be published. Required fields are marked *