<div>
<div class="content wysiwyg-content">
Hello,<br />
<br />
I am looking to automate the process to open many XML Files in Microsoft Excel. I can do this manually by:<br />
1) specifying the XML file to open<br />
2) The following prompt appears:<br />
<br />
&nbsp;<a href="https://filedb.experts-exchange.com/incoming/2011/08_w33/487036/xcel.jpg" target="_blank" title="xmlopenoptions (20 KB)"><img alt="xmlopenoptions" class="file-block lazyload" style="max-width: 266px;" data-src="https://filedb.experts-exchange.com/incoming/2011/08_w33/487036/xcel.jpg" /></a><br />
<br />
As shown, &quot;Use the XML Source Task Pane&quot; &nbsp;is chosen. This option allows me to chose specific fields to import in and display in the spreadsheet.<br />
<br />
3) I choose the specific fields I want and then &quot;Import&quot; data into excel to populate those specific fields.<br />
<br />
4) I rename the column headings to what I need.<br />
<br />
<br />
The question I have is if there is any way to automate this process via a script or a native &quot;automation&quot; function within Excel. Ideally, I'd like to create a separate sheet for each XML file I import in.<br />
<br />
Thank You<br />

</div>
</div>


xcel.jpg

Hello,

I am looking to automate the process to open many XML Files in Microsoft Excel. I can do this manually by:
1) specifying the XML file to open
2) The following prompt appears:

 


As shown, "Use the XML Source Task Pane"  is chosen. This option allows me to chose specific fields to import in and display in the spreadsheet.

3) I choose the specific fields I want and then "Import" data into excel to populate those specific fields.

4) I rename the column headings to what I need.


The question I have is if there is any way to automate this process via a script or a native "automation" function within Excel. Ideally, I'd like to create a separate sheet for each XML file I import in.

Thank You


Opening XML files in MS-Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Extensible Markup Language (XML) refers to the encoding of documents such that they can be read by both machines and humans. XML documents use tags to show the beginning and end of a set of data. XML is used extensively on websites to show volumes of data, and is the default for a number of office productivity suites. This topic includes discussions of XML-related technologies, such as XQuery (the XML Query language), XPath (the XML Path language), XSLT (eXtensible Stylesheet Language Transformations), XLink (the XML Linking language) and XPointer (the XML Pointer language).

VBScript (Visual Basic Scripting Edition) is an interpreted scripting language developed by Microsoft that is modeled on Visual Basic, but with some important differences. VBScript is commonly used for automating administrative and other tasks in Windows operating systems (by means of the Windows Script Host) and for server-side scripting in ASP web applications. It is also used for client-side scripting in Internet Explorer, specifically in intranet web applications.