Visual Basic 6.0

Parse the XML of an Excel (XLS) Spreadsheet

See more XML Examples

Demonstrate how to access various items from the XML of an XLS (Excel) Spreadsheet. Our sample data is shown below, and can be downloaded from http://www.chilkatsoft.com/data/ndc_nhric_labeler_codes_04_29_2015.xls

<?xml version="1.0" version="1.0" encoding="utf-8" ?>
<mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">
    <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
        <Version>14.00</Version>
    </DocumentProperties>
    <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
        <AllowPNG />
    </OfficeDocumentSettings>
    <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
        <WindowHeight>7800</WindowHeight>
        <WindowWidth>12000</WindowWidth>
        <WindowTopX>105</WindowTopX>
        <WindowTopY>105</WindowTopY>
        <ProtectStructure>False</ProtectStructure>
        <ProtectWindows>False</ProtectWindows>
    </ExcelWorkbook>
    <Styles>
        <Style ss:ID="Default" ss:Name="Normal">
            <Alignment ss:Vertical="Top" />
            <Borders />
            <Font ss:FontName="Arial" />
            <Interior />
            <NumberFormat />
            <Protection />
        </Style>
        <Style ss:ID="s63">
            <Alignment ss:Vertical="Bottom" ss:WrapText="1" />
            <Font ss:FontName="Arial" x:Family="Swiss" ss:Bold="1" />
            <Interior ss:Color="#C0C0C0" ss:Pattern="Solid" />
        </Style>
        <Style ss:ID="s64">
            <NumberFormat ss:Format="@" />
        </Style>
    </Styles>
    <Worksheet ss:Name="NDCLabelerCode">
        <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="8186" x:FullColumns="1" x:FullRows="1">
            <Column ss:Index="2" ss:Width="580.5" />
            <Row ss:AutoFitHeight="0" ss:Height="38.25">
                <Cell ss:StyleID="s63">
                    <Data ss:Type="String">NDC Labeler Code</Data>
                </Cell>
                <Cell ss:StyleID="s63">
                    <Data ss:Type="String">Firm Name</Data>
                </Cell>
            </Row>
            <Row>
                <Cell ss:StyleID="s64">
                    <Data ss:Type="String">0002</Data>
                </Cell>
                <Cell ss:StyleID="s64">
                    <Data ss:Type="String">Eli Lilly and Company</Data>
                </Cell>
            </Row>
...

Visual Basic 6.0

Dim success As Long
success = 0

Dim xlsDoc As New ChilkatXml

success = xlsDoc.LoadXmlFile("myFiles/ndc_nhric_labeler_codes_04_29_2015.xls")
If (success <> 1) Then
    Debug.Print xlsDoc.LastErrorText
    Exit Sub
End If

' This example will access the following pieces of data:
' NDC Labeler Code, Firm Name, 0002, Eli Lilly and Company, ...

' First navigate to the Worksheet named "NDCLabelerCode"
Dim xWorksheet As ChilkatXml
Set xWorksheet = xlsDoc.GetChildWithAttr("Worksheet","ss:Name","NDCLabelerCode")

' Next, get the Table node:
Dim x As ChilkatXml
Set x = xWorksheet.GetChildWithTag("Table")

' How many immediate child nodes?
Dim numChildren As Long
numChildren = x.NumChildren

' Loop over each Row and access the cell contents.
Dim i As Long
For i = 0 To (numChildren - 1)

    ' Navigate without creating new XML objects
    success = x.GetChild2(i)

    If (x.TagEquals("Row")) Then

        Dim data0 As String
        data0 = x.ChilkatPath("Cell[0]|Data|*")
        Debug.Print data0

        Dim data1 As String
        data1 = x.ChilkatPath("Cell[1]|Data|*")
        Debug.Print data1
    End If

    ' Navigate back up.
    success = x.GetParent2()
Next