deleting cetain lines on a file
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: deleting cetain lines on a file

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    So Cal
    Posts
    379
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hello ALL,
    I have a need for a macro that will open up all text files (with extension .csv) in a directory, find lines that have a cell entry of **** Header Line ****
    and then delete those lines, close and save the files. There is only one column containing the string, col "J". The number of lines with a cell containing this string will vary as will the number of files.
    There are 52 diff columns on each file and each are in the same format.

    Any assistance would be gratefully recieved!

    Thanks and have a great day....

    Dan

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hope this does it for you... will search all files as prescribed and strip any whole lines that contain the search text anywhere in that line...




    Sub strip_files()

    search_dir = "c:"
    file_type = ".csv"
    find_string = "*** Header Line ***"

    filesfound = 0

    Dim filefound As String
    filefound = Dir(search_dir & "*" & file_type)
    If filefound = "" Then GoTo end_files
    nextfile:
    filesfound = filesfound + 1
    file1 = search_dir & filefound
    file2 = Left(file1, Len(file1) - (Len(file_type) - 1)) & "tmpx"
    If Dir(file2) <> "" Then Kill file2
    Open file1 For Input As #1
    Open file2 For Output As #2
    Do Until EOF(1)
    Line Input #1, strg
    If InStr(1, strg, find_string) = 0 Then Print #2, strg
    Loop
    Close #1
    Close #2
    Kill file1

    filefound = Dir(search_dir & "*" & file_type)
    If filefound <> "" Then GoTo nextfile

    end_files:
    filefound = Dir(search_dir & "*.tmpx")
    Do Until filefound = ""
    file1 = search_dir & filefound
    file2 = Left(file1, Len(file1) - 5) & file_type
    Name file1 As file2
    filefound = Dir(search_dir & "*.tmpx")
    Loop

    MsgBox filesfound & " files processed."
    End Sub





  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ps: configure the first few lines, the first of which should read

    search_dir = "c:"

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    darn, it did it again, what is wrong with this thing! ha ha

    should only be on forward slash in the directory name specified.

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    there is definately something wrong with this site, mail me if you want the code as i submitted it (if it doesn't work from copy & pasting) daley_123@hotmail.com

  6. #6
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-29 17:09, daleyman wrote:
    there is definately something wrong with this site, mail me if you want the code as i submitted it (if it doesn't work from copy & pasting) daley_123@hotmail.com
    Yes there is the [] double up...known prob
    This is getting looked into.....please bere
    with us until it gets a fix....


    Regards

    Ivan

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    no problem, thanks.

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    So Cal
    Posts
    379
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    wow thanks for all the interest. i am sorry it took so long for my reply but i was out of town. i will, now that i am back at work uugh!, give this a go. do appreciate the input. you don't know how much work you have saved more than one person. have a good day.

    dan

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    So Cal
    Posts
    379
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    success my friend...


    Dan

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com