Generate Excel Pivot Reports Using VB 6.0

MS Excel offers highly flexible reporting capabilities in the form of Pivot tables. This article shows how a general procedure can be written in VB to generate reports as Excel pivot tables using COM. The data is fetched from a SQL Server 2000 database using ADO.

This procedure can be used as a reusable component to cater to Excel reporting requirements in VB projects, which will offer great flexibility in grouping and summarising data by any report field.

First, I write the code for the general procedure. The parameters that the procedure expects are as follows:

• Report Data: This is the data from the database or can be data from any source on which reporting is to be done. The data in this snippet is in the form of a disconnected ADODB recordset. The query used to generate the data need not use a group by clause, rollup, or cube operators because the grouping and summarising will be taken care of by the pivot table itself.
• The fields in the above recordset that are to be used as Page Level grouping, Row level grouping, Col Level grouping, and the Data.

Note: The actual data behind the report is hidden from the user, so the data underlying the pivot table can be, by no means, modified.

I will now show how the procedure can be invoked and also attach a sample Excel report that can be generated.

The Code for the General Subroutine

References

The sample code needs two references:

• MICROSOFT ACTIVEX DATA OBJECTS LIBRARY(ANY VERSION ABOVE 2.0)
• MICROSOFT EXCEL 9.0 OBJECT LIBRARY

Note: The MS Excel version used for this code snippet is 2000.

Public Sub GenerateReport(prmTemp As ADODB.Recordset, _
                          prmPage As String, prmCol  As String, _
                          prmRow  As String, prmData As String, _
                          prmFile As String)
On Error GoTo Err_GenerateReport
   ‘VARIABLE DECLARATION
   Dim xlApp    As Excel.Application
   Dim xlBook   As Excel.Workbook
   Dim xlSheet  As Excel.Worksheet
   Dim xlSheet1 As Excel.Worksheet
   Dim rstemp   As ADODB.Recordset
   Dim intX     As Integer
   Dim intY     As Integer
   Set xlApp    = New Excel.Application
   Set xlBook   = xlApp.Workbooks.Add
   Set xlSheet  = xlBook.Worksheets.Add
   xlSheet.Name = “Pivot”
   Set rstemp = prmTemp
   ‘DUMP THE RECORDSET TO EXCEL
   For intY = 0 To rstemp.Fields.Count – 1
      xlSheet.Cells(intX + 1, intY + 1).Value = _
         rstemp.Fields(intY).Name
   Next intY
   intX = intX + 1
   While Not rstemp.EOF
      For intY = 0 To rstemp.Fields.Count – 1
         xlSheet.Cells(intX + 1, intY + 1).Value = _
            rstemp.Fields(intY).Value
      Next intY
      rstemp.MoveNext
      intX = intX + 1
   Wend
   ‘DATA DUMPED, SO WE HAVE THE DATA ON WHICH TO PIVOT
   ‘ADDING A NEW WORKSHEET FOR THE PIVOT TABLE
   Set xlSheet1  = xlBook.Worksheets.Add
   xlSheet1.Name = “Report”
   ‘CREATING THE PIVOT TABLE
   xlBook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
      “Pivot!R1C1:R” & rstemp.RecordCount + 1 _
      & “C” & rstemp.Fields.Count).CreatePivotTable _
      TableDestination:=xlSheet1.Range(“A9″), _
      TableName:=”PivotTable1”
   xlSheet1.PivotTables(“PivotTable1”).SmallGrid = False
   ‘SETTING THE PAGE LEVEL FIELDS FROM THE PARAMETERS PASSED
   If Len(prmPage) > 0 Then
      For intX = 1 To Len(prmPage)
         With xlSheet1.PivotTables(“PivotTable1”).PivotFields( _
            rstemp.Fields(CInt(Mid$(prmPage, intX, 1))).Name)
            .Orientation = xlPageField
            .Position = intX
         End With
      Next intX
   End If
   ‘SETTING THE COL LEVEL FIELDS FROM THE PARAMETERS PASSED
   If Len(prmCol) > 0 Then
      For intX = 1 To Len(prmCol)
         With xlSheet1.PivotTables(“PivotTable1”).PivotFields( _
            rstemp.Fields(CInt(Mid$(prmCol, intX, 1))).Name)
            .Orientation = xlColumnField
            .Position = intX
         End With
      Next intX
   End If
   ‘SETTING THE ROW LEVEL FIELDS FROM THE PARAMETERS PASSED
   If Len(prmRow) > 0 Then
      For intX = 1 To Len(prmRow)
         With xlSheet1.PivotTables(“PivotTable1”).PivotFields( _
            rstemp.Fields(CInt(Mid$(prmRow, intX, 1))).Name)
            .Orientation = xlRowField
            .Position = intX
         End With
      Next intX
   End If
   ‘SETTING THE DATA FIELDS FROM THE PARAMETERS PASSED
   If Len(prmData) > 0 Then
      For intX = 1 To Len(prmData)
         With xlSheet1.PivotTables(“PivotTable1”).PivotFields( _
            rstemp.Fields(CInt(Mid$(prmData, intX, 1))).Name)
            .Orientation = xlDataField
            .Position = 1
         End With
      Next intX
   End If
   ‘HIDING THE PIVOTTABLE COMMANDBAR
   xlApp.CommandBars(“PivotTable”).Visible = False
   xlSheet1.Cells.EntireColumn.AutoFit
   xlSheet1.Range(“A1”).Select
   xlApp.DisplayAlerts = False
   ‘DELETING THE SHEET WITH THE SOURCE DATA – SO THAT NO ONE
   ‘CAN MODIFY
   xlSheet.Delete
   xlApp.DisplayAlerts = True
   xlSheet1.Range(“A1”).Select
   ‘SAVING THE EXCEL SHEET
   xlBook.SaveAs prmFile
   xlApp.Visible = True
Exit_GenerateReport:
   ‘xlBook.Close
   Set xlApp    = Nothing
   Set xlBook   = Nothing
   Set xlSheet  = Nothing
   Set xlSheet1 = Nothing
   Set rstemp   = Nothing
   Exit Sub
Err_GenerateReport:
   xlBook.Close
   Set xlApp    = Nothing
   Set xlBook   = Nothing
   Set xlSheet  = Nothing
   Set xlSheet1 = Nothing
   Set rstemp   = Nothing
   Err.Raise vbObjectError + 1500, “modReport.GenerateReport”, _
      Err.Description
End Sub

Now I will show how the above procedure can be invoked.