Ed from AZ
2/3/2010 2:10:00 PM
I just realized one of the major reasons I included Word in this mix.
I am pulling the data into an Excel sheet so I can add to it. I then
need to write the modified data back into the XML file.
Not being familiar with XML or XPath, my thought train was:
-- Read XML file via FSO and paste into Word
-- Find the major groups and sub-groups and set bookmarks
-- Pull the data into Excel by parsing the tags and text
-- Add new data and modify existing data as required
-- Create an array of each sub-group and group, wrapping the data in
the proper tags
-- Replace the contents of the associated bookmark range with the new
data
-- Write the whole kaboodle back into the XML file
So the hiccup in my thinking was how to get the data from Excel back
into the XML file. All I could think of was using the bookmarks and
ranges in Word.
Is there a better way?
Ed
On Jan 28, 12:26 pm, Chip Pearson <c...@cpearson.com> wrote:
> You can read the XML file directly using the MSXML library. In VBA,
> go to the Tools menu, choose References, and scroll down to "Microsoft
> XML, v6.0" and check that item. The code uses some relatively simple
> XPath to select the nodes, so I assume that you (1) already know
> XPath, or (2) can figure it out, or (3) can ask for assistance with
> XPath.
>
> Without know the actual layout of your XML, I just guessed and made up
> the following XML file:
>
> <?xml version="1.0" encoding="UTF-8"?>
> <MAIN_CATEGORY>
> <DATA_PAGE num="001">
> <GRID_LIST>
> <LINE num="001">
> <name>Chip Pearson</name>
> <city>Kansas City</city>
> </LINE>
> <LINE num="002">
> <name>Jen Filson</name>
> <city>Mission Hills</city>
> </LINE>
> </GRID_LIST>
> </DATA_PAGE>
> <DATA_PAGE num="002">
> <GRID_LIST>
> <LINE num="003">
> <name>Janice Downey</name>
> <city>Westwood Hills</city>
> </LINE>
> <LINE num="04">
> <name>Mike Stitt</name>
> <city>Charlotte</city>
> </LINE>
> </GRID_LIST>
> </DATA_PAGE>
> </MAIN_CATEGORY>
>
> Then, you can use code like
>
> Sub AAA()
> ' Required reference:
> ' Name: MSXML2
> ' Description: Microsoft XML, v6.0
> ' Typical location: C:\Windows\System32\msxml6.dll
> ' GUID: {F5078F18-C551-11D3-89B9-0000F81FE221}
> ' Major: 6 Minor: 0
>
> Dim DOM As MSXML2.DOMDocument60
> Dim FName As Variant
> Dim DataPageList As MSXML2.IXMLDOMNodeList
> Dim DataPage As MSXML2.IXMLDOMNodeList
> Dim GridList As MSXML2.IXMLDOMNodeList
> Dim Grid As MSXML2.IXMLDOMNodeList
> Dim LinesList As MSXML2.IXMLDOMNodeList
> Dim LineX As MSXML2.IXMLDOMNode
> Dim Arr() As String
> Dim N As Long
> Dim M As Long
> Dim P As Long
> Dim K As Long
> Dim R As Range
> Dim J As Long
>
> Set R = Range("C3") '<<<<< OUTPUT ON WORKSHEET
> FName = Application.GetOpenFilename("XML Files (*.xml),*.xml")
> If FName = False Then
> Exit Sub
> End If
> Set DOM = New MSXML2.DOMDocument60
> DOM.Load CStr(FName)
> DOM.setProperty "SelectionLanguage", "XPath"
>
> ' get all data pages
> Set DataPageList = DOM.SelectNodes("/MAIN_CATEGORY/DATA_PAGE")
> For N = 0 To DataPageList.Length - 1
> Set GridList = DataPageList.Item(N).SelectNodes("./GRID_LIST")
> For M = 0 To GridList.Length - 1
> Set LinesList = GridList.Item(M).SelectNodes("./LINE")
> ReDim Arr(0 To LinesList.Length - 1)
> K = 0
> For P = 0 To LinesList.Length - 1
> Set LineX = LinesList.Item(P)
> Arr(K) = LineX.ChildNodes(0).Text & vbCrLf & _
> LineX.ChildNodes(1).Text
> K = K + 1
> Next P
> '<< write to worksheet
> For J = 0 To UBound(Arr)
> R.Offset(0, J).Value = Arr(J)
> Next J
> Set R = R.Offset(1, 0)
> Next M
> Next N
> End Sub
>
> This lists the contents between the <LINE> and </LINE> tags out to the
> worksheet. You'll have to modfy the code a bit to conform with your
> actual data structure, but the code will be very muich like that shown
> above.
>
> Cordially,
> Chip Pearson
> Microsoft MVP 1998 - 2010
> Pearson Software Consulting, LLCwww.cpearson.com
> [email on web site]
>
> On Mon, 25 Jan 2010 14:21:20 -0800 (PST), Ed from AZ
>
>
>
> <prof_ofw...@yahoo.com> 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-