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.