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

[asifakhtar]

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

[mikerickson]

asifakhtar,
Welcome to VBA Express.

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

[Sean.DiSanti]

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]

[macropod]

Hi asifakhtar,

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