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?
 
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
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
johngr904Author Commented:
Neither Works.  They both run fine, but when I look at the revised file, the ™ symbols are still there.
0
 
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
 
johngr904Author Commented:
Thanks, that was just what I needed!  Couldn't find that information anywhere!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.