How do remove or replace a TM symbol in an XML File

I have written a vba program in Excel that logs onto a website, navigates to an XML export file, and saves it to a network folder for processing by our AS/400.  The problem is that occasionally a trademark symbol chr(153) is in the file and that causes the file to bomb out on the AS/400 because it does not recognize the symbol.  Any ideas for a simple routine to remove the TM symbol or replace it with a space?  I keep having issues with importing it into a worksheet and changing it there.  It loses the XML format, mostly involving quote signs.
johngr904Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cyberkiwiCommented:
Portion code from http://www.ozgrid.com/forum/showthread.php?t=53975&highlight=manipulate+text+file
Sub ChangeTextFile() 
     ' Manipulating a text file with VBA
     '  Loops through text file and creates revised one
     ' This code requires a  reference (Tools > References) to Microsoft Scripting Runtime
     
    Dim FSO As FileSystemObject 
    Dim FSOFile As TextStream, FSOFileRevised As TextStream 
    Dim FilePath As String, FilePathRevised As String 
     
    FilePath = "c:\test.xml" ' create a test.xml file or change this
     
     ' adds "_Revised" to your file name
    FilePathRevised = Left(FilePath, Len(FilePath) - 4) & "_Revised" & Right(FilePath, 4) 
     
    Set FSO = New FileSystemObject 
    If FSO.FileExists(FilePath) Then 
         ' opens the file for reading
        Set FSOFile = FSO.OpenTextFile(FilePath, 1, False) 
         ' opens "revised" file in write mode
        Set FSOFileRevised = FSO.OpenTextFile(FilePathRevised, 2, True) 
        Do While Not FSOFile.AtEndOfStream 
            FSOFileRevised.WriteLine (Replace(FSOFile.ReadLine, Chr(153), "TM"))
        Loop 
        FSOFile.Close 
        FSOFileRevised.Close 
    Else 
         MsgBox (FilePath & " does not exist") 
    End If 
End Sub 

Open in new window

0
cyberkiwiCommented:
This should work without having to add the referenec
Dim iSource As Integer, iDest As Integer, sText As String
''Close any open text files
Close
''Get the number of the next free text file
iSource = FreeFile
''Write the entire file to sText
Open "C:\input.xml" For Input As #nSourceFile
sText = Input$(LOF(1), 1)
Close
'set and open file for output
iDest = FreeFile
Open "C:\input2.xml" For Output As iDest
'write project info and then a blank line. Note the comma is required
Print #fnum, Replace(sText, Chr(153), "TM")
Close #fnum
End Sub

Open in new window

0
johngr904Author Commented:
Neither Works.  They both run fine, but when I look at the revised file, the ™ symbols are still there.
0
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

cyberkiwiCommented:
What are you using to open the XML files anyway? Internet Explorer?

Instead of replacing what I thought is the raw character in the XML file, you probably have to replace this &153;

Print #fnum, Replace(sText, "&153;", "TM")

If you open the source xml from notepad, it should have &153; in it
0
johngr904Author Commented:
Doesn't make any difference using the &153, and when I open the XML file with notepad it shows the ™ symbol.
0
johngr904Author Commented:
It has something to do with the trademark symbol, because I can replace other text strings and it works fine, just not with the ™ symbol.
0
danaseamanCommented:
Trademark symbol can be Chr(153) or Unicode ChrW(2122).
Can you attach the original file that contains the ™ symbol?
0
johngr904Author Commented:
Sure, here is a sample record in the file that contains a ™ symbol.  I also tried ChrW(2122), per your reply, but that didn't work either.  I had also tried ChrW(8482).
Sample-Batch-Record.xml
0
danaseamanCommented:
Your XML file appears to be UTF-8 encoded.
Therefore the ™ in UTF-8 is "â„¢"
The original ™ must probably was UTF-16 ChrW(2122)

Try this code to replace ™ symbol:

Option Explicit

Private Sub Form_Load()
   Dim strText As String
   
   strText = GetText(App.Path & "\Sample-Batch-Record.xml")
   'Note ™ in UTF-8 is "â„¢"
   strText = Replace(strText, "â„¢", "TM")
   Debug.Print strText
End Sub

Function GetText(ByVal sFileName As String) As String
   Dim FF As Integer
   FF = FreeFile
   Open sFileName For Input As #FF
   GetText = Input$(LOF(1), 1)
   Close FF
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johngr904Author Commented:
Thanks, that was just what I needed!  Couldn't find that information anywhere!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.