Consulting

Results 1 to 4 of 4

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

  1. #1

    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
    Attached Files Attached Files

  2. #2
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,776
    asifakhtar,
    Welcome to VBA Express.

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

  3. #3
    VBAX Regular
    Joined
    Nov 2010
    Location
    Las Vegas Nv
    Posts
    74
    Location
    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]

  4. #4
    VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,433
    Location
    Hi asifakhtar,

    You can simply open the file with Excel, then use Data|Text to Columns > Delimited > Other and set the column delimiter as ':'.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •