Find and replace text in text file using MS Access VBA

15 pts.
Tags:
Access VBA
Microsoft Access 2000
VBA
VBA editor
XML
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Vgnadeau
    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
    15 pointsBadges:
    report
  • carlosdl
    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
    70,220 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following