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

Article translations Article translations
Article ID: 307230 - View products that this article applies to.
This article was previously published under Q307230
Expand all | Collapse all

On This Page

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. 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.
  7. On the File menu, click Close to close the XML file.
  8. 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.

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

Properties

Article ID: 307230 - Last Review: March 30, 2007 - Revision: 5.1
APPLIES TO
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
Keywords: 
kbautomation kbbug KB307230

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com