Please help:need to read XML, edit, and write back to XML file

[Ed from AZ]

I'm using Word 2003. I use a proprietary Java program that takes all
the information in the various forms and generates an XML file. There
are times I need to move information between files. I tried to
explore this in the Excel and Access NG, because the data format would
lend itself very well to a spreadsheet or table. But I reached a dead
end.

My thought is to create a macro that would find the proper tags and
parse out all the data in between the correct tags into a table or
spreadsheet. Then, after I have cut / pasted / mutilated the data as
required, I can write the new data back into an array, append the
correct tags, and write it back into the original XML file.

One problem is that when I open an XML file in Word 2003, I see on
screen the cute little visual representations of the tags - but they
aren't really there. At least, they don't show up in Print Preview,
and Paragraph.Range.Text doesn't see them. I found a clue in one NG
post and opened the Script Editor (ALT + SHIFT + F11), and saw tags
and much more. But I still have no idea how to accomplish what seems
to be the simple project of writing out the data from certain tags and
then writing it back when I'm done.

Can anyone please drop-kick me in a good direction to get started
towards this?

Ed

 

[Steve Yandl]

Ed,

If you were only looking for a few tags, it would be simple enough to parse
out the text strings in a VBA routine using InStr and Mid functions but I
gather you're dealing with a more complex scenario.

Try doing a web search using the term
MSXML2.DOMDocument
and I think you may find some helpful links with examples of using that
object within VBA.

Steve

 

[Ed from AZ]

Actually, Steve, I really am (I think!!) just looking for a few tags.
The problem is that when I view the doc in Word, I don't see <tag>text<
\tag>. Instead I see this nice colored autoshape-looking graphic with
"text" in the middle of it.

As I ssaid, I tried iterating through all the paragraphs and trying
different outputs of the Paragraph.Range - .Text, .XMLNode, and so
forth - to see if I could find the tag, but no luck so far.

If I open the XML in NotePad or WordPad, then I see <tag>text<\tag>.
But I don't know how to capture and manipulate that with Word VBA, and
then have the confidence of writing it back into the XML file.

I will search for the MSXML2.DOMDocument and see what I get.

Thanks for the input.

Ed

 

[Steve Yandl]

Ed,

Take a look at this link.
http://www.microsoft.com/technet/scriptcenter/resources/qanda/oct07/hey1023.mspx

The information is for extracting text with a vbScript file but almost all
of the code can be used in a VBA routine. At the end where they use
WScript.Echo
That is very similar to MsgBox and the variable that comes afterward is the
text that was pulled from between the tags. I think I'd be inclined to do
this from a VBA routine in Excel and bring text strings into different cells
rather than messing with a Word table or multiple paragraphs.

Steve

 

[Astrid]

Hi Ed,

Something like this?

Dim oXMLNode As XMLNode

For Each oXMLNode In ActiveDocument.XMLNodes
Debug.Print oXMLNode.BaseName & " " & oXMLNode.Text
Next

Set oXMLNode = Nothing

 

[Ed from AZ]

Thank you, Steve!! This got me farther than anything else yet!

And it's perfect - it puts everything in a string with all the tags,
which I can then parse into separate elements and write into an Excel
worksheet or a Word table. (Excel may be better, but with some of my
data, Excel's desirte to automatically format cells rather than just
accept whatever comes may be a problem.)

Okay - now comes the fun part:
After I've done all my data manipulations in Word or Excel, I can
write everything back into a string.
Is it then as simple as replacing the original string with the new
string and closing the file?

Ed

 

[Steve Yandl]

Ed,

If you stick with the Scripting FileSystemObject, you can once again read
the entire XML (text) file into a string and then use the 'Replace' function
to replace the tags and text between them with your updated strings. When
you work with text files with the filesystemobject, you open them for
reading, writing or appending. Always keep in mind that if you open the
file for writing, existing text will be overwritten so make sure you back up
your xml files as you test options. The scripting site I directed you to
has scores of other sample scripts that might give you ideas.

You might want to take a look at what Astrid posted in this thread. It
might be an even more efficient way to handle the problem.


Steve

 

[Ed from AZ]

You might want to take a look at what Astrid posted in this thread. It

might be an even more efficient way to handle the problem.

I copied Astrid's code into a VBA module and ran it, but it didn't
give me any of the tags. It did, though, give me whole chinks of info
that I didn't see in the doc.

I think I would rather work with the whole file simply as a large
string, as the scripting code gave me. If I parse carefully along the
tags I know should be there, I ought to be able to do just about
anything I want and build a new string with my new data - providing
simply writing that new string back into the document won't screw
things up!

But, yes, for that reason, I an working with unneeded copies of other
documents. That much I have learned!

Thanks for all the input and direction.

Ed