The Spreadsheet Control
The control is on its face identical to the worksheet object in Excel and the programming object model shows the same similarity. However the name is (to som extend) misleading as it’s a container for workbooks and the workbooks are containers of worksheets. The control can only handle one open workbook at the time. Unfortunately it can’t handle Excel’s files.
Some information about the control:
Each worksheet can contain up to 702 columns and 262144 rows.
If the control is populated with data from a data source then only the columns and rows in use are viewed.
Can only support one active worksheet, which limit the possibility to have interaction between
worksheets through functions and references. We can’t make a lookup in one worksheet and place the result
in another worksheet through formulas.
Support nearly all functions that Excel provides including functions in Analysis Toolpak.
Support relative and absolute cell references as well as range names.
Does not support Data Validation.
Support autofiltering and sorting although on a primitive level.
Have the same worksheet’s events as its sibling and several others events like StartEdit, EndEdit and
ViewChange. In total it has over 40 events.
Allow us to create customized column headings.
Has its own built-in source control.
Lack the possibility to print from the control (as with the other OWC).
If we plan to create a model that involves functions / formulas including a less sophisticated design then it’s highly recommended to do all the work in Excel and then simple copy and paste it into the control at design time as the control does not provide any user friendly interface.
The below code example shows how we can manipulate central properties of the control, populate the control with data from a Recordset, and apply an autofilter. When the form is loaded we get the following output:
Const stCon As String = _
“Provider=SQLOLEDB.1;Integrated Security=SSPI;” & _
“Persist Security Info=False;Initial Catalog=Northwind;” & _
Const stSQL As String = _
“SELECT Year(ShippedDate) AS Period, “ & _
“ShipCountry AS Country, “ & _
“ShipCity AS City, “ & _
“Freight “ & _
“FROM Orders “ & _
“WHERE Year(ShippedDate)IS NOT NULL;”
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim owcSpread As OWC11.Spreadsheet
Dim owcWbook As OWC11.Workbook
Dim owcWSheet As OWC11.Worksheet
Dim owcAutoFilter As OWC11.AutoFilter
Dim iCounter As Integer
Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset
Set owcSpread = frmData.Spreadsheet1
Set owcWbook = owcSpread.ActiveWorkbook
Set owcWSheet = owcWbook.ActiveSheet
‘Get the recordset.
.CursorLocation = adUseClient
Set rst = .Execute(stSQL)
Set rst.ActiveConnection = Nothing
‘Setup the Spreadsheet control.
.AllowPropertyToolbox = False
.DisplayOfficeLogo = False
.DisplayPropertyToolbox = False
.DisplayTitleBar = False
.DisplayToolbar = False
.DisplayGridlines = False
.DisplayHorizontalScrollBar = False
.DisplayColumnHeadings = False
.DisplayRowHeadings = False
.DisplayWorkbookTabs = False
.EnableResize = False
‘Populate the worksheet with the recordset.
‘Turn on the AutoFilter for the range.
‘Set the variable to the worksheet AutoFilter which is not equal
‘to the Range AutoFilter.
Set owcAutoFilter = .AutoFilter
‘Create the filters.
‘Criteria for Year, i e exclude 1996 records.
‘Excluded is the default setting for Criteria.
‘Criterias for Shipping Country,
‘i e include only records from France and Germany.
.FilterFunction = ssFilterFunctionInclude
‘Apply the filters.
‘Sort the filtered list.
For iCounter = 4 To 1 Step -1
.Sort ColumnKey:=iCounter, Order:=xlAscending, Header:=xlYes
‘Adjust the columns.
‘Disallow the users to manipulate the data and the worksheet.
.AllowDeletingColumns = False
.AllowDeletingRows = False
.AllowInsertingColumns = False
.AllowInsertingRows = False
.AllowSorting = False
.Enabled = True
‘The range that have been populated with data which is
‘also visible for the users.
frmData.lblViewable.Caption = .ViewableRange
‘Release objects from memory.
Set rst = Nothing
Set cnt = Nothing
Set owcAutoFilter = Nothing
Set owcWSheet = Nothing
Set owcWbook = Nothing
Set owcSpread = Nothing
The freeze Panes is set manually as it seems to be impossible (at least for me) to do it via code, i e freeze the first row. Other then that it works as expected via code.
To export the filtered data to Excel we use the command plCommandExport as the following snippet code shows:
Set ptCommand = owcSpread.Commands(plCommandExport)
‘Execute the Export to Excel command.
Please note that all the settings are also exported, including any worksheet protection.
The control has a property named Dirty that is intended to control if data have been changed or not. In theory it’s a nice feature however due to a bug it does not work. For a workaround please see the following article at my English site: Use the OWC Spreadsheet in MS VB 6.0
In the next part the OWC Chart control will be presented.