swaggerbox
02-18-2013, 05:12 AM
Found this code on another site. What it does is basically extract all values of the <SN> tags (the text in between "<SN>" and "</SN>") of a WORD document (E:\Test.doc).
How do I execute this from Excel so that the variables extracted here (msg) are stored in column A (beginning with A2), removed the duplicates and sorted alphabetically?
Any help would be greatly appreciated.
Sub Extract_SN_TAGS()
Dim MyTags()
Dim rng As Range
Dim rng2 As Range
Dim rng3 As Range
Dim msg As String
Set rng = ActiveDocument.Range
MyTags() = Array("<SN>", "</SN>")
With rng.Find
.ClearFormatting
Do While .Execute(Findtext:=MyTags(0), Forward:=True) = True
Set rng2 = ActiveDocument.Range( _
Start:=rng.End, End:=ActiveDocument.Range.End)
With rng2.Find
.ClearFormatting
.Text = MyTags(1)
.Execute
Set rng3 = ActiveDocument.Range( _
Start:=rng.End, End:=rng2.Start)
msg = msg & rng3.Text & vbCrLf
End With
Loop
End With
MsgBox msg
End Sub
How do I execute this from Excel so that the variables extracted here (msg) are stored in column A (beginning with A2), removed the duplicates and sorted alphabetically?
Any help would be greatly appreciated.
Sub Extract_SN_TAGS()
Dim MyTags()
Dim rng As Range
Dim rng2 As Range
Dim rng3 As Range
Dim msg As String
Set rng = ActiveDocument.Range
MyTags() = Array("<SN>", "</SN>")
With rng.Find
.ClearFormatting
Do While .Execute(Findtext:=MyTags(0), Forward:=True) = True
Set rng2 = ActiveDocument.Range( _
Start:=rng.End, End:=ActiveDocument.Range.End)
With rng2.Find
.ClearFormatting
.Text = MyTags(1)
.Execute
Set rng3 = ActiveDocument.Range( _
Start:=rng.End, End:=rng2.Start)
msg = msg & rng3.Text & vbCrLf
End With
Loop
End With
MsgBox msg
End Sub