| Microsoft Office 2000/Visual Basic Programmer's Guide | |
Whether you are using VBA or VBScript code, working with the Spreadsheet control's object model is surprisingly similar to working with a worksheet in Excel. In fact, since both object models support an ActiveSheet property (the Spreadsheet control can contain only a single worksheet), you may find that some code written to work with Excel's Worksheet object (and its related objects, properties, and methods) will work exactly the same way when it is run against a Spreadsheet control. For example, Figure 12.7 shows a Web page (ConvertToScript.htm in the ODETools\V9\Samples\OPG\Samples\CH12 subfolder on the Office 2000 Developer CD-ROM) that was created from an Excel worksheet that originally contained VBA code.
Figure 12.7 An HTML Version of an Excel Worksheet That Used VBA Code
The ConvertToScript.htm sample file was created to show how easy it is to convert Excel VBA code that inserts data into a worksheet to VBScript code that inserts the same data into a Spreadsheet control on a Web page. This sample file was created by using the Publish as Web Page dialog box to publish the Employees worksheet in the ExcelSamples.xls file (located in the ODETools\V9\Samples\OPG\Samples\CH05 subfolder on the Office 2000 Developer CD-ROM) as the ConvertToScript.htm file. The two CommandButton controls on the worksheet were replaced with HTML intrinsic command button controls. The VBA code was copied from the Excel VBA project and pasted into the Web page in the command buttons' onclick event procedures. The VBA code did have to be modified somewhat to account for the different way that VBScript handles some of the language elements, but it is essentially the same.
The following code samples illustrate the similarity between the VBA code and the VBScript code used to make these samples work. The first code sample is VBA code from the GetEmployeeData procedure in ExcelSamples.xls. The second code sample is VBScript code from the cmdUpdate button's onclick event procedure. You can use the comments in the example to compare the VBA code to the VBScript code required to do the same task.
' VBA Code Sample
' The wksEmployees variable is a VBA object variable that points
' to a worksheet in Excel's current workbook.
' Create Recordset object and fill it with
' records from the Northwind sample database.
strSQL = "SELECT FirstName, LastName, Title, Extension " _
& "FROM Employees ORDER BY LastName"
With rstEmployees
.Open strSQL, CONN_STRING & DB_PATH, adOpenKeyset, adLockOptimistic
' Remove the existing values from the control.
wksEmployees.UsedRange.Clear
' Add the field names as column headers.
For Each fldCurrent In .Fields
intIRow = intIRow + 1
wksEmployees.Cells(1, intIRow).Value = fldCurrent.Name
Next fldCurrent
' Fill the control with data from the database.
varData = .GetRows(.RecordCount)
For intIRow = 1 To .RecordCount
For intICol = 0 To UBound(varData)
wksEmployees.Cells(intIRow + 1, intICol + 1).Value _
= varData(intICol, intIRow - 1)
Next intICol
Next intIRow
End With
' VBScript Code Sample
' The Spreadsheet1 object is the ID of the Spreadsheet control on this page.
' You specify the name of an object in script by using the value of the
' ID attribute of the control you want to work with.
' Create Recordset object and fill it with
' records from the Northwind sample database.
Set rstEmployees = CreateObject("ADODB.Recordset")
strSQL = "SELECT FirstName, LastName, Title, Extension "
strSQL = strSQL & "FROM Employees ORDER BY LastName"
rstEmployees.Open strSQL, cnnConnection, 3, 1
' Remove the existing values from the control.
Spreadsheet1.ActiveSheet.Cells(1,1).Select
Spreadsheet1.ActiveSheet.UsedRange.Clear
' Add the field names as column headers.
For fldCurrent = 0 to rstEmployees.Fields.Count - 1
intIRow = intIRow + 1
Spreadsheet1.ActiveSheet.Cells(1, intIRow).Value = _
rstEmployees.Fields(fldCurrent).Name
Next
' Fill the control with data from the database.
varData = rstEmployees.GetRows(rstEmployees.RecordCount)
For intIRow = 1 To rstEmployees.RecordCount
For intICol = 0 To UBound(varData)
Spreadsheet1.ActiveSheet.Cells(intIRow + 1, intICol + 1).Value = _
varData(intICol, intIRow - 1)
Next
Next
As you can see from the preceding example, you use script to work with individual cells in the Spreadsheet control in exactly the same way that you work with cells by using VBA code in Microsoft Excel.
For complete documentation of the Spreadsheet control's object model, see the Msowcvba.chm Help file, which is located in the C:\Program Files\Microsoft Office\Office\1033 subfolder.
Note The path to the Msowcvba.chm Help file reflects the language ID folder (1033) for U.S. English language support in Office. The language ID folder below C:\Program Files\Microsoft Office\Office differs for each language.