PRB: Long Delay When You Navigate Away from a Web Page That Contains Office Spreadsheet Component (276249)


The information in this article applies to:

  • Microsoft Office Spreadsheet Component 9.0 1.0
This article was previously published under Q276249

SYMPTOMS

When you navigate away from a Web page that contains a Spreadsheet component, you experience a long delay before the next page is displayed in the browser. During this delay, note that the cube at the upper-left corner of the Spreadsheet component spins to indicate that the Spreadsheet component is still working.

CAUSE

The long delay may occur if there is a large amount of information that the Spreadsheet component must persist prior to when it unloads the Web page. The amount of persisted data might be larger than you expect if the last "dirty" cell on the Spreadsheet is much farther down or across the Spreadsheet than the actual range that contains the data. Unused cells on the Spreadsheet may be dirtied unexpectedly if you apply a format or perform some other action to entire rows or columns on the Spreadsheet.

The "More Information" section describes scenarios where you might experience this behavior, and also provides tips to resolve a long delay while you navigate away from the Web page.

STATUS

This behavior is by design.

MORE INFORMATION

Scenario 1: Manipulate Entire Rows and Columns

The following HTML generates a Web page that contains a Spreadsheet component. Data is added to cells A1:F300 and the unused columns of the Spreadsheet are hidden. You might expect the component to report the UsedRange property for the sheet to be A1:F300 (300 rows x 6 columns = 1,800 cells). However, hiding columns G:ZZ dirties rows 1 through 300, so the component actually reports the UsedRange property as 1:300 (300 rows x 702 columns = 210,600 cells). Therefore, when you navigate away from this Web page, the component persists information for all 210,600 cells, rather than just 1,800 cells as you expect. 
<HTML> 
<BODY> 
<OBJECT classid=clsid:0002E510-0000-0000-C000-000000000046 id=SSheet>
</OBJECT> 
<SCRIPT LANGUAGE=vbscript> 
Sub Window_onload() 
   SSheet.ActiveSheet.Range("A1:F300").Value = "test" 
   SSheet.ActiveSheet.Range("G1:ZZ1").EntireColumn.Hidden = True 
   MsgBox SSheet.ActiveSheet.UsedRange.Address
End Sub 
</SCRIPT> 
</BODY> 
</HTML>
The delay you might experience in this scenario can be corrected easily if you set the ViewableRange property for the Spreadsheet component to a specific range rather than hide entire rows or columns on the Spreadsheet: 
Sub Window_onload() 
   SSheet.ActiveSheet.Range("A1:F300").Value = "test"
   SSheet.ViewableRange = SSheet.ActiveSheet.UsedRange.Address
   MsgBox SSheet.ActiveSheet.UsedRange.Address
End Sub

Scenario 2: Apply AutoFilters to Entire Columns

Another scenario is when you apply an AutoFilter to entire columns on the Spreadsheet: 
<HTML> 
<BODY> 
<OBJECT classid=clsid:0002E510-0000-0000-C000-000000000046 id=SSheet>
</OBJECT> 
<SCRIPT LANGUAGE=vbscript> 
Sub Window_onload() 
   SSheet.ActiveSheet.Range("A1:C1").Value = Array("Field1", "Field2", "Field3")
   SSheet.ActiveSheet.Range("A2:C300").Formula = "=MOD(ROW(),4)"
   SSheet.ActiveSheet.Range("A1:C1").EntireColumn.AutoFilter
End Sub 
Sub Window_onBeforeUnload()
   MsgBox SSheet.ActiveSheet.UsedRange.Address
End Sub
</SCRIPT> 
</BODY> 
</HTML>
After this HTML page is loaded, if you apply a filter to exclude some of the rows in the Spreadsheet and then navigate away from the page, the BeforeUnload event may report the UsedRange of the Spreadsheet to be A:C (65,536 rows x 3 columns = 196,608 cells). As in the previous scenario, apply the AutoFilter to a specific range instead of entire columns to resolve the long delay in navigation: 
   SSheet.ActiveSheet.UsedRange.AutoFilter

Scenario 3: Spreadsheet Built at Design Time

Spreadsheets can be set up in design time with tools such as Microsoft FrontPage or Microsoft Visual InterDev. If you apply formats or add data to a Spreadsheet component when you design your Web page, you might also find that the last cell on the Spreadsheet is much farther down or farther across the Spreadsheet than you expect. When you design your Web page, press the CTRL+END keys to determine the address of the last cell on the Spreadsheet.

To correct unnecessary delays when you navigate away from your Web page, reset the last cell by following these steps:
  1. Select the column header for the first unused column on the Spreadsheet.
  2. Press CTRL+SHIFT+RIGHT ARROW, and note that this selects all of the columns from the column that you selected through column ZZ.
  3. Right-click the selection, choose Delete from the menu, and then choose Columns.
  4. Select the row header for the first unused row on the Spreadsheet.
  5. Press CTRL+SHIFT+DOWN ARROW, and note that this selects all rows from the row you selected through row 65,536.
  6. Right-click the selection, select Delete from the menu, and then choose Rows.
  7. Save your changes.
Modification Type:MajorLast Reviewed:11/12/2001
Keywords:kbDSupport kbOfficeWebSpread kbprb kbProgramming KB276249
©2002 Microsoft Corporation. All rights reserved.