Open an XML Document Directly into a List

Through the user interface, you can open an XML document directly from Excel, automatically creating an XML list that is mapped to the elements in the source XML document. This functionality can be replicated via code by using the OpenXML method of the Workbooks collection. This method returns a workbook object with the XML data mapped to your spreadsheet:

Sub ImportXMLtoList()

Dim strTargetFile As String

'Inhibit schema warning

Application.DisplayAlerts = False

'Select target XML document strTargetFile = ThisWorkbook.Path & "\EmployeeSales.xml"

'Use the OpenXML method to open the target file

Workbooks.OpenXML Filename:=strTargetFile, LoadOption:=xlXmlLoadImportToList

'Turn alerts back on

Application.DisplayAlerts = True

End Sub

First you inhibit the schema warning (where Excel tells you it will infer a schema for you) by setting the DisplayAlerts property to False. This prevents user confusion when custom-made schemas are not involved in your automated XML processes. You then assign the target XML document to a variable, allowing for some flexibility when incorporating this code into a larger process. Next, you pass the target filename to the OpenXML expression and include the xlXmlLoadlmportToList variant, telling Excel to import the XML data directly into a list object. Finally, you set the DisplayAlerts property back to True.

0 0

Post a comment