How can I read a text file and write values into an excel sheet

Hi,
How can I read a text file and write values into an excel sheet.



I have a text file which I create when I run “JavaScript Code” to extract pdf bookmarks. The location of the text file is “C:\Users\makhtar\Desktop\WOOF_Stacey_CAT_master_Bookmarks.txt”


I have an excel template “Bookmark Excel Template” at “C:\Users\makhtar\Desktop\Bookmark Excel Template.xls”


How can I read every line of the text file and write values in the excel column.


Each text file row consists of 3 values. Date: Description: Author
Please note the values are separated by “:”


The writing should start from A8 in Excel which should be 1 and then the 3 values in the sheet. Next row should be 2 and then the 3 values and so on.


Please see attached Sample Excel file.


I am pasting text file data as I am only allowed to upload 1 file:


TABLE of CONTENTS
2010-10-19: Application for Determination of Catastrophic Impairment (OCF-19): Dr. Lisa Becker (MD), Omega Medical Associates
2010-10-19: OCF-19 Report: Dr. Lisa Becker (MD), Omega Medical Associates
2009-07-18: Ambulance Call Report: Ontario Ministry of Health & Long-Term Care
2009-07-18: Ambulance Call Report: Ontario Ministry of Health & Long-Term Care
2009-07-18: Triage/ Emergency Nursing Record: Stratford General Hospital
1992-03-03: Emergency Department Flowsheet: St. Joseph's Health Centre
2008-08-01: Inpatient/ One Day Stay Face Sheet: St. Joseph's Health Centre
2008-08-03: Medication Administration Record: St. Joseph's Health Centre
2009-06-12: Inpatient/ One Day Stay Face Sheet: St. Joseph's Health Centre
2009-07-18: CT Cervical Spine: Stratford General Hospital
2009-07-18: CT Head: Huron Perth Healthcare Alliance
2009-07-18: Consultation Note: Dr. T. Jimenz (MD), Stratford General Hospita
2009-07-18: RAD Chest: Stratford General Hospital
2009-07-18: RAD Hand/ Wrist, Right Forearm: Stratford General Hospital
2009-07-18: Specimen Inquiry: HPHA & AMGH Hospitals
2009-07-18: Stat Broadcast: HPHA & AMGH Hospitals
2009-07-18: Stat Broadcast: HPHA & AMGH Hospitals
2009-07-18: Stat Broadcast: HPHA & AMGH Hospitals
2009-07-18: Stat Broadcast: HPHA & AMGH Hospitals
2009-07-18: Stat Broadcast: HPHA & AMGH Hospitals





Thanks

asifakhtar,
Welcome to VBA Express.

I moved your thread to the general Excel forum, where you can find more answers.

Here's an example of general line by line input from the helpfile, i'm sure you can tailor it to your needs
[vba]
Dim TextLine
Open "TESTFILE" For Input As #1 ' Open file.
Do While Not EOF(1) ' Loop until end of file.
Line Input #1, TextLine ' Read line into variable.
Debug.Print TextLine ' Print to the Immediate window.
Loop
Close #1 ' Close file.
[/vba]

Hi asifakhtar,

You can simply open the file with Excel, then use Data|Text to Columns > Delimited > Other and set the column delimiter as ':'.