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_B ookmarks.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.

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

One way is to use a query table.

Select A8
Data > Import External Data > Import Data
Browse to the text file > Open
Delimited > Next
Untick Tab
Tick Other and type in :
Finish

Going forward, if the text file's contents change you can simply right click on one of the cells in the query table > refresh


Another option is just to open the text file into a new workbook:
File > Open
Change the filter to all files
Browse to the text file > Open
Follow the Text to Columns instructions as above
Copy and paste the data into your template

I have been told not to use import. I have been asked to achieve this by VBA code.

Thanks

If you go for option one, then the query table is set so all you have to do is refresh the query table. This can be set in the options to happen when the workbook is opened. Much better than VBA, right?

If you go for option two, then you can automate the process using VBA. To get started use the macro recorder.

VBA option 3 would be to use file I/O. But the only reason I can imagine that you would be forced to use this would be if it is a homework assignment.

It is not an assignment. I work as a programmer but Excel is not my specialty. Importing data is a lot of work for our managers and they are not good in Excel as well that is why I am asked to acheive this with a click of a button.

Thanks

Option (1) achieves it without the need to click anything. It will automatically refresh the data in the workbook when it is opened. The only work is setting up the query table in the first place, which I have described how to do.