Home > IT Answers > Database > Find and replace text in text file using MS Access VBA
Vgnadeau
15 pts.
 Find and replace text in text file using MS Access VBA
Access 2000, Access VBA, VBA editor, Visual Basic for Applications, XML File Creation
Hi I need to modify some tags of an XML file that I receive. How can I search the file for specific text and replace it? Here is the code I have right now. It runs without errors but does not replace the text.

Function ReplaceTextInFile() Dim sSearchText As String Dim sReplaceText As String Dim sFileName As String Dim MyString sSearchText = "cb:TRINGLE" sReplaceText = "cb:MATERIAL" sFileName = "C:XMLtestexport.xml" Open sFileName For Input As #1 Do While Not EOF(1)    Input #1, MyString    If InStr(1, MyString, sSearchText, vbTextCompare) Then       MyString = Replace(MyString, sSearchText, sReplaceText, 1, , vbTextCompare)    End If Loop Close #1  End Function


Please help me!

Software/Hardware used:
Microsoft Access VBA
ASKED: July 6, 2010  11:46 PM
UPDATED: May 8, 2013  3:43 PM
RELATED QUESTIONS
Answer Wiki:
The text is being replaced in your MyString memory variable, but you never write the modified string to the file. You could do it creating a new file (opening it for writing) and writing each line of text you read from your original file after the needed replacement; or you could read the complete file, store the modified content in memory, close the file, open it again for writing, and write the modified text to it. Take a look at this similar question/answer: Edit a text file with VBA
Last Wiki Answer Submitted:  May 8, 2013  3:44 pm  by  Michael Tidmarsh   11,390 pts.
All Answer Wiki Contributors:  Michael Tidmarsh   11,390 pts. , carlosdl   63,535 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

Okay. I am making progress. 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 beginning of the file missing!

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

Function FindAndReplaceXMLText()

Dim sSearchText As String

Dim sReplaceText As String

Dim sFileName As String

Dim sFileText As String

Dim strText As String

Dim strNewText As String

Dim intWhere1 As Integer

Dim FirstPos, NextPos

Const ForReading = 1

Const ForWriting = 2

sSearchText = “cb:TRINGLE”

sReplaceText = “cb:MATERIAL”

sFileName = “C:XMLtestexport.xml”

‘Create instance of FileSystemObject.

Set objFSO = CreateObject(“Scripting.FileSystemObject”)

Set objFile = objFSO.OpenTextFile(sFileName, ForReading)

‘read entire contents of file, save to strText variable

strText = objFile.ReadAll

objFile.Close

‘Search for text in string.

strNewText = Replace(strText, sSearchText, sReplaceText, , 1, vbTextCompare)

Set objFile = objFSO.OpenTextFile(sFileName, ForWriting)

objFile.WriteLine strNewText

objFile.Close

‘Create instance of FileSystemObject.

Set objFSO = CreateObject(“Scripting.FileSystemObject”)

Set objFile = objFSO.OpenTextFile(sFileName, ForReading)

‘read entire contents of file, save to strText variable

strText = objFile.ReadAll

objFile.Close

FirstPos = InStr(1, strText, sSearchText, 1)

NextPos = FirstPos + 10

FirstPos = InStr(NextPos, strText, sSearchText, 1)

NextPos = FirstPos + 10

strNewText = Replace(strText, sSearchText, sReplaceText, NextPos, 1, vbTextCompare)

Set objFile = objFSO.OpenTextFile(sFileName, ForWriting)

objFile.WriteLine strNewText

objFile.Close

End Function

Vgnadeau
 15 pts.
 

That is the expected behavior when using the REPLACE function (take a look at the documentation, scroll down to the “Remarks” section).

You will need to add the first part manually. Something like this:

strNewText = Left(strText,NextPos-1)+Replace(strText, sSearchText, sReplaceText, NextPos, 1, vbTextCompare)

But I would make some other changes:

This would be the complete function:

Function FindAndReplaceXMLText()
Dim sSearchText As String
Dim sReplaceText As String
Dim sFileName As String
Dim sFileText As String
Dim strText As String
Dim strNewText As String
Dim intWhere1 As Integer
Dim FirstPos
Const ForReading = 1
Const ForWriting = 2
sSearchText = “cb:TRINGLE”
sReplaceText = “cb:MATERIAL”
sFileName = “C:\XMLtestexport.xml”
‘Create instance of FileSystemObject.
Set objFSO = CreateObject(”Scripting.FileSystemObject”)
Set objFile = objFSO.OpenTextFile(sFileName, ForReading)
‘read entire contents of file, save to strText variable
strText = objFile.ReadAll
objFile.Close
‘Search for text in string.
strNewText = Replace(strText, sSearchText, sReplaceText,1 , 1, vbTextCompare)
FirstPos = 0
for i=1 to 3
FirstPos = InStr(FirstPos+1, strNewText, sSearchText, 1)
next
strNewText = left(strNewText,FirstPos-1)+Replace(strNewText , sSearchText, sReplaceText,FirstPos , 1, vbTextCompare)
Set objFile = objFSO.OpenTextFile(sFileName, ForWriting)
objFile.WriteLine strNewText
objFile.Close
End Function
carlosdl
 63,535 pts.