PDA

View Full Version : How to read a notepad.txt file contents into an excel sheet using VBA



shruthi.s
06-13-2017, 04:35 AM
hi friends,
my requirenment is that i have and test file

abcde
xyz
hello world...!

i need the contents to be read in a excel sheet on the click of a button . i am using developer and VBA



abcde










xyz










hello world...!












please help

YasserKhalil
06-13-2017, 05:00 AM
Try this code


Sub ReadTextFileIntoExcel()
Dim fileNum As Integer
Dim strLine As String
Dim i As Long


fileNum = FreeFile()
Open ThisWorkbook.Path & "\TextFile.txt" For Input As #fileNum


While Not EOF(fileNum)
Line Input #fileNum, strLine
If strLine = "" Then GoTo Skipper
i = i + 1
Cells(i, 1).Value = strLine
Skipper:
Wend
End Sub

shruthi.s
06-13-2017, 05:30 AM
thank you.. for a fast response
i am trying to read a XML file .
file is getting read into excel but in a single cell
can u plzz help me out to read each line into a row in excel

shruthi.s
06-13-2017, 05:32 AM
i = i + 1
targetSheet.Range("A" & i).Value = strLine

YasserKhalil
06-13-2017, 05:41 AM
try to change the extension in the line from txt to xml

Open ThisWorkbook.Path & "\TextFile.txt" For Input As #fileNum

Jan Karel Pieterse
06-13-2017, 07:39 AM
XML files can be opened directy into Excel, but what the end result looks like depends heavily on the structure of the XML file. See: http://www.jkp-ads.com/articles/xmlandexcel00.asp