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.

More by Author

Must Read