Tuesday, December 04, 2007

Open XML File in Excel

Here are the primitive commands to open an XML file in Microsoft Excel.

Sub Open_XML_File()

Dim oWX As Workbook

Set oWX = Workbooks.OpenXML("c:\sample.xml")

End Sub

Sub Open_XML_File_As_List()

Dim oWX As Workbook

Set oWX = Workbooks.OpenXML(Filename:="c:\sample.xml", LoadOption:=XlXmlLoadOption.xlXmlLoadImportToList)

End Sub

This option will work for Excel 2003 and above

1 comment:

  1. Anonymous7:50 AM


    this requires the file to have the XML tag at top.

    i'm presently trying to solve this, here's the solution i'm testing at this very moment

    Dim GM_ConfigFile As String, XMLTag As String
    XMLTag = "< ... xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "UTF-8" & Chr(34) & " ...>"
    GM_ConfigFile = "C:\Documents and Settings\stroyer\Application Data\Mozilla\Firefox\Profiles\fkp5w2d2.default\gm_scripts\excel_config.xml"
    Workbooks.OpenXML FileName:=GM_ConfigFile, LoadOption:=xlXmlLoadImportToList
    If ActiveWorkbook.XmlMaps.count = 0 Then Call InsertLineInFile(GM_ConfigFile, 0, XMLTag)

    InsertLineInFile function can be found at http://faq.vb.free.fr/index.php?rubrique=10


Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.