SetValue Method

Microsoft Excel Visual Basic

Show All Show All

SetValue Method

Note  XML features, except for saving files in the XML Spreadsheet format, are available only in Microsoft Office Professional Edition 2003 and Microsoft Office Excel 2003.

Maps the specified XPath object to a ListColumn object or Range collection. If the XPath object has previously been mapped to the ListColumn object or Range collection, the SetValue method sets the properties of the XPath object.

expression.SetValue(Map, XPath, SelectionNamespace, Repeating)

expression    Required. An expression that returns one of the objects in the Applies To list.

Map    Required XmlMap. The schema map that contains information about the XPath object.

XPath    Required String. A valid XPath statement.

SelectionNamespace    Optional Variant. Specifies any namespace prefixes used in the XPath argument. This argument can be omitted if the XPath object doesn't contain any prefixes, or if the XPath object uses the Microsoft Excel prefixes.

Repeating    Optional Boolean. Specifies whether the XPath object is to be bound to a column in an XML list, or mapped to a single cell. Set to True to bind the XPath object to a column in an XML list.

Example

The following example creates an XML list based on the "Contacts" schema map that is attached to the workbook, and then uses the SetValue method to bind each column to an XPath object.

    Sub CreateXMLList()
    Dim mapContact As XmlMap
    Dim strXPath As String
    Dim lstContacts As ListObject
    Dim objNewCol As ListColumn

    ' Specify the schema map to use.
    Set mapContact = ActiveWorkbook.XmlMaps("Contacts")
    
    ' Create a new list.
    Set lstContacts = ActiveSheet.ListObjects.Add
        
    ' Specify the first element to map.
    strXPath = "/Root/Person/FirstName"
    ' Map the element.
    lstContacts.ListColumns(1).XPath.SetValue mapContact, strXPath

    ' Specify the second element to map.
    strXPath = "/Root/Person/LastName"
    ' Add a column to the list.
    Set objNewCol = lstContacts.ListColumns.Add
    ' Map the element.
    objNewCol.XPath.SetValue mapContact, strXPath

    strXPath = "/Root/Person/Address/Zip"
    Set objNewCol = lstContacts.ListColumns.Add
    objNewCol.XPath.SetValue mapContact, strXPath
End Sub