Solved

Importing XML into an Excel 97/2000 worksheet

Posted on 2002-03-14
6
403 Views
Last Modified: 2013-11-19
Hi,

Hopefully a simple one.

Wonder if anyone can supply some VBA code that when run in Excel 97 will take XML from an HTTP URL source and store the entries in the worksheet cells.

eg.
<?xml version="1.0" encoding="UTF-8" ?>
<people total="3">
<person name="Bob" siblings="2">
<child name="Bill"></child>
<child name="Ben"></child>
</person>
<person name="Anne" siblings="1">
<child name="Charlie"></child>
</person>
<person name="Dave" siblings="3">
<child name="Eric"></child>
<child name="Fred"></child>
<child name="George"></child>
</person>
</people>


Ideally, the worksheet would be similar to,

Bob,Bill
Bob,Ben
Anne,Charlie
Dave,Eric
Dave,Fred
Dave,George


This may sound simple in principle but I'm not even sure it can be done.

No external software or 3rd party parsers can be installed except Office 97 (or possibly 2000 at a push) and the parser that comes with IE5+.

I've had a good dig around the MSDN site but can find anything other than all the XP stuff.


To someone who knows their stuff this should be 300 easy points :-)


TIA,

Alex

0
Comment
Question by:Alex_Gould
  • 3
  • 2
6 Comments
 
LVL 44

Accepted Solution

by:
bruintje earned 300 total points
ID: 6867529
Hi Alex_Gould, i think the samples from this page can help a lot

http://www.geocities.com/tomizono/tools/xml2xl.html

HTH:O)Bruintje
0
 

Author Comment

by:Alex_Gould
ID: 6873504
Hi,


Much appreciated.

The example on this site is almost ideal. It is a VB program, and I'm really after a VBA macro.

But this has given me the prompt I needed.

I really appreciate the fact that even though you didn't post any sample code, you didn't just copy of modify these and claim it as your own. That shows integrity !

I'm including the code in this comment so if anyone comes by after the same solution this then they will have the same code - just in case Mr. Tomizono site is removed.

It is released under GNU and so the aim is to help people afterall.

Many thanks again.

Alex

______________________________


Attribute VB_Name = "ConvXml2XL1"
Option Explicit

Dim xmlUrl As String, ErrMes As String

Sub okGo()
' main program
    xmlUrl = "C:\tmp\resume-1.xml"     ' URL of a target XML
    If Not convMain Then MsgBox ErrMes    ' execution
End Sub

'___________________________________________________________
'conv-xml2xl-1 version 1.1, Copyright (C) 2000 Tomizono <tomizono@yahoo.com>
'conv-xml2xl-1 comes with ABSOLUTELY NO WARRANTY. This is free software,
'and you are welcome to redistribute it under certain conditions.
'See http://www.gnu.org/copyleft/gpl.html#SEC3 for details.
'___________________________________________________________
'convert an xml document into an MS Excel Worksheet. (DOM Tree view)
'this is a VBA module source for MS Excel.
'input: well-formed XML document
'output: Microsoft Excel Book
'this version: http://www.geocities.com/tomizono/gpl/2000/conv-xml2xl-1.1.1.bas
'lattest: http://www.geocities.com/tomizono/gpl/conv-xml2xl-1.bas
'
'Further information is available at:
'http://www.geocities.com/tomizono/tools/xml2xl.html
'___________________________________________________________
'conv-xml2xl-1: convert an xml into an MS Excel book.
'Copyright (C) 2000 Tomizono <tomizono@yahoo.com>
'
'This program is free software; you can redistribute it and/or modify
'it under the terms of the GNU General Public License as published by
'the Free Software Foundation; either version 2 of the License, or
'(at your option) any later version.
'
'This program is distributed in the hope that it will be useful,
'but WITHOUT ANY WARRANTY; without even the implied warranty of
'MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
'GNU General Public License for more details.
'
'You should have received a copy of the GNU General Public License
'along with this program; if not, write to the Free Software
'Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
'___________________________________________________________

Function LoadXML() As Object
    ' load specified XML, return the object
    Set LoadXML = CreateObject("MSXML.DOMDocument")
    LoadXML.async = False
    LoadXML.Load (xmlUrl)
End Function
   
Function CheckXML(xmlObj As Object) As Boolean
    ' validate XML
    Dim pErr As Object
    Set pErr = xmlObj.parseError
    If (pErr.errorCode <> 0) Then
        ErrMes = "Error: " & pErr.url & " cannot be parsed." & vbCrLf _
            & "Error reason: " & pErr.reason & vbCrLf _
            & "Error position: " & pErr.srctext & " (line " & pErr.Line & ")"
        CheckXML = False
    Else
        CheckXML = True
    End If
End Function

Function convMain() As Boolean
' convert to Excel
    Dim MySheet As Worksheet
    Dim xmlObj As Object
   
    ' before
    convMain = False
    Set xmlObj = LoadXML          ' load XML
    If Not CheckXML(xmlObj) Then  ' check XML
        Set xmlObj = Nothing
        Exit Function
    End If
    Set MySheet = MakeMyBook      ' create a new Book
    MySheet.Activate
    xml2xl1 xmlObj, MySheet.Range("A1")     ' DOM to Range
    ' after
    With MySheet.Cells
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
        .WrapText = False
    End With
   
    Set xmlObj = Nothing
    Set MySheet = Nothing
    convMain = True
End Function

Function MakeMyBook() As Worksheet
' create a new Book, return a sheet object of the book
    Set MakeMyBook = Application.Workbooks.Add.Worksheets(1)
End Function

Function xml2xl1(iNode As Object, MyRange As Range) As Boolean
' read DOM and write to Range
    Dim iNode2 As Object
    Dim iAttr As Object
    Dim i As Long
    Const NODE_ELEMENT = 1
    Const NODE_DOCUMENT = 9
   
    If (iNode.nodeType = NODE_ELEMENT) Then
   
    ' Name and Text of myself (element)
        MyRange.Value = iNode.nodeName
        MyRange.Interior.ColorIndex = 40
        If iNode.selectNodes("text()").Length > 0 Then
            Set MyRange = MyRange.Offset(0, 1)
            MyRange.Value = iNode.selectSingleNode("text()").Text
            MyRange.Interior.ColorIndex = 19
            Set MyRange = MyRange.Offset(1, 0)
        Else
            Set MyRange = MyRange.Offset(1, 1)
        End If
    ' Attributes
        Set iAttr = iNode.Attributes
        For i = 0 To iAttr.Length - 1
            MyRange.Value = iAttr(i).Name
            MyRange.Interior.ColorIndex = 35
            Set MyRange = MyRange.Offset(0, 1)
            MyRange.Value = iAttr(i).Text
            MyRange.Interior.ColorIndex = 19
            Set MyRange = MyRange.Offset(1, -1)
        Next i
        Set iAttr = Nothing
   
    ElseIf (iNode.nodeType = NODE_DOCUMENT) Then
        MyRange.Value = xmlUrl
        Set MyRange = MyRange.Offset(1, 1)
    End If
   
    ' child Elements
    Set iNode2 = iNode.selectNodes("*")
    For i = 0 To iNode2.Length - 1
        xml2xl1 iNode2(i), MyRange
    Next i
    Set MyRange = MyRange.Offset(0, -1)
    Set iNode2 = Nothing
   
    xml2xl1 = True
End Function





0
 
LVL 44

Expert Comment

by:bruintje
ID: 6873574
why the C?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Alex_Gould
ID: 6883860
Sorry, should have been a B.


My mistake.

Alex
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6884099
Corrected per your request in Community Support.
Moondancer - EE Moderator
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6884126
gosh, didn't even think to long about it, was only a first reaction ;)

But thanks anyway
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel VBA, find a string in a column, update a cell 7 67
The blinking Office button 4 38
TT Auto Dashboard 13 85
Why doesn't duplicate values work on this spreadsheet? 6 35
Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
JavaScript has plenty of pieces of code people often just copy/paste from somewhere but never quite fully understand. Self-Executing functions are just one good example that I'll try to demystify here.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now