HOWTO: Use the Spreadsheet Web Component with Visual Basic

ID: Q235883

The information in this article applies to:

Microsoft Office 2000
Microsoft Office Spreadsheet Component 9.0
Microsoft Visual Basic Professional and Enterprise Editions for Windows, version 6.0

SUMMARY

If you want to use a spreadsheet or calculation engine in your custom Visual Basic solution, you can use the Spreadsheet control included with the Microsoft Office Web Components. This article discusses how to insert a spreadsheet as a control on a Visual Basic form and populate the spreadsheet with data.

MORE INFORMATION

You can add the Office Web Components to a form in Visual Basic in the same way that you add any other ActiveX control. The Spreadsheet control included with the Office Web Components is a more robust option than the DataGrid control that ships with Visual Basic. Use the spreadsheet data to display formatted data, calculate formulas and allow user interactivity.

IMPORTANT: To view and work with any of the Office Web Components, you must have them installed on your computer. The components are installed with Microsoft Office 2000, or, if your company has an Office 2000 site license, the components can be downloaded from your Intranet. Your users must have these components installed as well, and must be covered by an Office 2000 license. You and your users must use Microsoft Internet Explorer version 4.01 or later.

Steps to Create a Sample Project

Create a new Standard EXE project in Visual Basic. Form1 is created by default.

Add a reference to Microsoft Office Web Components 9.0.

Add the Spreadsheet control from the Visual Basic toolbox to your form.

The Spreadsheet control can be displayed and used like a worksheet, or it can be hidden and used as a calculation engine for visible controls on the page.

Working with the Spreadsheet control in Visual Basic is almost identical to working with a worksheet in Excel. The methods and properties you use to manipulate the Spreadsheet component are similar to those you would use when automating Excel.

Populating a Spreadsheet Control

You can add data to a Spreadsheet control in one of the following ways:

Enter data directly into the grid.

Copy and paste data from an Excel sheet, text file, or Word document.

Import data from a text file or a Web page. For more information see the LoadText Method topic and the HTMLURL Property topic in online Help.

Write code to populate the spreadsheet with data.

Note that there is no way to bind the Spreadsheet control to data directly.

The following procedure populates and formats a Spreadsheet control with data from the sample database Northwind (NWind.mdb):


Sub GetNwindData()

    Dim rstEmployees As Object
    Dim cnn As String
    Dim strSQL As String
    Dim fldCount As Integer
    Dim intIRow As Integer
    Dim intICol As Integer
    Dim varData As Variant

    'Create recordset and fill with records from Northwind sample database.
    Set rstEmployees = CreateObject("ADODB.Recordset")
    cnn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=C:\Program Files\Microsoft Visual Studio\VB98\nwind.mdb"
    strSQL = "SELECT FirstName, LastName, Title, Extension FROM Employees ORDER BY LastName"
    rstEmployees.Open strSQL, cnn, 3 ' adOpenStatic = 3
    
    'Clear any existing values from the spreadsheet control.
    Spreadsheet1.ActiveSheet.Cells(1, 1).Select
    Spreadsheet1.ActiveSheet.UsedRange.Clear

    'Add the field names as column headers.
    For fldCount = 0 To rstEmployees.Fields.Count - 1
        intIRow = intIRow + 1
        Spreadsheet1.ActiveSheet.Cells(1, intIRow).Value = rstEmployees.Fields(fldCount).Name
    Next

    'Fill the control with data from the database.
    Dim iNumCols As Integer
    Dim iNumRows As Integer
    
    iNumCols = rstEmployees.Fields.Count
    iNumRows = rstEmployees.Recordcount
    varData = rstEmployees.GetRows(iNumRows)
    
    For intIRow = 1 To iNumRows
        For intICol = 1 To iNumCols
            Spreadsheet1.ActiveSheet.Cells(intIRow + 1, intICol).Value = varData(intICol - 1, intIRow - 1)
        Next
    Next

    'Format the headers in row 1 with a Bold Font that is 11 points.
    With Spreadsheet1.Range(Spreadsheet1.Cells(1, 1), Spreadsheet1.Cells(1, iNumCols)).Font
        .Bold = True
        .Size = 11
    End With
    
    'AutoFit the columns and make all text left-aligned.
    With Spreadsheet1.Range(Spreadsheet1.Cells(1, 1), Spreadsheet1.Cells(iNumRows + 1, iNumCols))
        .AutoFitColumns
        .HAlignment = ssHAlignLeft
    End With

End Sub

Note that a Spreadsheet control is not a Workbook and contains only a single sheet. The ActiveSheet property always refers to that single sheet.

Getting Help

After you create a reference to the Office Web Components library, you can access Help through the object browser or from the General tab of the Spreadsheet Property Toolbox. You can also locate and double-click the Help file Msowcvba.chm. To get help on a specific keyword, highlight the keyword and press the F1 key. For information about creating references and using object model Help, see Help in Visual Basic or Visual Basic for Applications.

You can bind a Chart control to a Spreadsheet control. For additional information on the Chart control, please click the article number below to view the article in the Microsoft Knowledge Base:

Q235885 HOWTO: Use the Chart Web Component With VB

REFERENCES

For more information about Office Web Components, see Chapter 12, "Using Web Technologies," in the Microsoft Office 2000/Visual Basic Programmer's Guide.

Additional query words:

Keywords : kbVBp600 kbOffice2000 kbDSupport kbOfficeWebSpread
Version : WINDOWS:2000,6.0
Platform : WINDOWS
Issue type : kbhowto