Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Feb 2015
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Select unique records and save to new text file

    Hi, I am new to the forum and am looking for VBA code to do the following:
    I have written the code to perform formatting, and have stripped down a text file. What I am missing ....I need to extract records that have the code 300 as the first three characters, and then save to a new text file. Thanks in advance for your support.
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    idisc,

    Will something like this work for you?

    Code:
    Public Sub CodePrefix()
    If Left(Range("A1"), 3) = 300 Then
        MsgBox "Code begins with 300"
    Else: MsgBox "Code does not begin with 300"
    End If
    End Sub
    HTH,
    Maud

  3. #3
    New Lounger
    Join Date
    Feb 2015
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Maud, The text file contains over 22,000 lines. What I am looking for is a Sub that reads each line and copies those lines containing the code 300 to a new text file. Thanks.

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 986 Times in 916 Posts
    That's a task for VBS rather than Excel / VBA. Does it have to be VBA?

    cheers, Paul

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by Paul T View Post
    That's a task for VBS rather than Excel / VBA. Does it have to be VBA?

    cheers, Paul
    Or maybe some PowerShell.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. The Following User Says Thank You to RetiredGeek For This Useful Post:

    idisc (2015-11-15)

  7. #6
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 986 Times in 916 Posts
    You have really taken to PS!

    cheers, Paul

  8. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by Paul T View Post
    You have really taken to PS!

    cheers, Paul
    Gee Paul, is it that Obvious?
    ROTFLOL.gif
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    idisc,

    Sorry, though you were just stuck on how to identify a code string starting with 300. Tour text file (btw, has no extension) has multiple numeric columns. Which column(s) are the search criteria for the code numbers?

  10. #9
    New Lounger
    Join Date
    Feb 2015
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Maud and all others, Thanks for your responses. The code number is the first three characters on each line. Hope you can come up with something.

  11. #10
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 986 Times in 916 Posts
    Here's a quick n dirty PowerShell script to do it. You could do it all on one line to save setting up script execution.
    To find the code at the start of the line we use a regular expression starting with caret ('^'+$CodeToFind).

    cheers, Paul

    Code:
    $CodeToFind  = "123"
    $Source      = "G:\BEKDocs\InFile.txt"
    $Destination = "G:\BEKDocs\Transfer\OutFile.txt"
    
    @(Get-Content $Source).where{$_ -match '^'+$CodeToFind} | Out-File -FilePath $Destination -append
    Last edited by Paul T; 2015-11-14 at 03:27.

  12. The Following User Says Thank You to Paul T For This Useful Post:

    idisc (2015-11-15)

  13. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    idisc,

    If you want to stay within Excel then this file will read your text file record by record and check the first three digits for 300. If found, it will write each record with the 300 prefix to a text file called Output.txt.

    You must make a reference to the Microsoft Scripting Runtime library and change the paths of the source and output files (blue).

    HTH,
    Maud

    Code:
    Public Sub GetText()
    'MUST ENABLE MICROSOFT SCRIPTING RUNTIME
    'VB EDITOR > TOOLS > REFERENCES... > MICROSOFT SCRIPTING RUNTIME > OK
    '--------------------------------------------------------
    'DECLARE AND SET VARIABLES
    Dim file As New FileSystemObject
    Dim stream As TextStream
    Set stream = file.CreateTextFile("C:\Users\Maudibe\Desktop\Output.txt", True)
    '--------------------------------------------------------
    'READ SOURCE FILE LINES, CHECK FORST 3 VALUES, WRITE TO OUTPUT FILE
    fn = FreeFile
    Open "C:\Users\Maudibe\Desktop\Egn4Test.txt" For Input As #fn
        While Not EOF(fn)
            Line Input #fn, LineString
            If Left(LineString, 3) = 300 Then
                stream.WriteLine LineString
            End If
        Wend
    '--------------------------------------------------------
    'CLEANUP
        Close #fn
        stream.Close
    End Sub

  14. The Following User Says Thank You to Maudibe For This Useful Post:

    idisc (2015-11-15)

  15. #12
    New Lounger
    Join Date
    Feb 2015
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts
    A great big thanks to all contributors. Since I want to stay within Excel I will go with Maud's solution. Probably the best forum out there.

  16. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    idisc,

    I revised the workbook slightly by adding a button to run the code and pointed to cells C1 and C2 where you can add/change the source and destination paths respectively. If the paths do not exist then a popup message will warn you.

    HTH,
    Maud

    idisc1.png
    Attached Files Attached Files

  17. #14
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 986 Times in 916 Posts
    Now you need a 3rd cell with the code to search for.

    cheers, Paul

  18. #15
    New Lounger
    Join Date
    Feb 2015
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I am pleased to say that I tested Maud's solution, and it worked like a charm. Thanks again to all those that contributed.

Posting Permissions

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