PRB: Setting Range.Value of Office Spreadsheet to a JScript Array Ignores Blank Entries (286315)


The information in this article applies to:

  • Microsoft Office 2003 Web Components
  • Microsoft Office Chart Component 9.0
  • Microsoft Office XP Web Components
This article was previously published under Q286315

SYMPTOMS

When you populate a range of cells in the Office Spreadsheet Component from a Microsoft JScript array, if an element in the array is empty (omitted), the corresponding cell is not skipped. Instead, the next element of the array becomes the value of the next cell in the range being populated.

RESOLUTION

Alternative solutions include:
  • Replace the blank element of the array with an empty string by using two consecutive quotation marks. -or-

  • Create the array as a SafeArray in Microsoft Visual Basic Script, and then populate the range in JScript and use the SafeArray as a Variant. (Note JScript cannot create SafeArrays.)

MORE INFORMATION

Steps to Reproduce Behavior

Note The following sample code uses the CLSID for the Office 2000 Spreadsheet Component.

To use the Office XP Spreadsheet Component, replace the CLSID in the <object> element with:

0002E551-0000-0000-C000-000000000046



To use the Office 2003 Spreadsheet Component, replace the CLSID in the <object> element with:

0002E559-0000-0000-C000-000000000046

  1. Using any text or html editor, create a Web page named C:\Mytest.htm that contains the following code:
    <html>
<body>
<object classid="clsid:0002E510-0000-0000-C000-000000000046" id="Spreadsheet1">     
</object>
<input type=button value="button" name=button1>
<script language=javascript for="button1" event="onclick">
   var z = new Array(3);
   z[0]="aaa";
   //z[1]="bbb";
   z[2]="ccc";
   Spreadsheet1.Range("a1:c1").Value=z; 
</script>
</body>
</html>
  2. Start Internet Explorer and browse to C:\MyTest.htm.
  3. Click the button on the Web page.
The string "aaa" is in cell A1, "ccc" is in cell B1, and the number "3" (the dimension of the array) is in cell C1.

Sample Workaround Using SafeArray

Replace the code in your MyTest.htm file with the following: 
<html>
<body>
<object classid="clsid:0002E510-0000-0000-C000-000000000046" id="Spreadsheet1">
</object>
<input type=button value="button" name=button1>
<script language="VBScript">
   Function CreateNamesArray()
     ' Create an array to set multiple values at once.
     Dim saNames(2)
     saNames(0) = "aaa"
     saNames(2) = "ccc"
     CreateNamesArray = saNames
   End Function
</script>
<script language=javascript for="button1" event="onclick">
   Spreadsheet1.Range("a1:c1").Value = CreateNamesArray()
</script>
</body>
</html>
Test the modified Web page in the browser. This time, "aaa" is in cell A1, cell B1 is empty, and "ccc" is in cell C1.

REFERENCES

Using Office Web Components
http://support.microsoft.com/ofd

Microsoft Office Developer Center
http://msdn.microsoft.com/office

(c) Microsoft Corporation 2001, All Rights Reserved. Contributions by Chris Jensen, Microsoft Corporation.

Modification Type:MinorLast Reviewed:8/23/2005
Keywords:kbOfficeWebSpread kbprb KB286315
©2004 Microsoft Corporation. All rights reserved.