Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find-Replace in text file (A2k)

    I've created a form which contains data ie :

    Owner
    Address1
    Address2
    Address3
    PostCode

    etc etc etc

    With the form, I have a command button which needs to do the following:

    Open a text file
    Find and replace data ( As in Owner and address field etc)
    Save as text file in another location.

    I could do with some help on this to get me started.

  2. #2
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find-Replace in text file (A2k)

    The following example is very close.
    The file saves as the txt file but when opening, there are loads of spurious characters.

    <pre> Dim oWord As Object
    Dim oDoc As Object
    Dim strPath As String
    Dim JN As String
    strPath = "C:MM-UtilitiesATX.txt"
    JN = InputBox("Please Enter Your Initials")

    Set oWord = CreateObject("word.application")
    Set oDoc = oWord.documents.Add("C:MM-UtilitiesImport.txt")

    With oWord.Selection.Find
    .Execute FindText:="DAV33343", ReplaceWith:="DJW99999", Replace:=2

    End With

    oDoc.SaveAs "C:mm-Utilities" & JN & Me.EST_NO & "-ATX-Import.txt"
    oDoc.Close False
    Set oDoc = Nothing
    oWord.NormalTemplate.Saved = True
    oWord.Quit
    Set oWord = Nothing

    exitHandler:
    On Error Resume Next
    Resume exitHandler</pre>


  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Find-Replace in text file (A2k)

    Here is some aircode that will do it without using Word.

    It opens the file directly, reads each line into a string, performs a replace on that string then writes that string to another file.


    Dim strInputfile As String
    Dim strouputfile As String
    strinputfile = "c:something.txt"
    stroutputfile = "c:somethingelse.txt"
    Dim strLine as string
    Dim strSearchtext as string
    Dim strreplacetext as string
    strSearchtext = "something"
    strreplacetext = " alternative"
    Open strinputfile For Input As #1 ' Open file for intput.
    Open stroutputfile For Output As #2 ' Open file for output.
    do while not EOF(1)
    Line Input #1, strLine
    strLine = replace(strLine,strsearchtext,strreplacetext)
    Print #2, strLine
    Loop

    Close #1
    Close #2
    Regards
    John



  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find-Replace in text file (A2k)

    John
    Thanks for the reply.
    I'm dealing with multiple items to replace.
    Your code works fine if replacing one item.
    I tried this as an example which doesn't work.
    Can you elaborate the code to compensate and replace multiple items with the loop ?

    <pre>Dim strInputfile As String
    Dim strouputfile As String

    strInputfile = "C:MM-UtilitiesAudaImport.txt"
    stroutputfile = "c:MM-UtilitiesATXelse.txt"

    Dim strLine As String
    Dim strSearchtext1 As String
    Dim strReplacetext1 As String
    Dim strSearchtext2 As String
    Dim strReplacetext2 As String

    strSearchtext1 = "DAV33343"
    strReplacetext1 = "ZZZ11111"
    strSearchtext2 = "Y106NBF"
    strReplacetext2 = "G787EEH"

    Open strInputfile For Input As #1 ' Open file for intput.
    Open stroutputfile For Output As #2 ' Open file for output.
    Do While Not EOF(1)
    Line Input #1, strLine
    strLine = Replace(strLine, strSearchtext, strReplacetext)
    Print #2, strLine
    Loop

    Close #1
    Close #2

    End Sub</pre>


  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find-Replace in text file (A2k)

    Change

    strLine = Replace(strLine, strSearchtext, strReplacetext)

    to

    strLine = Replace(strLine, strSearchtext1, strReplacetext1)
    strLine = Replace(strLine, strSearchtext2, strReplacetext2)

    You can add more such lines if needed.

  6. #6
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find-Replace in text file (A2k)

    Thank you John, the code is exactly what I was after.

    A little concern though.
    I am replacing items in the text with data from a database.

    ie Replace #Registration #(19 Characters) with [Me.Reg](7 Characters) notice the spaces after the string. !
    The fields in the text are field length critical, in this case would the 7 replace the 19 ?

    Or is there a work around ?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find-Replace in text file (A2k)

    Hi Chuck,

    If the lnes need to keep the same length, it is your responsibility as programmer to ensure that the Replacetext]i]n[/i] variable has the same length as the SearchText/ variable. But is [Me.Reg] referring to a value from a form? If so, what will be the length of the value? Not necessarily 7, I suppose.

  8. #8
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find-Replace in text file (A2k)

    Yes Hans

    There's no way of determining the length of the replacement text, where as the search text are fixed width.

    <pre>strSearchtext1 = "#ASSESS#"
    strReplacetext1 = JN & Me.EST_NO
    strSearchtext2 = "#DATE2222#"
    strReplacetext2 = Date
    strSearchtext3 = "#REGIS #"
    strReplacetext3 = Me.REG
    strSearchtext4 = "#CLAIMNUMBER2222222#"
    strReplacetext4 = Me.CLM_NO
    strSearchtext5 = "#SEARCHNAME22222222#"
    strReplacetext5 = Me.OWN_NME
    strSearchtext6 = "#ASSESSOR #"
    strReplacetext6 = "# #"
    strSearchtext7 = "#OWNER #"
    strReplacetext7 = Me.OWN_NME
    strSearchtext8 = "#ADDRESS1 #"
    strReplacetext8 = Me.OWN_ADD_1</pre>


  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find-Replace in text file (A2k)

    Hi Dave,

    You can do something like this:
    <pre>strSearchtext1 = "#ASSESS#"
    strReplacetext1 = Left(JN & Me.EST_NO & Space(100), Len(strSearchtext1))
    strSearchtext2 = "#DATE2222#"
    strReplacetext2 = Left(Date & Space(100), Len(strSearchtext2))
    strSearchtext3 = "#REGIS #"
    strReplacetext3 = Left(Me.REG & Space(100), Len(strSearchtext3))
    strSearchtext4 = "#CLAIMNUMBER2222222#"
    strReplacetext4 = Left(Me.CLM_NO & Space(100), Len(strSearchtext4))
    strSearchtext5 = "#SEARCHNAME22222222#"
    strReplacetext5 = Left(Me.OWN_NME & Space(100), Len(strSearchtext5))
    strSearchtext6 = "#ASSESSOR #"
    strReplacetext6 = "# #"
    strSearchtext7 = "#OWNER #"
    strReplacetext7 = Left(Me.OWN_NME & Space(100), Len(strSearchtext7))
    strSearchtext8 = "#ADDRESS1 #"
    strReplacetext8 = Left(Me.OWN_ADD_1 & Space(100), Len(strSearchtext8))
    </pre>

    This way, the replacement text will be made the same length as the search text.

  10. #10
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find-Replace in text file (A2k)

    Hans

    Do I need to replace the "(100)" with the field length ? or is this accounted for ?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find-Replace in text file (A2k)

    The 100 is just a number larger than the length of any of the search texts. You could use 75 or 200 just as well. The Left function will chop off unneeded spaces: in

    strReplacetext3 = Left(Me.REG & Space(100), Len(strSearchtext3))

    the Me.REG & Space(100) part appends 100 spaces to the right of the value of Me.REG. So for example, if Me.REG is 6 characters long, Me.REG & Space(100) will be 106 characters long.

    Len(strSearchText3) is 9 in your code, so Left(Me.REG & Space(100), Len(strSearchtext3)) will take the first 9 of those 106 characters: the 6 of Me.REG plus 3 spaces. The remaining 97 spaces are discarded.

  12. #12
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find-Replace in text file (A2k)

    Hans

    Tried and tested, this is just absolutely brilliant.
    Hope the demo went ok and have a good weekend !! <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find-Replace in text file (A2k)

    Yes, thanks <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
    Have a good weekend too.

  14. #14
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find-Replace in text file (A2k)

    Hans

    One final thing (There always is !!)
    The Post Code in the text is a 4&3 character string ie ST54 GT6 which are joined together in the replace string.

    <pre>strSearchtext12 = "#PC#"
    strReplacetext12 = Left(Me.OWN_PCD & Space(100), Len(strSearchtext12))
    strSearchtext13 = "#P#"
    strReplacetext13 = Left(Me.OWN_PCD & Space(100), Len(strSearchtext12))</pre>


    #PC# is the left 4 charcters
    #P# is the right 3 characters

    Could you help with the syntax ?

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find-Replace in text file (A2k)

    Try this:

    strSearchtext12 = "#PC# #P#"
    strReplacetext12 = Left(Me.OWN_PCD & Space(100), Len(strSearchtext12))

Page 1 of 2 12 LastLast

Posting Permissions

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