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

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group