I would probably try a different approach to this, although I cannot
provide you with useful detail or code:
a. use Excel VBA and MSXML to extract the nodes and data from the XML.
You don't need a schema to do that. That should save you having to do
your own parsing, and you won't need to involve Word.
b. optionally, if it makes sense to do so, construct an XSLT transform
to transform your XML structure into something simpler to deal with, and
use MSXML to perform the transform. The impression I get is that your
XML is simple enough that you would not need that, which would be handy
because XSLT can be hard work if you are starting from scratch.
Then if I couldn't make that work I'd probably fall back to an approach
that was either similar to yours, except that right now I don't see why
it would be easier to parse the XML in a Word document rather than doing
it directly with Excel VBA.
Peter Jamieson
http://tips.pjmsn.me.uk
On 25/01/2010 22:21, Ed from AZ wrote:
This is posted to both the Word and Excel VBA NG because so far that's
the only way I can figure out to do this. But wait - I throw in FSO
to make it even messier! Using Word and Excel 2007.
We have a proprietary program that saves its working files as XML.
When a user needs to add a list of data, he's presented with either a
grid format that's a pain to work with, or a single-form-per-item page
that's easier to use but wearying after 35+ items. What I want to do
is get the part of the XML to be revised into an Excel worksheet so
the user can work easier. Because this program is proprietary, the
schema is not available to me, and since I'm not familiar with XML
except as text separated by tags, I wouldn't know how to use it.
So my bright idea so far is to open the file for reading in FSO, then
paste that into a new Word doc. This gives me a text file with XML
tags, but without Word's interpretation of the XML. Direct import
into Excel causes Excel to interpret the tags and buries the
information I want to get. Parsing the text in Word, I can set ranges
using the different tags and work my way down to the info. In my
current plan, I'm thinking of driving this from Excel.
The data is set up like so:
<MAIN_CATEGORY
<DATA_PAGE num="0001"
-- page info --
<GRID_LIST
<LINE num="0001"
-- line info --
<LINE num="0002"
-- line info --
etc.
The data I want is between the<LINE tags.
I could get all the data I need by looping through and reading each
data point from<LINE to</LINE into an array, then writing that
back into Excel. Or I could simply capture each<GRID_LIST into a
range, save it out as a separate temp XML file, then import that into
Excel.
Is this really the best way to do this?? Or is there a much easier
way? Any recommendations?
Ed