How to copy text from Notepad and Paste in Excel? – User friendly Tech help

Scenario:-n

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

n

Solution:-

n

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

n

Pre-requisites:- 

n

Create Notepad file and add data.(Change the path to your file in below code)

n

Excel used for adding data should not exists (Name conflict)

n

Code:-

n

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

n

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

n

n‘Calling function for Writing data into Excel
nfnWriteData sData,sExcelPath

n

‘******************************Function for Reading********************

n‘Function for Reading data from Notepad
nPublic Function fnReadData(sNote)
n    ‘NotePad file already exists with data, we need to get all the present data
n    Dim objFSO,objPad
n    set objFSO =CreateObject(“Scripting.FileSystemObject”)
n    ‘Opening and Reading the Data
n    Set objPad = objFSO.OpenTextFile(sNote)    sData = objPad.ReadAll()n

    ‘Destroying the objects

n
n    Set objFSO = Nothing
n    Set objPad = Nothing    fnReadData = sDatan

End Function

n‘******************************Function for Writing********************
n‘Function for Writing data into newly created Excel
nPublic Function fnWriteData(sData,sExcelPath)
n    ‘Create an Excel file and paste data into this
n    Set objExcel = createobject(“Excel.Application”)
n    Set objWB = objExcel.Workbooks.Add()
n    objExcel.Cells(1,1) = sData
n    ‘Saving the workbook     objWB.saveAs(sExcelPath)n

     ‘Quit the Excel and destroying the Excel object

n    objExcel.Quitn

    Set objWB = Nothing

n
n    set objExcel=nothingn

End Function

n
n
n
nMore Examples:-
nWorking with Notepad
nExcel object Model

Was this article helpful?
YesNo

Similar Posts