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

View products that this article applies to.

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.

↑ Back to the top

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

↑ Back to the top

Status

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

↑ Back to the top

More information

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

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

In Excel, start a new workbook.
Press ALT+F11 to start the Visual Basic Editor.

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

Close the Visual Basic Editor.
Excel 2002 or Excel 2003
- 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.
Excel 2007
- On the Developer tab, click Macros in the Code group. Click to select the "Macro1" 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:
  1. Click the Microsoft Office Button, and then click Excel Options.
  2. Click the Popular tab, and then click to select the Show Developer Tab in the Ribbon option.
  3. Click OK.
On the File menu, click Close to close the XML file.
Excel 2002 or Excel 2003
- 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.
Excel 2007
- On the Developer tab, click Macros in the Code group. Click to select the "Macro1" macro in the list, and then click Run.

↑ Back to the top

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

↑ Back to the top