Author: Anonymous

Office Web Components (OWC) – Part V Spreadsheet control as datasource to Chart Control

Posted on by Anonymous

Chart control – Using the Spreadsheet control as data source

Speaking about the Spreadsheet control, I received an e-mail from an alias (?) asking how to customize the column- and rowheadings. Below is a snipped code that shows how to do it:

Dim Spread As OWC11.Spreadsheet
Dim stYears(0 To 2) As String
Dim iCounter As Integer

stYears(0) = “1996”
stYears(1) = “1997”
stYears(2) = “1998”

Set Spread = Me.Spreadsheet1

With Spread
    .Height = 3540
    .Width = 4695
    With .ActiveWindow
        ‘Set the caption of the actual columnheadings.
        .ColumnHeadings(1).Caption = “Country    “
        .ColumnHeadings(2).Caption = “Freight    “
        .ColumnHeadings(3).Caption = “Shipments  “
        ‘Set the caption of the actual rowheadings.
        For iCounter = 0 To 2
            .RowHeadings(iCounter + 1).Caption = stYears(iCounter)
            .RowHeadings(iCounter + 4).Caption = stYears(iCounter)
        Next iCounter
        ‘Limit the viewable part of the worksheet.
        .ViewableRange = “A1:D6”
    End With
End With

Set Spread = Nothing

The below image shows how the control looks like after executing the code:

Spread1

In the following example itís assumed that the Spreadsheet control is populated with a Recordset containing quarterly data for two countries. In addition itís a request to show the maximum / minimum amount of freight values as well as the number of shipments.

The following image show how the form is set up in the first place:

Spread2

The formulas have been created at design time and the formulas are the following:

Max: =MAX(C2:C9)
Min: =MIN(C2:C9)

When clicking on the ìView chartî ñ button the form expand and show the Chart control as the following image shows:

Spread3

Whenever the underlying data is updated in the Spreadsheet control itís automatically reflected in the Chart control.

The following code does all the hard work to set up the Chart space and the two charts:

Private Sub cmdBView_Click()
‘Ranges with data for the charts.
Const stData1 As String = “Datasource!A2:D5”
Const stData2 As String = “Datasource!A6:D9”

Dim owcSpread As OWC11.Spreadsheet
Dim owcChart As OWC11.ChartSpace
Dim stTitle1 As String, stTitle2 As String
Dim stLegend1 As String, stLegend2 As String

With Me
    .Width = 9315
    Set owcSpread = .Spreadsheet1
    Set owcChart = .ChartSpace1
End With

With owcSpread.ActiveSheet
    stTitle1 = .Range(“B2”).Value
    stTitle2 = .Range(“B6”).Value
    stLegend1 = .Range(“C1”).Value
    stLegend2 = .Range(“D1”).Value
End With
     
With owcChart
    .Clear
    ‘Set up the Spreadsheet control to be the data source.
    .DataSource = owcSpread
    ‘Remark: We are forced to add both the 1st and 2nd chart.
    ‘Add the first chart.
    .Charts.Add
    ‘Add the second chart.
    .Charts.Add
    ‘If we want to use the same scale for the Y-axis for all the charts then
    ‘we need to set this property to true.
    .HasUnifiedScales = True
    .Border.Color = vbWhite
    ‘By changing the layout we can control how the charts are presented
    ‘inside the Chart space.
    .ChartLayout = chChartLayoutHorizontal
End With

‘Set up the charts and manipulate some of their properties.
With owcChart.Charts(0)
    .Type = chChartTypeAreaStacked
    ‘The data reference must be of the datatype string.
    ‘The last parameter specify if each row represent a serie or not.
    .SetSpreadsheetData stData1, False
    .HasTitle = True
    With .Title
        .Caption = stTitle1
        .Font.Name = “Verdana”
        .Font.Size = 10
        .Font.Bold = True
        .Font.Color = RGB(0, 51, 153)
    End With
    With .SeriesCollection(1)
        .Interior.Color = vbYellow
        .Caption = stLegend1
    End With
    With .SeriesCollection(2)
        .Interior.Color = vbBlue
        .Caption = stLegend2
    End With
    With .Axes(0).Font
        .Name = “Verdana”
        .Size = 8
        .Bold = True
        .Color = RGB(0, 51, 153)
    End With
    With .Axes(1).Font
        .Name = “Verdana”
        .Size = 8
        .Color = RGB(0, 51, 153)
    End With
    .HasLegend = True
    With .Legend
        .Position = chLegendPositionBottom
        .Border.Color = vbWhite
        .LegendEntries(2).Visible = False
    End With
End With

With owcChart.Charts(1)
    .Type = chChartTypeAreaStacked
    .SetSpreadsheetData stData2, False
    With .SeriesCollection(1)
        .Interior.Color = vbRed
        .Caption = stLegend1
    End With
    With .SeriesCollection(2)
        .Interior.Color = vbBlue
        .Caption = stLegend2
    End With
    .HasTitle = True
    With .Title
        .Caption = stTitle2
        .Font.Name = “Verdana”
        .Font.Size = 10
        .Font.Bold = True
        .Font.Color = RGB(0, 51, 153)
    End With
    With .Axes(0).Font
        .Name = “Verdana”
        .Size = 8
        .Bold = True
        .Color = RGB(0, 51, 153)
    End With
    With .Axes(1).Font
        .Name = “Verdana”
        .Size = 8
        .Color = RGB(0, 51, 153)
    End With
    .HasLegend = True
    With .Legend
        .Position = chLegendPositionBottom
        .Border.Color = vbWhite
        .LegendEntries(2).Visible = False
    End With
End With

Set owcSpread = Nothing
Set owcChart = Nothing
   
End Sub

To sum up
For the last three years I have shipped over 20 customs solution that, in one or another way, include one or more of the controls in the OWC. Iíve mainly used them in COM add-ins for Excel or as part of VB 6.0-solutions but due to NDAs Iím not able to show any of these solutions in public. Anyway, up to this date no issues have been reported to me due to the use of these controls and therefore I find them to be trusted.

This post ends the introduction of the OWC package and although I have only skimmed on the surface I hope that you at least now have an idea about them.

Kind regards,
Dennis

Office Web Components (OWC) – Part IV Introduction Chart control

Posted on by Anonymous

The Chart control

The control is actually a chart space in which one or more charts can reside in. The chart space is also the top object in the control’s object model and like the other controls in the OWC package some properties can only be manipulated through the chart space.

In addition to databases, text-/XML-files both the Spreadsheet control and the Pivottable control in the OWC package can be used as data sources for the Chart control.

One of the Chart control’s more powerful abilities is that charts can either be of the standard type of chart or genuine pivot charts.

If we want to display several charts in the same chart space we need to consider some limitations of the chart space such as:
* The same data source must be used.
* The same categories names on the x-axis and the same values on the y-axis must be used.
* Not all types of chart can are supported.

As for the chart object in the control it’s very like the sibling in Excel but it has a more complex object model which can be difficult to grasp (at least for those of us who are not chart-specialist per se). On the other hand we don’t need to manipulate so many properties and methods of the chart object in order to get a functional solution. The number of supported types of charts exceeds 60 which should be sufficient for most cases.

The example below use a rowset based XML-file as a data source and creating an XML-file can be done in several ways. One of the more simple ways to generate an XML-file with Excel is as the following example shows:

Option Explicit

Sub Create_XML_File()
‘Early binding – add a reference to the Microsoft ActiveX Data Objects 2.5 Library or later.
Dim rst As ADODB.Recordset
Dim str As ADODB.Stream

Const stCon As String = _
“Provider=SQLOLEDB.1;Integrated Security=SSPI;” & _
“Persist Security Info=False;Initial Catalog=Northwind;” & _
“Data Source=IBM”

Const stSQL As String = _
“SELECT ShipCountry AS Country, SUM(Freight) AS Freight, “ & _
“COUNT(Freight) as Shipments, “ & _
“YEAR(ShippedDate)AS Year “ & _
“FROM Orders “ & _
“WHERE YEAR(ShippedDate) IS NOT NULL “ & _
“GROUP BY ShipCountry,YEAR(ShippedDate);”
 
Set rst = New ADODB.Recordset
Set str = New ADODB.Stream

With rst
    .CursorLocation = adUseClient
    .Open stSQL, stCon, adOpenStatic, adLockReadOnly, adCmdText
    .Save str, adPersistXML
    .Close
End With

With str
    .SaveToFile “C:Report.xml”, adSaveCreateOverWrite
    .Close
End With

Set str = Nothing
Set rst = Nothing
End Sub

The following image shows the output after that the below code has been executed:

OWC Chart1

The default number of charts is one and if we want to have additional chart then we need to add them via code.

The following procedure sets up the chart space and the chart:

Option Explicit

Public Function Set_Up_Chart_Control()
Const stCon As String = “Provider=MSPersist;”

‘The data source must follow the Recordset Persistance schema
‘when using XML-files.
Const stFile As String = “C:Report.xml”

Dim owcChSpace As OWC11.ChartSpace
Set owcChSpace = frmChart.ChartSpace1

‘Setup the Chart Space.
With owcChSpace
    ‘Clear the space.
    .Clear
    .ConnectionString = stCon
    .CommandText = stFile
    .Border.Color = RGB(153, 204, 204)
    .Interior.Color = RGB(153, 204, 204)
    .DisplayToolbar = True
    .DisplayPropertyToolbox = False
    .DisplayFieldList = True
    ‘By setting this property to false  a standard chart
    ‘and not a PivotChart is created.
    .DisplayFieldButtons = True
    ‘Prevent the user(s) from doing anything else except filter the data
    ‘and add / remove fields.
    .AllowUISelection = False
    ‘Populate two dimensions with fields from the data source.
    .SetData chDimCategories, 0, “Country”
    .SetData chDimValues, 0, “Freight”
    ‘If we want to use several fields for data filter etc we can use arrays.
    .SetData chDimFilter, 0, Array(“Year”, “Shipments”)
    With .DropZones(chDropZoneFilter)
        .ButtonInterior.Color = vbWhite
        .ButtonFont.Color = vbBlue
    End With
    With .DropZones(chDropZoneCategories)
        .ButtonInterior.Color = vbWhite
        .ButtonFont.Color = vbBlue
    End With
End With

‘Setup the chart.
With owcChSpace.Charts(0)
    ‘Choose the chart type.
    .Type = chChartTypeAreaStacked
    .HasTitle = True
    With .Title
        .Caption = “Freight per Country”
        .Font.Name = “Verdana”
        .Font.Size = 10
        .Font.Bold = True
        .Font.Color = RGB(0, 51, 153)
    End With
    .Interior.SetTwoColorGradient chGradientHorizontal, _
                                  chGradientVariantCenter, _
                                  RGB(255, 255, 204), _
                                  RGB(204, 204, 204)
    ‘Set up the Value-axis.
    With .Axes(1)
        .HasTitle = True
        With .Title
            .Caption = “US$”
            .Font.Name = “Verdana”
            .Font.Bold = True
            .Font.Size = 8
            .Font.Color = RGB(0, 51, 153)
        End With
    End With
    .HasLegend = False
End With

Set owcChSpace = Nothing

End Function

The Chart control can’t export the underlying data to Excel (or to any other program) but it can create an image of the viewed chart, in GIF, JPEG or in the PING format. The following snippet code shows how this can be achieved:

Dim stFileName As String
Dim owcChSpace As OWC11.ChartSpace

Set owcChSpace = Me.ChartSpace1

‘Create the filename.
stFileName = _
Replace(CStr(Date), “-“, “”) & _
Replace(CStr(Time), “:”, “”) & “.gif”

With owcChSpace
    ‘Temporarily hide the toolbar.
    .DisplayToolbar = False
    ‘Export to a standalone GIF-file.
    .ExportPicture _
    FileName:=“c:DDE” & stFileName, _
    FilterName:=“gif”, _
    Width:=700, _
    Height:=440
    ‘Display the toolbar again.
    .DisplayToolbar = True
End With

MsgBox “Done!”, vbOKOnly, “OWC Chart”

Set owcChSpace = Nothing

In the last post about the OWC I will show how we can use the Spreadsheet control as a data sources for the Chart control.

Kind regards,
Dennis

Office Web Components (OWC) – Part III Spreadsheet control

Posted on by Anonymous

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:

Spreadsheetcontrol

Option Explicit

Function Set_Up_Worksheet()
Const stCon As String = _
“Provider=SQLOLEDB.1;Integrated Security=SSPI;” & _
“Persist Security Info=False;Initial Catalog=Northwind;” & _
“Data Source=IBM”
                       
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.
With cnt
    .CursorLocation = adUseClient
    .Open stCon
    Set rst = .Execute(stSQL)
    Set rst.ActiveConnection = Nothing
    .Close
End With

‘Setup the Spreadsheet control.
With owcSpread
    .AllowPropertyToolbox = False
    .DisplayOfficeLogo = False
    .DisplayPropertyToolbox = False
    .DisplayTitleBar = False
    .DisplayToolbar = False
    With .ActiveWindow
        .DisplayGridlines = False
        .DisplayHorizontalScrollBar = False
        .DisplayColumnHeadings = False
        .DisplayRowHeadings = False
        .DisplayWorkbookTabs = False
        .EnableResize = False
    End With
End With

With owcWSheet
    ‘Populate the worksheet with the recordset.
    .Cells.CopyFromRecordset rst
    ‘Turn on the AutoFilter for the range.
    .UsedRange.AutoFilter
    ‘Set the variable to the worksheet AutoFilter which is not equal
    ‘to the Range AutoFilter.
    Set owcAutoFilter = .AutoFilter
    ‘Create the filters.
    With owcAutoFilter
        ‘Criteria for Year, i e exclude 1996 records.
       ‘Excluded is the default setting for Criteria.
        .Filters(1).Criteria.Add “1996”
        ‘Criterias for Shipping Country,
        ‘i e include only records from France and Germany.
        With .Filters(2).Criteria
            .FilterFunction = ssFilterFunctionInclude
            .Add “France”
            .Add “Germany”
        End With
        ‘Apply the filters.
        .Apply
    End With
    ‘Sort the filtered list.
    With .UsedRange
        For iCounter = 4 To 1 Step -1
            .Sort ColumnKey:=iCounter, Order:=xlAscending, Header:=xlYes
        Next iCounter
    End With
    ‘Adjust the columns.
    .Cells.Columns.AutoFit
   
    ‘Disallow the users to manipulate the data and the worksheet.
    With .Protection
        .AllowDeletingColumns = False
        .AllowDeletingRows = False
        .AllowInsertingColumns = False
        .AllowInsertingRows = False
        .AllowSorting = False
        .Enabled = True
    End With
End With

With owcSpread.ActiveWindow
    ‘The range that have been populated with data which is
    ‘also visible for the users.
    frmData.lblViewable.Caption = .ViewableRange
End With
   
‘Release objects from memory.
rst.Close
Set rst = Nothing
Set cnt = Nothing
Set owcAutoFilter = Nothing
Set owcWSheet = Nothing
Set owcWbook = Nothing
Set owcSpread = Nothing

End Function

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:

Dim ptCommand As OWC11.OCCommand
Set ptCommand = owcSpread.Commands(plCommandExport)
‘Execute the Export to Excel command.
ptCommand.Execute

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.

Kind regards,
Dennis

Office Web Components (OWC) – Part II – Programming the Pivottable

Posted on by Anonymous

Programming the Pivottable control with MS VB 6.0 SP-6

If we intend to use an ADO-classic approach to populate the control with a Recordset then one way is to use a disconnected Recordset like the following snippet code example shows:

With cnt
    .CursorLocation = adUseClient
    .Open stCon
    Set rst = .Execute(stSQL)
    Set rst.ActiveConnection = Nothing
    .Close
End With

ptTable.DataSource = rst

For demonstration purpose the example use the “OLAP-approach” which means that the control is directly connected to the source during the session.

Option Explicit

Function Setup_PivotTable()
Const stCon As String = _
“Provider=SQLOLEDB.1;Integrated Security=SSPI;” & _
“Persist Security Info=False;Initial Catalog=Northwind;” & _
“Data Source=IBM”
                       
Const stSQL As String = _
“SELECT ShipCountry, Freight, ShipVia, “ & _
“ShippedDate AS Year “ & _
“FROM Orders;”

Dim ptTable As OWC11.PivotTable
Dim ptView As OWC11.PivotView
Dim ptFsets As OWC11.PivotFieldSets
Dim ptField As OWC11.PivotField
Dim ptTotal As OWC11.PivotTotal
Dim ptCommand As OWC11.OCCommand
Set ptTable = frmOWCPT.PivotTable1

With ptTable
    ‘Establish the connection to the data source etc.
    .ConnectionString = stCon
    .CommandText = stSQL
    ‘In case no data is returned we can catch it.
    If .ActiveData Is Nothing Then
        ‘Message…
        ‘End the procedure…
    End If
    Set ptView = .ActiveView
End With

Set ptFsets = ptView.FieldSets
 
With ptView
    ‘Add the Year by Week-field to the Filter area.
    .FilterAxis.InsertFieldSet ptFsets(“Year by Week”)
    ‘Exclude records with empty years.   ‘In case of multilanguage enviroment then
   ‘the Array ‘should be applied then any local word for ‘Empty’.
    .FieldSets(“Year by Week”).Fields(0).ExcludedMembers = Array(“”)
    ‘Add the Year by Month-field to the Filter area.
    .FilterAxis.InsertFieldSet ptFsets(“Year by Month”)
    .FieldSets(“Year by Month”).Fields(0).ExcludedMembers = Array(“”)
   
    ‘Add the ShipCountry-field to the Row area.
    .RowAxis.InsertFieldSet ptFsets(“ShipCountry”)
    .FieldSets(“ShipCountry”).Fields(0).Caption = “Shipping Country”
   
    ‘Add the Shipvia-field to the Column area.
    .ColumnAxis.InsertFieldSet ptFsets(“ShipVia”)
    .FieldSets(“ShipVia”).Fields(0).Caption = “Agency”
   
    ‘Add the Freight-field to the Data area.
    .DataAxis.InsertFieldSet ptFsets(“Freight”)
   
    ‘Add Total calculated field.
    .DataAxis.InsertTotal ptView.AddTotal(“No of Shipments”, _
    ptFsets(“Freight”).Fields(0), _
    plFunctionCount)
   
    .DataAxis.InsertTotal ptView.AddTotal(“Share of Shipments”, _
    ptFsets(“Freight”).Fields(0), _
    plFunctionCount)
   
    ‘Show the calculated field as percentage of the row
    ‘and hide the field from being displayed in the fieldlist.
    With .Totals(“Share of Shipments”)
        .ShowAs = plShowAsPercentOfRowTotal
        .DisplayInFieldList = False
    End With
       
    .DataAxis.InsertTotal ptView.AddTotal(“Total Freight”, _
    ptFsets(“Freight”).Fields(0), _
    plFunctionSum)
   
    .DataAxis.InsertTotal ptView.AddTotal(“Share of Freight”, _
    ptFsets(“Freight”).Fields(0), _
    plFunctionSum)
   
    With .Totals(“Share of Freight”)
        .ShowAs = plShowAsPercentOfRowTotal
        .DisplayInFieldList = False
    End With
   
    .DataAxis.InsertTotal ptView.AddTotal(“Average Freight”, _
    ptFsets(“Freight”).Fields(0), _
    plFunctionAverage)
         
    ‘Format fields.
    .FieldSets(“Freight”).Fields(0).NumberFormat = “0”
    .Totals(“Total Freight”).NumberFormat = “0”
    .Totals(“Average Freight”).NumberFormat = “0”
       
    ‘View Total captions as Rowheadings instead as Columnheadings (default).
    .TotalOrientation = plTotalOrientationRow
   
    With .TitleBar
        .Caption = “A simple sample Pivottable report”
        .BackColor = RGB(0, 102, 0)
        .ForeColor = vbWhite
    End With
       
    ‘Disallow users from editing the data.
    .AllowEdits = False
End With

With ptTable
    ‘Uncheck the option to show the Drop Areas.
    ‘No property of the Pivottable is accessible to show/hide it via code.
    ‘At least I’m not aware of any.
    Set ptCommand = .Commands(plCommandDropzones)
    ptCommand.Execute
    ‘Collapse the Pivottable so that only summary data is shown.
    .ActiveData.HideDetails
    .Refresh
    ‘If necessary, show the Fieldlist.
    .DisplayFieldList = True
    ‘Fix the Pivottable’s size in the form.
    ‘The Autofit property is automatically set to False when we set the
    ‘height and width properties of the control.
    With .Object
        .Height = 460
        .Width = 710
    End With
    .BackColor = RGB(204, 255, 255)
End With
 
‘Release objects from memory.
Set ptCommand = Nothing
Set ptFsets = Nothing
Set ptView = Nothing
Set ptTable = Nothing

End Function

When populating the control with data via code no Totals are automatically added and therefore we need to do it with code as well.

The following picture shows the initial status after that the above code has been executed:

Pivottable1

By resetting the orientation of the Totals captions to default following view is obtained:

Pivottable2

By drilling down the underlying data is viewed which may give a better understanding of pattern etc:

Drilling down1

If we use any date related field the control will per automatic generate two sets of hierarchies for each date field. One hierarchy (Year by Month) contains the grouping of Year, Quarter, Month and Day and the other (Year by Week) contains the interval Year, Week and Day. The basic explanation for the existence of two hierarchies is that weeks do not always follow the months. In the example one date field (as Filter field) is used and therefore the control generates the following hierarchies.

Year by Week and Year by Month (Days are not viewed in the picture):

Yearweek YearMonth

In the example the Toolbar of the control is available and one of the coolest features of the control is the ability to export to Excel. By clicking on the Excel-button the active view is exported to Excel and a built-in Pivottable is created with the data.

The Pivottable control places a high demand on developers but in return we get a flexible and a very powerful control which may leverage any solutions for business analysis and reporting.

In the next two coming parts the Spreadsheet control will be introduce.
Kind regards,
Dennis

Office Web Components (OWC) – Part II – Introduction Pivottable

Posted on by Anonymous

The Pivottable control

I thought I could cover most things about the control in one post but I realized it requires two posts instead and here comes the first one.

The control explicit target to view data in a way that support decision making and let the users view the data enabling them to “holistic look thru” the data.

The name of the control associate it strongly to the built-in Pivottable in Excel and on its face it’s very similar to the Pivottable in Excel however there exist some difference between them, which this and next part hopefully can point out.

The control is explicit designed to work with OLAP (Online Analytical Processing) data sources, which can be best described as multidimensional databases and extracts from databases (cubes). The structure is a N-matrix and each dimension consist of members (items) and the intersection of N members holds the value. OLAP is the best and fastest way to work with large amount of multidimensional records and implicit it also point out one of the strongest abilities of the control, the drilling down capacity.

The control can also work with tabular data sources, such as less complex databases, Excel worksheets, textfiles and row formatted XML-files.

It’s beyond the scoop of this post to describe OLAP in detail as it can be both very abstract and very complex. To fully understand OLAP it will require a book on the subject and lot of practice and therefore the following refer to tabular data source.

The terminology associated with the control is different compared with the built-in in Excel. In order to work with it programmatically and with tabular data sources we need at least a basic understanding of the terms in use and what they actually refer to. The following picture shows the keywords in use and what part of the control they refer to.

OWC Pivottable 1

Like the built-in Pivottable the control also have a viewable field list enable the users to drag and drop fields. The symbol with 0’s and 1’s represent calculated fields.

OWC Pivottable 2

Connecting data sources to the control can be done in two ways, as with most data controls, either via a property page or via code at runtime. Personally I prefer to do it via code as it gives me more control and make it easier to change.

The following picture shows where we can setup the connection etc via the property page of the control:

OWC Pivottable 3

The code-example in the next part will show how we do it programmatically.

Unlike the built-in sibling in Excel the control does not rely on a pivot-cache. The control also uses ADO to load the retrieved data into the component Windows Cursor Engine (WCE) which is part of the Microsoft Data Access Components (MDAC) and it use its own memory cache to hold the data (and if necessary, due to the amount of returned data, pages it to disk).

After the data has been loaded into the cache the control then can communicate with it to view data, filter data and drilling down.

Kind regards,
Dennis

Office Web Components (OWC) – Part I

Posted on by Anonymous

What are OWC?
For a week ago I asked Dick if some post concerning the Office Web Components (OWC) would be of general interest for the visitors of the Daily Dose of Excel. The answer was clear so here is the first of several posts about OWC.

Per se itís a library that includes a set of ActiveX controls that are powerful but are also fairly complex.

The library contains the following controls:
* The Microsoft Pivottable control
* The Microsoft Excel Spreadsheet control
* The Microsoft Excel Charting engine
* The Microsoft Office Data Source control
* The Microsoft Record Navigation control (Included in the latest available version 11.0)

The controls target various purposes but the primarily area is for reporting and analysis of structured and already created data. They look, feel and behave like smaller versions of their siblings in Microsoft Office but they both differ and can be more powerful, especially the Pivottable control. The programming object models for the OWC are both similar and at the same time differ a lot, which may cause some confusing.

OWC were created to fill the need to present data from databases and OLAP for the Internet / Intranet. But since they are standard ActiveX controls they also can be used in forms with any developing platforms that support COM like MS Visual Basic 6.0 / VB.NET and VBA.

Controls are usually divided into two groups, the group that are visible and used in control containers like forms and the group that can only be in memory as invisible controls. Compared with other ActiveX controls OWC have one major advantage, they can be used either as visible controls or in memory as invisible controls. The later ability gives the option to create server-generated static content in a fast way.

The primary ways for connecting to data sources and to retrieve data, for present available versions, is with ADO. Since the Pivottable, Spreadsheet and Chart controls have a built-in data source control itís not necessary to explicit use the Data Source Control.

If we intend to use one or more of these controls with Excel the best solution is to create COM add-ins in VB. The reason for it is that later versions of Excel show a security message whenever the forms are initialized: ìThe application is about to initialize ActiveX controls that might be unsafeÖî and the smoothest way to solve it is simple to use COM add-ins.

Since the controls have no technology dependency with MS Office they can coexist with both earlier as well as later versions of MS Office. Per se they can be used without having any Office version installed at all. However, the coming parts will show how we can interact between these controls and Excel.

OWC was first released when the version 2000 of MS Office was shipped and since then MSFT have released a new version of OWC when new Office versions have been released.

A general recommendation is to always make sure to use the latest available version of OWC as it still is under developing, i e bugfixes, new properties / methods are added etc.

When the first version (9.0) was released there were a lot of legal questions and restrictions for using them. Later versions (10.0 and 11.0) have different and freer terms.
For more information please see Office Web Component (OWC.) licensing

In addition, the first version was quite buggy and the documentation can to some extend being considered to be very confusing.

All in all the combination probably lead to create a badwill for OWC among developers and perhaps also explain why OWC is still an extremely low profiled MSFT-product.

If you want to use the latest available version of OWC, that is 11.0, it can be downloaded from the following URL: Office Web Components

Otherwise itís available on the MS Office CD.

In the next coming part the Pivottable control will be in focus and it will show how we programmatically can control and manipulate the control with MS VB 6.0.

Kind regards,
Dennis

Create Pivottable-reports with VB.NET

Posted on by Anonymous

I thought it would be of general interest to see how we can create reports based on Pivottable(s) by automating Excel from VB.NET 2003.

Depending on what the purpose is with a report we can control what the end-users can do via the creation of the SQL-query and how we setup of the Pivotable(s).

In the example early binding is in use and the following namespaces must be imported to the project:

Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices

Maincode:

  Const stCon As String = _
 “ODBC;DSN=MS Access Database;” & _
 “DBQ=C:Northwind.mdb;DefaultDir=C:;” & _
 “DriverId=25;FIL=MS Access;” & _
 “MaxBufferSize=2048;PageTimeout=5;”
 
 Const stSQL As String = _
 “SELECT ShipCountry, “ & _
 “COUNT(Freight) AS [# Of Shipments], “ & _
 “SUM(Freight) AS [Total Freight] “ & _
 “FROM Orders “ & _
 “GROUP BY ShipCountry;”
 
Dim xlApp As Excel.Application
 
Try
     ‘Grab a running instance of Excel.
      xlApp = Marshal.GetActiveObject(“Excel.Application”)
Catch ex As COMException
      ‘If no instance exist then create a new one.
      xlApp = New Excel.Application
End Try
 
Dim xlWBook As Excel.Workbook = xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet)
Dim xlWSheet As Excel.Worksheet = CType(xlWBook.Worksheets(1), Excel.Worksheet)
Dim xlRange As Excel.Range = CType(xlWSheet, Excel.Worksheet).Range(“B2”)
 
‘Create the Pivotcache.
Dim ptCache As Excel.PivotCache = xlWBook.PivotCaches.Add( _
SourceType:=Excel.XlPivotTableSourceType.xlExternal)
 
‘Setup the Pivotcache.
With ptCache
.Connection = stCon
.CommandText = stSQL
.CommandType = Excel.XlCmdType.xlCmdSql
End With
 
‘Create the Pivottable.
Dim ptTable As Excel.PivotTable = _
xlWSheet.PivotTables.Add( _
PivotCache:=ptCache, _
TableDestination:=xlRange, _
TableName:=“PT_Report”)
 
‘Setup the Pivottable.
With ptTable
.ManualUpdate = True
.PivotFields(“ShipCountry”).Orientation = Excel.XlPivotFieldOrientation.xlRowField
.PivotFields(“# Of Shipments”).Orientation = Excel.XlPivotFieldOrientation.xlDataField
.PivotFields(“Total Freight”).Orientation = Excel.XlPivotFieldOrientation.xlDataField
.Format(Excel.XlPivotFormatType.xlReport2)
.ManualUpdate = False
End With
 
 xlWBook.SaveAs(“c:Report.xls”)
 
‘Switch to Excel.
With xlApp
.Visible = True
.UserControl = True
End With
 
‘Tell the Garbage Collector that these objects are ready to be destroyed.
ptTable = Nothing
ptCache = Nothing
xlWSheet = Nothing
xlWBook = Nothing
xlApp = Nothing

Personally I use more and more VB.NET to create “reporttools” instead of VB 6.0 although I still find it hard to switch between VBA/VB and VB.NET.

Kind regards,
Dennis

Textfiles – Part 1: Create Mdbs on the fly

Posted on by Anonymous

Itís common within large companies that end users are not allowed to access central databases and instead all data are received in large textfiles. Itís also usual that end users donít have MS Access available and that all users use the same Office-version.
Instead of import all data directly to a workbook a better option can be to create a Mdb (Microsoft Database) on the fly, import data from the text file(s) to it and aggregate the data before importing it to Excel.

References must be set to Microsoft ActiveX Data 2.5 Objects Library and Microsoft ADO Ext. 2.5 for DDL and Security.

Option Explicit
 
Const stPath As String = “c:DDE”
Const stDBase As String = “Source.mdb”
 
‘In order to create a mdb in Access 97 format add the line to the connection string:
‘”Jet OLEDB:Engine Type=4;”
‘The default is Access 2000 format (Type=5).
Const stCon As String = _
“Provider=Microsoft.Jet.OLEDB.4.0;” & _
“Data Source=” & stPath & stDBase & “;”
 
‘The SQL-query to import data from the textfile.
Const stSQLAdd As String =  _
“INSERT INTO tblReportData “ & _
“SELECT * “ & _
“FROM [Text;DATABASE=” & stPath & “].[Data.txt];”
 
‘The SQL-query to import selected summarized data from the mdb-file.
Const stSQLSelect As String = _
“SELECT Dept, Quarter, SUM(Amount) “ & _
“FROM tblReportData “ & _
“GROUP BY Dept, Quarter;”
 
Sub Create_MDB_On_The_Fly()
Dim xCat As ADOX.Catalog, xTable As ADOX.Table, xCol As ADOX.Column
Dim cnt As ADODB.Connection, rst As ADODB.Recordset
Dim wsSheet As Worksheet
 
‘Delete the present mdb.
On Error Resume Next
Kill stPath & stDBase
On Error GoTo 0
 
‘Instantiate the objects.
Set wsSheet = ActiveSheet
Set xCat = New ADOX.Catalog
Set xTable = New ADOX.Table
 
‘Create the new mdb.
xCat.Create stCon
 
‘Add columns to the table and make sure that they accept null values.
With xTable
    ‘Name the table.
    .Name = “tblReportData”
    .Columns.Append “Dept”
    .Columns.Append “Quarter”
    .Columns.Append “Amount”, adInteger
    ‘Provide access to provider specific properties.
    .ParentCatalog = xCat
    For Each xCol In .Columns
        .Columns(xCol.Name).Properties(“Nullable”).Value = True
    Next xCol
End With
   
‘Add the table to the mdb.
xCat.Tables.Append xTable
 
‘You will be surprised when You see the outcome of the following.
Debug.Print xCat.ActiveConnection
 
‘Associate the variable to the already open connection.
Set cnt = xCat.ActiveConnection
 
With cnt
    ‘Import the data from the text file.
    .Execute (stSQLAdd)
    ‘Retrieve a recordset from the mdb.
    Set rst = .Execute(stSQLSelect)
End With
 
If Not rst.BOF Or rst.EOF Then
    Application.ScreenUpdating = False
    ‘Add fieldnames and dump the retrieved recordset into the active sheet.
    With wsSheet
        With .Range(“A1:C1”)
            .Value = VBA.Array(“Dept”, “Quarter”, “Total amount”)
            .Font.Bold = True
            .EntireColumn.AutoFit
        End With
        .Range(“A2”).CopyFromRecordset rst
    End With
    Application.ScreenUpdating = True
End If
 
Set rst = Nothing: Set cnt = Nothing
Set xCol = Nothing: Set xTable = Nothing: Set xCat = Nothing
End Sub

Although the emergency development sets focus on XML-files we still work with textfiles and Part II will be cover some issues when trying to access textfiles as ADO-datasources.