Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Heres hoping some enterprising sole has created some code for this and not got it picked up by the Search Engines (IE done the net thing).

    I'm in the middle of a project to create an XML document from an Access 2007 (in Access 2000 Compatibility mode) Table. I've managed to emulate the correct format, so thats all sorted, however some of the data is being pasted into text fields from HTML sources and inherits Special Characters (for Example in XML & (thats an Ampersand) needs to be written as & to display properly in XML, but shows in access as "&", however theres also a problem with , and sundry white spaces, "-" et al.

    The Problem; While I can thump out Variable = Replace(Variable, "&","&") etc. This will all be retroactive as I discover other examples which "kills" the XML file.

    Is there a Sub Routine or an Add-in that anyone knows about that will do this for me.

    As a thought also does Access have a feature that will restrict these characters after selecting certain formating???

    TX All

    Graliv

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I used this for shaping up some text to go in an url, maybe it helps?

    Code:
    Const sCriticalchars As String = "$&+,/:;=?@ ""<>#%{}|\^~[]`"
    Const sReplacementCodes As String = "%24,%26,%2B,%2C,%2F,%3A,%3B,%3D,%3F,%40,%20,%22,%3C,%3E,%23,%25,%7B,%7D,%7C,%5C,%5E,%7E,%5B,%5D,%60"
    
    Function MakeProperData(sData As String) As String
    	Dim lCount As Long
    	Dim vReplacementCodes As Variant
    	Dim sNewData As String
    	vReplacementCodes = Split(sReplacementCodes, ",")
    	sNewData = ""
    	For lCount = 1 To Len(sData)
    		If InStr(sCriticalchars, Mid(sData, lCount, 1)) > 0 Then
    			sNewData = sNewData & vReplacementCodes(InStr(sCriticalchars, Mid(sData, lCount, 1)) - 1)
    		Else
    			sNewData = sNewData & Mid(sData, lCount, 1)
    		End If
    	Next
    	MakeProperData = sNewData
    End Function
    
    Function MakeProperDataReverse(sData As String) As String
    	Dim lCount As Long
    	Dim vReplacementCodes As Variant
    	Dim sNewData As String
    	vReplacementCodes = Split(sReplacementCodes, ",")
    	sNewData = sData
    	For lCount = 0 To UBound(vReplacementCodes)
    		sNewData = Replace(sNewData, vReplacementCodes(lCount), Mid(sCriticalchars, lCount + 1, 1))
    	Next
    	MakeProperDataReverse = sNewData
    End Function
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Looks Interesting the Clock Has Ticked however so tomorrow I'll report back.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •