BUG: StyleSheets Parameter of the OpenXML Method Ignored When Automating Excel (307230)


The information in this article applies to:

  • Microsoft Office Excel 2003
  • Microsoft Excel 2002
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("MSXML.DOMDocument")
    Set oXSL = CreateObject("MSXML.DOMDocument")
    oXML.Load "c:\customers.xml"
    oXSL.Load "c:\customers.xsl"
       
    'Transform the XML using the stylesheet.
    Dim sHTML As String
    sHTML = oXML.transformNode(oXSL)
        
    'Save the results to an HTML file.
    Open "c:\customers.htm" For Output As #1
    Print #1, sHTML
    Close #1

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

STATUS

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

MORE INFORMATION

Steps to Reproduce the Behavior

  1. 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
    <?xml version='1.0'?>
<?xml-stylesheet type='text/xsl' href='customers.xsl'?>
<Customers>
  <Customer>
    <CustomerID>ALFKI</CustomerID>
    <CompanyName>Alfreds Futterkiste</CompanyName>
  </Customer>
  <Customer>
    <CustomerID>ANTON</CustomerID>
    <CompanyName>Antonio Moreno Taqueria</CompanyName>
  </Customer>
</Customers>
    C:\Customers.xsl
    <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
  <xsl:template match="/">
    <HTML>
      <BODY>
        <TABLE>
          <COLGROUP WIDTH="100" ALIGN="CENTER"></COLGROUP>
          <COLGROUP WIDTH="200" ALIGN="LEFT"></COLGROUP>
          <TD><B>Customer ID</B></TD>
          <TD><B>Company</B></TD>
          <xsl:for-each select="Customers/Customer">
            <TR>
              <TD><xsl:value-of select="CustomerID"/></TD>
              <TD><xsl:value-of select="CompanyName"/></TD>
            </TR>
          </xsl:for-each>
        </TABLE>
      </BODY>
    </HTML>
  </xsl:template>
</xsl:stylesheet>
  2. In Excel, start a new workbook.
  3. Press ALT+F11 to start the Visual Basic Editor.
  4. 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 "C:\customers.xml", 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("excel.application")
    oApp.Visible = True
    oApp.Workbooks.OpenXML "c:\customers.xml", Array(1)
End Sub
  5. Close the Visual Basic Editor.
  6. 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 stylesheet applied.
  7. On the File menu, click Close to close the XML file.
  8. 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 stylesheet.

REFERENCES

For additional information about using XML with Microsoft Excel, click the following article numbers to view the articles in the Microsoft Knowledge Base:

288215 INFO: Microsoft Excel 2002 and XML

285891 HOWTO: Use Visual Basic or ASP to create an XML spreadsheet for Excel 2002

Modification Type:MajorLast Reviewed:3/13/2004
Keywords:kbAutomation kbbug KB307230 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.