Microsoft KB Archive/286315

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

Article ID: 286315

Article Last Modified on 2/22/2007

-

APPLIES TO


 * Microsoft Office Web Components
 * Microsoft Office Chart Component 9.0
 * Microsoft Office 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.)



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 element with:

0002E551-0000-0000-C000-000000000046

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

0002E559-0000-0000-C000-000000000046

  Using any text or html editor, create a Web page named C:\Mytest.htm that contains the following code:    var z = new Array(3); z[0]=&quot;aaa&quot;; //z[1]=&quot;bbb&quot;; z[2]=&quot;ccc&quot;; Spreadsheet1.Range(&quot;a1:c1&quot;).Value=z;  Start Internet Explorer and browse to C:\MyTest.htm. Click the button on the Web page.

The string &quot;aaa&quot; is in cell A1, &quot;ccc&quot; is in cell B1, and the number &quot;3&quot; (the dimension of the array) is in cell C1.

Sample Workaround Using SafeArray
Replace the code in your MyTest.htm file with the following:  <input type=button value=&quot;button&quot; name=button1> <script language=&quot;VBScript&quot;> Function CreateNamesArray ' Create an array to set multiple values at once. Dim saNames(2) saNames(0) = &quot;aaa&quot; saNames(2) = &quot;ccc&quot; CreateNamesArray = saNames End Function <script language=javascript for=&quot;button1&quot; event=&quot;onclick&quot;> Spreadsheet1.Range(&quot;a1:c1&quot;).Value = CreateNamesArray Test the modified Web page in the browser. This time, &quot;aaa&quot; is in cell A1, cell B1 is empty, and &quot;ccc&quot; is in cell C1.

<div class="references_section">