Microsoft KB Archive/307230

= The StyleSheets parameter of the OpenXML method is ignored when you try to automat Excel =

Article ID: 307230

Article Last Modified on 3/30/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition

-



This article was previously published under Q307230



SYMPTOMS
When you call the OpenXML method, your code may be interrupted with the Excel Import XML dialog box. User intervention is required for your code to continue. This problem occurs when both of the following conditions are met:
 * You are calling the OpenXML method from an Automation client.
 * You have provided a value for the StyleSheets parameter of the OpenXML method.



RESOLUTION
To work around this problem, transform the XML before you open it in Excel and omit the value for the StyleSheets parameter. To do this, use Microsoft XML (MSXML) to load the XML and XSL into DOMDocument objects so that you can transform the XML at run time and save the results to a file. The resulting file can then be opened in Excel without user intervention. Sub Macro3 'Load the XML and the XSL (the stylesheet). Dim oXML As Object, oXSL As Object Set oXML = CreateObject(&quot;MSXML.DOMDocument&quot;) Set oXSL = CreateObject(&quot;MSXML.DOMDocument&quot;) oXML.Load &quot;c:\customers.xml&quot; oXSL.Load &quot;c:\customers.xsl&quot; 'Transform the XML using the stylesheet. Dim sHTML As String sHTML = oXML.transformNode(oXSL) 'Save the results to an HTML file. Open &quot;c:\customers.htm&quot; For Output As #1 Print #1, sHTML Close #1

'Automate Excel to open the HTML file. Dim oApp As Excel.Application Set oApp = CreateObject(&quot;excel.application&quot;) oApp.Visible = True oApp.Workbooks.Open &quot;c:\customers.htm&quot; End Sub



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.



Steps to Reproduce the Behavior
  Using a text editor such as Notepad.exe, create the following XML and XSL files and save them as C:\Customers.xml and C:\Customers.xsl, respectively:C:\Customers.xml     ALFKI Alfreds Futterkiste   ANTON</CustomerID> Antonio Moreno Taqueria</CompanyName> </Customer> </Customers> C:\Customers.xsl <xsl:stylesheet xmlns:xsl=&quot;http://www.w3.org/1999/XSL/Transform&quot; version=&quot;1.0&quot;> <xsl:template match=&quot;/&quot;> <HTML> <BODY> </BODY> </HTML> </xsl:template> </xsl:stylesheet> </li> <li>In Excel, start a new workbook.</li> <li>Press ALT+F11 to start the Visual Basic Editor.</li> <li> On the Insert menu, click Module to insert a new code module, and then add the following code to the module: Sub Macro1 'Open the XML file and apply the first stylesheet. Workbooks.OpenXML &quot;C:\customers.xml&quot;, Array(1) End Sub

Sub Macro2 'Automate Excel to open the XML file and apply the first 'stylesheet. Dim oApp As Excel.Application Set oApp = CreateObject(&quot;excel.application&quot;) oApp.Visible = True oApp.Workbooks.OpenXML &quot;c:\customers.xml&quot;, Array(1) End Sub </li> <li>Close the Visual Basic Editor.</li> <li>Excel 2002 or Excel 2003 <ul> <li>On the Tools menu, click Macro and then click Macros. In the list of macros, select Macro1 and click Run. Note that the XML file is opened with the style sheet applied.</li></ul>

Excel 2007 <ul> <li>On the Developer tab, click Macros in the Code group. Click to select the &quot;Macro1&quot; macro in the list, and then click Run.

Note If the Developer tab is not visible on the Ribbon, follow these steps to enable the tab: <ol style="list-style-type: lower-alpha;"> <li>Click the Microsoft Office Button, and then click Excel Options.</li> <li>Click the Popular tab, and then click to select the Show Developer Tab in the Ribbon option.</li> <li>Click OK.</li></ol> </li></ul> </li> <li>On the File menu, click Close to close the XML file.</li> <li>Excel 2002 or Excel 2003 <ul> <li>On the Tools menu, click Macro and then click Macros. In the list of macros, select Macro2 and click Run. Note that this time the Import XML dialog box appears, and you are prompted to select the style sheet.</li></ul>

Excel 2007 <ul> <li>On the Developer tab, click Macros in the Code group. Click to select the &quot;Macro1&quot; macro in the list, and then click Run.</li></ul> </li></ol>

<div class="references_section">