By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,567 Members | 1,670 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,567 IT Pros & Developers. It's quick & easy.

How can I find and replace only certain instances of a word in a text file ?

P: 10
Hi. I am receiving an XML file that needs to be 'tweeked' before I can properly import it into to my Access database. I need to replace the first and fourth instance of'cb:TRINGLE'. I am able to replace the first instance of ‘cb:TRINGLE’ in the xml file. My problem now is that I do not want all instances to be replaced. I only want the first and fourth … In the following code you can see that I am able to locate the position of the fourth instance (I think) but I end up with the entire begining of the file missing!

Is there a better way to only replace certain matches with the Replace function?

Expand|Select|Wrap|Line Numbers
  1.  
  2. Function FindAndReplaceXMLText()
  3. Dim sSearchText As String
  4. Dim sReplaceText As String
  5. Dim sFileName As String
  6. Dim sFileText As String
  7. Dim strText As String
  8. Dim strNewText As String
  9. Dim intWhere1 As Integer
  10. Dim FirstPos, NextPos
  11.  
  12. Const ForReading = 1
  13. Const ForWriting = 2
  14.  
  15. sSearchText = “cb:TRINGLE”
  16. sReplaceText = “cb:MATERIAL”
  17.  
  18. sFileName = “C:XMLtestexport.xml”
  19.  
  20. ‘Create instance of FileSystemObject.
  21. Set objFSO = CreateObject(”Scripting.FileSystemObject”)
  22. Set objFile = objFSO.OpenTextFile(sFileName, ForReading)
  23.  
  24. ‘read entire contents of file, save to strText variable
  25. strText = objFile.ReadAll
  26. objFile.Close
  27.  
  28. ‘Search for text in string.
  29. strNewText = Replace(strText, sSearchText, sReplaceText, , 1, vbTextCompare)
  30.  
  31. Set objFile = objFSO.OpenTextFile(sFileName, ForWriting)
  32. objFile.WriteLine strNewText
  33. objFile.Close
  34.  
  35. ‘Create instance of FileSystemObject.
  36. Set objFSO = CreateObject(”Scripting.FileSystemObject”)
  37. Set objFile = objFSO.OpenTextFile(sFileName, ForReading)
  38.  
  39. ‘read entire contents of file, save to strText variable
  40. strText = objFile.ReadAll
  41. objFile.Close
  42. FirstPos = InStr(1, strText, sSearchText, 1)
  43. NextPos = FirstPos + 10
  44. FirstPos = InStr(NextPos, strText, sSearchText, 1)
  45. NextPos = FirstPos + 10
  46.  
  47. strNewText = Replace(strText, sSearchText, sReplaceText, NextPos, 1, vbTextCompare)
  48.  
  49. Set objFile = objFSO.OpenTextFile(sFileName, ForWriting)
  50.  
  51. objFile.WriteLine strNewText
  52. objFile.Close
  53.  
  54. End Function
  55.  
  56.  
Can someone please help me with this?!
Jul 7 '10 #1
Share this Question
Share on Google+
8 Replies


thelonelyghost
100+
P: 109
Wow! I've edited this post a few times because I keep misunderstanding your post (purely due to stupidity on my part), and it seems like you've got it right for the most part. I just have a couple points to make.

For the sake of cleaning things up, you declared (but never used) the variables sFileText and intWhere1. If they're used elsewhere, obviously keep them. Otherwise they seem to serve no purpose but to take up space.

Second, look at the documentation for the Replace function. It has the ability to start the search/replace after a certain position. It looks like this could be remedied by a For-Each- and While-loop.

Expand|Select|Wrap|Line Numbers
  1. 'User Settings: replaces instances 1 and 4
  2. Dim inst As Integer
  3.   inst = Array(1,4)
  4.  
  5. 'Make a for-each loop to skip until the given instance
  6. For Each i In inst
  7.   NextPos = 1
  8.  
  9.   Do While i > 0
  10.     FirstPos = InStr(NextPos, strText, sSearchText, vbTextCompare)
  11.     NextPos = FirstPos + Len(sSearchText)
  12.     i = i - 1
  13.   Loop
  14.  
  15.   strText = Replace(strText, sSearchText, sReplaceText, NextPos, 1, vbTextCompare)
  16.  
  17. Next i
I haven't tested this, but it's a simple concept of a for-each-loop within the array. Within the for-each is a while-loop that essentially tells it to skip each instance until it reaches the instance specified by the array. Currently it should be set to replace the first instance and 4th instance.

NOTE: I haven't tested this since I don't have an environment set up similar to yours yet. I forsee a possible issue with old-instance 4 becoming new-instance 3 after replacing old-instance 1.

Let me know if it works or, if not, what errors you see.
Jul 7 '10 #2

P: 10
Here's what I have for code now;

Expand|Select|Wrap|Line Numbers
  1.  
  2. Function FindAndReplaceXMLText()
  3.  
  4. Dim sSearchText As String
  5.  
  6. Dim sReplaceText As String
  7.  
  8. Dim sFileName As String
  9.  
  10. Dim strText As String
  11.  
  12. Dim FirstPos, NextPos
  13.  
  14. Dim inst As Integer
  15.  
  16. Dim i As Integer
  17.  
  18.  
  19.  
  20. Const ForReading = 1
  21.  
  22. Const ForWriting = 2
  23.  
  24.  
  25.  
  26. sSearchText = "cb:TRINGLE"
  27.  
  28. sReplaceText = "cb:MATERIAL"
  29.  
  30.  
  31.  
  32. sFileName = "C:\XMLtest\export.xml"
  33.  
  34. 'Create instance of FileSystemObject.
  35.  
  36. Set objFSO = CreateObject("Scripting.FileSystemObject")
  37.  
  38. 'Set objFile = objFSO.OpenTextFile(sFileName, ForReading)
  39.  
  40. 'read entire contents of file, save to strText variable
  41.  
  42. strText = objFile.ReadAll
  43.  
  44. objFile.Close
  45.  
  46.  
  47.  
  48.  
  49.  
  50. 'User Settings: replaces instances 1 and 4
  51.  
  52. inst = Array(1, 4)
  53.  
  54.  
  55.  
  56. 'Make a for-each loop to skip until the given instance
  57.  
  58. For Each i In inst
  59.  
  60.     NextPos = 1
  61.  
  62.  
  63.  
  64.     Do While i > 0
  65.  
  66.       FirstPos = InStr(NextPos, strText, sSearchText, vbTextCompare)
  67.  
  68.        NextPos = FirstPos + Len(sSearchText)
  69.  
  70.        i = i - 1
  71.  
  72.     Loop
  73.  
  74.  
  75.  
  76.     strText = Replace(strText, sSearchText, sReplaceText, NextPos, 1, vbTextCompare)
  77.  
  78. Next i
  79.  
  80. Set objFile = objFSO.OpenTextFile(sFileName, ForWriting)
  81.  
  82. objFile.WriteLine strText
  83.  
  84. objFile.Close
  85.  
  86.  
  87.  
  88. End Function
  89.  
  90.  
  91.  
I get a compile error on 'For Each i In inst', "For Each may only iterate over a collection object or array"
Jul 7 '10 #3

thelonelyghost
100+
P: 109
I was afraid that would happen. I realize looking back that I forgot to declare the inst variable. Simple "Dim inst(2)"

If that doesn't solve the issue, replace the line "inst = Array(1,4)" with the following:
Expand|Select|Wrap|Line Numbers
  1. inst(1) = 1
  2. inst(2) = 4
Anything?
Jul 7 '10 #4

ADezii
Expert 5K+
P: 8,701
@vgnadeau
Just subscribing, will return later.
Jul 8 '10 #5

P: 10
Now I get a compile error "Expected Array" on inst(1) = 1

Ignore this message. I was able to get rid of the compile error.
Jul 8 '10 #6

P: 10
Ok. Here's my code now.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Function FindAndReplaceXMLText()
  3. Dim sSearchText As String
  4. Dim sReplaceText As String
  5. Dim sFileName As String
  6. Dim strText As String
  7. Dim FirstPos, NextPos
  8. Dim intLength As Integer
  9. Dim i As Variant
  10. Dim inst(2) As Integer
  11.  
  12. Const ForReading = 1
  13. Const ForWriting = 2
  14.  
  15.  
  16. sFileName = "C:\XMLtest\export.xml"
  17. 'Create instance of FileSystemObject.
  18. Set objFSO = CreateObject("Scripting.FileSystemObject")
  19. Set objFile = objFSO.OpenTextFile(sFileName, ForReading)
  20. 'read entire contents of file, save to strText variable
  21. strText = objFile.ReadAll
  22. objFile.Close
  23.  
  24. sSearchText = "cb:TRINGLE"
  25. sReplaceText = "cb:MATERIAL"
  26.  
  27. 'User Settings: replaces instances 1 and 4
  28. inst(1) = 1
  29. inst(2) = 4
  30. 'Make a for-each loop to skip until the given instance
  31. For Each i In inst
  32.     NextPos = 1
  33.  
  34.     Do While i > 0 And i < 3
  35.         FirstPos = InStr(NextPos, strText, sSearchText, vbTextCompare)
  36.         NextPos = FirstPos + Len(sSearchText)
  37.         i = i + 1
  38.  
  39.     Loop
  40.     If i < 4 Then
  41.         strText = Replace(strText, sSearchText, sReplaceText, NextPos, 1, vbTextCompare)
  42.     End If
  43. Next i
  44. Set objFile = objFSO.OpenTextFile(sFileName, ForWriting)
  45. objFile.WriteLine strText
  46.  
  47. objFile.Close
  48.  
  49. End Function
  50.  
  51.  
It does replace the fourth instance of cb:TRINGLE but removes all text before ... So I end up with this

>
</cb:MATERIAL>
The data after is still there, which is good.

Could you look at this again please? I'm getting desparate!
Jul 8 '10 #7

ADezii
Expert 5K+
P: 8,701
@vgnadeau
I had a heck of a time incorporating the Replace() Function into selective Find/Replace operations, and due to time constraints threw in the towel. I took a unique approach, using a Text File, and surgically found and replaced the 1st and 4th occurrences of sSearchString with sReplaceString via the hard way. I'll post the partial code below, but simply download the Attachment and copy both Files to the same Directory. Open the Database (Replace_Good.mdb):
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdTest_Click()
  2. On Error GoTo Err_cmdTest_Click
  3. Dim objFSO As FileSystemObject
  4. Dim ts As TextStream
  5. Dim sSearchText As String
  6. Dim sReplaceText As String
  7. Dim sFileName As String
  8. Dim lngPosition As Long
  9. Dim intNumOfMatches As Integer
  10. Dim strText As String
  11. Dim strNew As String
  12. Dim lngNewPos As Long
  13. Dim strMsg As String
  14.  
  15. sSearchText = "cb: TRINGLE "
  16. sReplaceText = "cb: MATERIAL"
  17.  
  18. sFileName = CurrentProject.Path & "\Test.txt"
  19.  
  20. strMsg = "   Open the Debug (Immediate Window) via CTRL+G, and you'll see that only " & _
  21.          "the first and fourth occurrences of" & vbCrLf & "[cb: TRINGLE] have been replaced with [cb: MATERIAL]." & _
  22.          vbCrLf & vbCrLf & "P.S. - Nevermind, I'll Open it for you (LOL)."
  23.  
  24. 'Create instance of FileSystemObject.
  25. Set objFSO = CreateObject("Scripting.FileSystemObject")
  26. Set ts = objFSO.OpenTextFile(sFileName, ForReading)
  27.  
  28. 'Read entire contents of file, save to strText variable
  29. strText = ts.ReadAll
  30.  
  31. '1st position of sSearchText within strText
  32. lngPosition = InStr(strText, sSearchText)
  33. If lngPosition = 0 Then Exit Sub
  34.  
  35. 'Surgically remove sSearchText, replace with sReplaceText, and
  36. 'Rebuild the String
  37. strNew = Left$(strText, lngPosition - 1) & sReplaceText & " " & _
  38.                Mid$(strText, lngPosition + Len(sSearchText))
  39.  
  40. 'Increment the Number of Matches and increment the new Starting
  41. 'Position for the subsequent Find Operation
  42. intNumOfMatches = intNumOfMatches + 1
  43. lngNewPos = lngPosition + 1
  44.  
  45. 'Look for additional Matches, and continue Looping until either the number
  46. 'of Mathces = 4 or until sSearchText is no longer found. At the same time
  47. 'change only the 1st and 4th occurrences of sSearchtetxt to sReplaceText
  48. Do
  49.   If InStr(lngNewPos, strText, sSearchText) <> 0 Then
  50.     lngPosition = InStr(lngNewPos, strText, sSearchText)
  51.     intNumOfMatches = intNumOfMatches + 1
  52.       lngNewPos = lngPosition + 1
  53.         If intNumOfMatches = 4 Then     '4th occurrence
  54.           strNew = Left$(strNew, lngPosition - 1) & sReplaceText & " " & _
  55.                    Mid$(strNew, lngPosition + Len(sSearchText))
  56.         End If
  57.   End If
  58. Loop Until intNumOfMatches = 4 Or lngPosition = 0
  59.  
  60. 'Write strNew, not strText, to the New File
  61. Debug.Print strNew
  62.  
  63. MsgBox strMsg, vbInformation, "Selective Replace"
  64. DoCmd.RunCommand acCmdDebugWindow
  65.  
  66. Exit_cmdTest_Click:
  67.     Exit Sub
  68.  
  69. Err_cmdTest_Click:
  70.     MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
  71.     Resume Exit_cmdTest_Click
  72. End Sub
Attached Files
File Type: zip Replace_Good.zip (21.6 KB, 77 views)
Jul 8 '10 #8

P: 10
Thank you ... you are my hero ;)
Jul 8 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.