How to import data in XML file to Excel sheet

[Manu Krrish]

XML,Excel Macros

Hi I am working on some stuff where i need to Import the data present in XML file to excel sheet.
i wrote below code for it

  Sub ImportXMLtoList()
         Dim strTargetFile As String
         Application.DisplayAlerts = False
         strTargetFile = "C:\BookData.xml"
         Workbooks.OpenXML Filename:=strTargetFile, LoadOption:=xlXmlLoadImportToList
         Application.DisplayAlerts = True
    End Sub

But when i run above code i am getting error something related to "Schema"

Looking for help guys.

Thanks in Advance

[AndreRet]

Can you elaborate more on the something like part error of schema. With a full on error I'm sure we can help. It seems that the xml schema loaded incorrectly...

[Manu Krrish]

Hi AndreRet Thanks for your reply.

I want to do it in Excel 2003 using Macros. When i run the same above code in MS2007 it imports data succesfully,but it opens an new excel file and paste the data in Row order.
But when i run the same code in MS2003 a pop saying "The specified XML Source Doesnt refer to a schema.Excel will create schema based on the XML source data" is displayed, when i click ok button then Nothing is pasted in Excel.

I can say XML contains Repeating and Non-Repeating Elements.Is there any way to import the required elements data instead of all data present in XMl.

Thanks in advance

[AndreRet]

It seems the code for excel 2003 is different than that for 2007 -

This from Microsoft...

expression.Import(Url, Overwrite)

expression Required. An expression that returns one of the objects in the Applies To list.
Url Required String. The path to the XML data to import. The path can be specified in Universal Naming convention (UNC) or Uniform Resource Locator (URL) format. The file can be an XML data file or a valid Office XML data Source Connection (.uxdc) file.
Overwrite Optional Variant. Set to True to overwrite existing data. Set to False to append to existing data. The default value is False.

Also, if the user has Excel Standard Edition 2003 because it does not include the XML import functionality - namely the Workbook.XmlImport function.

Try the following code for 2003...

Sub ImportXMLData() 
    Dim xlImportResult As XlXmlImportResult 

    xlImportResult = ThisWorkbook.XmlMaps("Invoice_Map").Import("C:\invoice.xml", True) 

    Select Case xlImportResult 
        Case xlXmlImportElementsTruncated 
            Debug.Print "XML data items imported with truncation." 
        Case xlXmlImportSuccess 
            Debug.Print "XML data items imported successfully." 
        Case xlXmlImportValidationFailed 
            Debug.Print "XML data items not imported. Validation failed." 
        Case Else 
            Debug.Print "Data import process reported an unknown result code." 
    End Select 

    Set xlImportResult = Nothing 
End Sub