Microsoft KB Archive/286209

= How To Script the Office XP Spreadsheet Component from a Web Page =

Article ID: 286209

Article Last Modified on 8/23/2005

-

APPLIES TO


 * Microsoft Office Web Components

-



This article was previously published under Q286209



SUMMARY
This article provides sample code that illustrates how you can script the Office XP Spreadsheet component from a Web page.



MORE INFORMATION
Working with the Office XP Spreadsheet component is very similar to working with a Microsoft Excel workbook object. The methods and properties you use to manipulate the spreadsheet are similar to those you use when you automate Excel. In fact, one of the new features of the Office XP Spreadsheet component is that it allows for more than one worksheet. There are also additional ways to populate the Spreadsheet component, such as with an XML data source or with a connection string to a database.

Populating the Spreadsheet Component
You can add data to the Spreadsheet component in one of the following ways:
 * Enter data directly into a worksheet.
 * Copy and paste data from an Excel worksheet, text file, or Microsoft Word document.
 * Import data from a text file, Web page, or XML file. These various types of data may be imported by using the Spreadsheet object's CSVURL, HTMLURL, XMLURL, CSVData, HTMLData, and XMLData properties and the LoadText method of the Range object.
 * Use the Value property of the Range object to assign values to specific cells in the spreadsheet.
 * Bind to a data source by using the DataSource and DataMember properties, or by using the ConnectionString and CommandText properties.

The following sample illustrates how you can populate the Spreadsheet component with data and apply various formatting to the cells and the worksheet. The sample populates a worksheet by using the ConnectionString and CommandText properties to bind to data in the Northwind sample database (Northwind.mdb).

Sample
  Paste the following code into Notepad, and then save the code on your local computer as Spreaddemo.htm. Spread Demo 



Sub btnDemo_OnClick Dim oSheet Dim sCnn Dim sSQL Dim iNumCols Dim iNumRows Dim oTbarButton

Dim c   Set c = oSS.Constants ' Add a new worksheet. Set oSheet = oSS.Worksheets.Add(1)

' Delete all other worksheets. Do While oSS.Worksheets.Count > 1 oSS.Worksheets(2).Delete Loop

' Name the new worksheet. oSheet.Name = &quot;Employees&quot; ' Fill the Employees sheet with data from Northwind sample database. sCnn = &quot;provider=microsoft.jet.oledb.4.0; data source=&quot; & _ &quot;C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb&quot; sSQL = &quot;SELECT Employees.EmployeeID, First(Employees.FirstName) AS FirstOfFirstName, &quot; & _ &quot;First(Employees.LastName) AS FirstOfLastName, &quot; & _ &quot;Count(Orders.OrderID) AS CountOfOrderID, &quot; & _ &quot;Sum(CCur([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100) AS Total &quot; & _ &quot;FROM (Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID) &quot; & _ &quot;INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID &quot; & _ &quot;GROUP BY Employees.EmployeeID;&quot; oSheet.ConnectionString = sCnn oSheet.CommandText = sSQL ' Determine the number of rows and columns. iNumCols = oSheet.UsedRange.Columns.Count iNumRows = oSheet.UsedRange.Rows.Count ' Change field names as desired. oSheet.Cells(1, 2).Value = &quot;First Name&quot; oSheet.Cells(1, 3).Value = &quot;Last Name&quot; oSheet.Cells(1, 4).Value = &quot;Orders&quot; oSheet.Cells(1, 5).Value = &quot;Total&quot; ' Format field names. With oSheet.Range(oSheet.Cells(1, 1), oSheet.Cells(1, iNumCols)) .Font.Bold = True .Font.Size = 11 .Interior.Color = &quot;Silver&quot; .Borders(c.xlEdgeBottom).Weight = c.xlThick End With ' Add subtotals for Orders and Total fields. oSheet.Cells(iNumRows + 2, 4).Formula = &quot;=SUBTOTAL(9,D2:D&quot; & iNumRows & &quot;)&quot; oSheet.Cells(iNumRows + 2, 5).Formula = &quot;=SUBTOTAL(9,E2:E&quot; & iNumRows & &quot;)&quot; ' Format columns. oSheet.Cells(1, 1).EntireColumn.HorizontalAlignment = c.xlHAlignLeft oSheet.Cells(1, 4).EntireColumn.NumberFormat = &quot;#,##0&quot; oSheet.Cells(1, 5).EntireColumn.NumberFormat = &quot;Currency&quot; oSheet.UsedRange.EntireColumn.AutoFit ' Sort by Total (column 5). With oSheet .Range(.Cells(1, 1), .Cells(iNumRows, iNumCols)).Sort _ 5, c.xlDescending, c.xlYes End With

' Hide the title bar. oSS.DisplayTitleBar = False

' Hide the Microsoft Office logo on the toolbar. oSS.DisplayOfficeLogo = False ' Display the Toolbar. oSS.DisplayToolbar = True ' Remove the &quot;Refresh All&quot; and &quot;Help&quot; button from the toolbar. On Error Resume Next Set oTbarButton = oSS.Toolbar.Buttons(&quot;owc10061&quot;) 'Refresh All button If Not Err.Number Then oSS.Toolbar.Buttons.Remove oTbarButton.Index - 1 oSS.Toolbar.Buttons.Remove oTbarButton.Index End If   Err.Clear Set oTbarButton = oSS.Toolbar.Buttons(&quot;owc1006&quot;) 'Help button If Not Err.Number Then oSS.Toolbar.Buttons.Remove oTbarButton.Index - 1 oSS.Toolbar.Buttons.Remove oTbarButton.Index End If   On Error GoTo 0 ' Apply window settings. oSS.Windows(1).ViewableRange = oSheet.UsedRange.Address oSS.Windows(1).DisplayRowHeadings = False oSS.Windows(1).DisplayColumnHeadings = False oSS.Windows(1).DisplayVerticalScrollBar = False oSS.Windows(1).DisplayHorizontalScrollBar = False

'Autofit the component on the Web page. oSS.AutoFit = True End Sub  Make sure the path to Northwind.mdb is correctly specified in the sCnn variable. Start Microsoft Internet Explorer and browse to Spreaddemo.htm. Click the button to fill the Spreadsheet component with data and to apply formatting.

Additional Notes
When you bind the Spreadsheet component to a data source, the spreadsheet's ViewableRange is automatically set to the size of the returned data. ViewableRange updates as the data source is refreshed. Note that at the end of the above code, the ViewableRange property was set to the UsedRange to ensure that the subtotals below the bound data are viewable.

Conceptually, the contents of a data-bound worksheet are owned by the Spreadsheet component. This means that any user edits to the worksheet are lost during the next data refresh without warning. Similarly, when binding data to a worksheet that already contains data, the existing data may be replaced when the data from the bound data source is retrieved.

To programmatically cause a data refresh, call the Refresh method of the Worksheet object, or of the Application object to refresh all worksheets.

<div class="references_section">