Macro for find and replace

[jvamar]

Hi All,

I have one requirement for find and replace.
There are 1000's of XML tag with values..
i want a macro which should replace all the tags(the value which is in between angel brackets<> should only be removed and angel bracekst also should be removed) with or without space and tag value should remain unchanged.
Can anyone help me on this...

 

[Legacy 68668]

try

Sub test()
With CreateObject("VBScript.RegExp")
    .Pattern = "<[^>]+>"
    .Global = True
    For Each r In ActiveSheete.UsedRange
        If .test(r.Value) Then r.Value = .replace(r.Value, "")
    Next
End With
End Sub

 

[jvamar]

Hi,
Thanks for the prompt response...ther is a problem in the below line...
pls can resend the code...

Sub test()
With CreateObject("VBScript.RegExp")
.Pattern = "<[^>]+>"
.Global = True
For Each r In ActiveSheete.UsedRange--->problem in this line..
If .test(r.Value) Then r.Value = .replace(r.Value, "")
Next
End With
End Sub

 

[Weaver]

I suspect a typo

try:

For Each r In ActiveSheet.UsedRange

 

[Legacy 68668]

try

Sub test()
Dim fn As String, temp As String
fn = "c:\test.xml"  '<- change here
temp = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
With CreateObject("VBScript.RegExp")
    .Pattern = "<[^>]+>"
    .Global = True
    temp = .replace(temp, "")
End With
Open Replace(fn, "xml", "Revised.xml") For Output As #1
    Print #1, temp
Close #1
End Sub

 

[jvamar]

<TABLE class=tborder id=post1893897 cellSpacing=0 cellPadding=6 width="100%" align=center border=0><TBODY><TR vAlign=top><TD class=alt1 id=td_post_1893897 style="BORDER-RIGHT: #ffffff 1px solid">the code is working thanks....i have edited the code....there is an extra e...i have removed that....
Hi,
Thanks for the prompt response...ther is a problem in the below line...
pls can resend the code...

Sub test()
With CreateObject("VBScript.RegExp")
.Pattern = "<[^>]+>"
.Global = True
For Each r In ActiveSheete.UsedRange--->problem in this line..
If .test(r.Value) Then r.Value = .replace(r.Value, "")
Next
End With
End Sub
<!-- / message --></TD></TR><TR><TD class=alt2 style="BORDER-RIGHT: #ffffff 1px solid; BORDER-TOP: #ffffff 0px solid; BORDER-LEFT: #ffffff 1px solid; BORDER-BOTTOM: #ffffff 1px solid">

</TD><TD class=alt1 style="BORDER-RIGHT: #ffffff 1px solid; BORDER-TOP: #ffffff 0px solid; BORDER-LEFT: #ffffff 0px solid; BORDER-BOTTOM: #ffffff 1px solid" align=right><!-- controls -->

</TD></TR></TBODY></TABLE>

 

[jvamar]

if there is an xml like this then...
i want two values in seperate column....pls help me...

<env:ContentItem action="Insert"><env:Data xsi:type="EUCSTATUS_LOOKUP_LOOKUPITEM"><EucstatusLookup><CODE>N</CODE><DESCRIPTION>Not Notified</DESCRIPTION></EucstatusLookup></env:Data></env:ContentItem>

 

[jvamar]

if there is an xml which has <>code<><>value<>...
i want two values in seperate column....pls help me...
<?xml:namespace prefix = env /><env:Data xsi:type="EUCSTATUS_LOOKUP_LOOKUPITEM"><CODE><env:Conten tItem><env:Data xsi:type="EUCSTATUS_LOOKUP_LOOKUPITEM"><env:ContentIytem action="Insert"><env:Data xsi:type="EUCSTATUS_LOOKUP_LOOKUPITEM"></DESCRIPTION></EUCSTATUSLOOKUP></env:Data><env:Data xsi:type="EUCSTATUS_LOOKUP_LOOKUPITEM" <EucstatusLookup<CODE></DESCRIPTION< DESCRIPTION env:ContentItem< env:Data< EucstatusLookup<></EUCSTATUSLOOKUP></env:Data></DESCRIPTION></EUCSTATUSLOOKUP></env:Data>
</env:ContentIytem></CODE></env:Data>
</env:Conten>

 

[Legacy 68668]

try

Sub test()
Dim r As Range, m As Object, i As Long
With CreateObject("VBScript.RegExp")
    .Pattern = ">([^<]+)"
    .Global = True
    For Each r In ActiveSheet.UsedRange
        If .test(r.Value) Then
            Set m = .execute(r.Value)
            For i = 0 To m.count - 1
                r.Offset(, i + 1).Value = m.submatches(0)
            Next
        End If
    Next
End With
End Sub

 

[jvamar]

thank u very much for ur prompt response.....
the code is working....