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

    hello,

    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

    ReplyDelete

StumbleUpon
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.