View Full Version : Extract Values of SN tags and save to Excel

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
Do While .Execute(Findtext:=MyTags(0), Forward:=True) = True
Set rng2 = ActiveDocument.Range( _
Start:=rng.End, End:=ActiveDocument.Range.End)
With rng2.Find
.Text = MyTags(1)
Set rng3 = ActiveDocument.Range( _
Start:=rng.End, End:=rng2.Start)
msg = msg & rng3.Text & vbCrLf
End With
End With
MsgBox msg
End Sub

02-18-2013, 09:11 AM
a hint in the right direction:

Sub M_snb()
with getobject("E:\Test.doc")
for j=1 to ubound(sn)-1 step 2
msgbox sn(j)
end with
End sub

02-20-2013, 06:18 AM
How can I change the code so that the values are extracted to a range in Excel and not in a Message Box?