Microsoft Office Tutorials and References
In Depth Information
Caution This procedure assumes the worksheet that you’re manipulating is blank and
that you haven’t mapped the MySuppliers.xsd schema to the active workbook. In fact, it’s
best if you run this procedure on a new workbook.
Sub CreateOneList()
Dim myMap, myMap2 As XmlMap
Dim xSchemaFile, strXPath, strSelNS, xMapName, xDataFile As String
Range(“A2”).Value = “SupplierID"
Range(“B2”).Value = “CompanyName"
Range(“C2”).Value = “ContactName"
Range(“D2”).Value = “ContactTitle"
Range(“E2”).Value = “Address"
Range(“F2”).Value = “City"
Range(“G2”).Value = “Region"
Range(“H2”).Value = “Postal Code"
Range(“I2”).Value = “Country"
Range(“J2”).Value = “Phone"
Range(“K2”).Value = “Fax"
xSchemaFile = “C:\MySuppliers.xsd"
Set myMap = ActiveWorkbook.XmlMaps.Add(xSchemaFile, “Root”)
ActiveSheet.ListObjects.Add(xlSrcRange, Range(“A2:K2”), , xlYes).Name = _
“List1"
Range(“A3”).Select
On Error Resume Next
xDataFile = “C:\MySuppliers.xml"
Set myMap2 = ActiveWorkbook.XmlMaps(“Root_Map”)
strXPath = “/Root/Supplier/SupplierID"
Range(“B2”).XPath.SetValue myMap, strXPath
Range(“B2”).Value = “Supplier ID"
strXPath = “/Root/Supplier/CompanyName"
Range(“C2”).XPath.SetValue myMap, strXPath
strXPath = “/Root/Supplier/ContactName"
Range(“D2”).XPath.SetValue myMap, strXPath
strXPath = “/Root/Supplier/ContactTitle"
Range(“E2”).XPath.SetValue myMap, strXPath
strXPath = “/Root/Supplier/MailingAddress/Address"
Range(“F2”).XPath.SetValue myMap, strXPath
strXPath = “/Root/Supplier/MailingAddress/City"
Range(“G2”).XPath.SetValue myMap, strXPath
Search JabSto ::




Custom Search