Results 1 to 7 of 7

Thread: Extracting Specific Line Of Data From Text File Using VBA

  1. #1
    Junior Member
    Join Date
    Apr 2013
    Posts
    12
    Rep Power
    0

    Extracting Specific Line Of Data From Text File Using VBA

    Hi,
    Please assist in creating vba code that will automate current task as follows,
    - manually open each file to check if complete with volume numbering 200+ accounts.
    - It was made by checking value of the ending balance - normal - ist file as againts ending
    balance of the next, if values are equal it means files complete,if not incomplete.
    Sample content of file
    file 1
    ,,,,,"BEGINNING BALANCE-NORMAL","C","","11602603.58"
    ,,,,,"ENDING BALANCE-NORMAL","C","","9301134.07"
    File 2
    ,,,,,"BEGINNING BALANCE-NORMAL","C","","9301134.07"
    ,,,,,"ENDING BALANCE-NORMAL","C","","4907521.54"

    File format is text and comma deliminated
    Data to look for is located on top- 1st and 2nd row

    I got several codes from other forums and tried to edit them to fit my requirements but to no avail.
    Because at this point I, cannot create original (made by me) code successfully as my knowlede in vba is limited, My macro programs are just copied from forums here and somewhere else.

    Thank you in advance

    Ofel
    Last edited by galang_ofel; 06-01-2013 at 04:00 PM.

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    And the name of the text files are all in sequence?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    Apr 2013
    Posts
    12
    Rep Power
    0
    Hi,
    Yes name of the text files are all in sequence.

    Thanks a lot
    ofel

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    And how many lines of text will these text files have? And are you looking for the very first line and the last line? Or can those be anywhere? Can you post two sample text files?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Junior Member
    Join Date
    Apr 2013
    Posts
    12
    Rep Power
    0
    Hi,
    My apology for the late reply, as requested please find sample file attached

    Thanks

    Ofel
    Attached Files Attached Files

  6. #6
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    13
    This should get you started.
    Gives Name, BeginBalance and EndingBalance for each file in directory.
    Code:
    Sub ReadTextFiles()
         'Set Reference to Microsoft Scripting Runtime (Tools > References)
        Const TextDir = "G:\My documents\TextFiles\" 'adjust accordingly
        Dim FSO As FileSystemObject, FSOStream As TextStream
        NumFiles = Format(CreateObject("scripting.filesystemobject") _
                        .GetFolder(TextDir).Files.Count, "0000")
        ReDim sn(1 To NumFiles, 1 To 3)
        Set FSO = New FileSystemObject
        fn = Dir(TextDir & "*.txt")
        i = 1
        Do While fn <> ""
            Set FSOStream = FSO.GetFile(TextDir & fn) _
                        .OpenAsTextStream(ForReading, TristateUseDefault)
            sn(i, 1) = fn
            sn(i, 2) = Split(FSOStream.ReadLine, ",")(8)
            sn(i, 3) = Split(FSOStream.ReadLine, ",")(8)
            fn = Dir()
            i = i + 1
         Loop
         With Sheets(1).Cells(1) 'puts data in 1st sheet, change to need
            .CurrentRegion.ClearContents
            .Resize(UBound(sn, 1), UBound(sn, 2)) = sn
        End With
    End Sub



    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 06-11-2023 at 03:34 PM.

  7. #7
    Junior Member
    Join Date
    Apr 2013
    Posts
    12
    Rep Power
    0
    Hi,
    Problem solved, Thanks a lot

Similar Threads

  1. Wrap Text On Spaces Up To A Maximum Number Of Characters Per Line
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 15
    Last Post: 12-20-2016, 09:47 AM
  2. Replies: 3
    Last Post: 06-10-2013, 06:12 PM
  3. Replies: 14
    Last Post: 05-25-2013, 06:55 AM
  4. Extracting Numeric Values From Alphanumeric Text
    By Safal Shrestha in forum Excel Help
    Replies: 3
    Last Post: 03-21-2013, 12:04 PM
  5. Write/Create Text File VBA
    By Admin in forum Download Center
    Replies: 0
    Last Post: 06-20-2011, 01:39 AM

Posting Permissions

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