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.

Generate Excel Pivot Reports Using VB 6.0

Assuming that there is a command button named cmdGenerate to generate the report,the code for this follows.

References

MICROSOFT ACTIVEX DATA OBJECTS LIBRARY(ANY VERSION ABOVE 2.0)

Private Sub cmdGenerate_Click()
On Error GoTo Err_optDet

   Dim sSQL            As String
   Dim sPageLevelPivot As String
   Dim sColLevelPivot  As String
   Dim sRowLevelPivot  As String
   Dim sDataPivot      As String
   Dim ExistFile
   Dim rstemp As ADODB.Recordset

   Screen.MousePointer = vbHourglass

   'CONSTRUCT YOUR SQL QUERY HERE
   'sSQL = SQL QUERY

   'CREATE THE RECORDSET rstemp HERE
   '(BEFORE THAT CREATE THE CONNECTION TO THE DATA SOURCE)
   '******************************************
'   Set rstemp = New ADODB.Recordset
'   With rstemp
'      .CursorType       = adOpenStatic
'      .CursorLocation   = adUseClient
'      .LockType         = adLockReadOnly
'      .ActiveConnection = Active connection to the data source
'      .Open
'   End With


   'DISCONNECTING FROM THE DATA SOURCE
   Set rstemp.ActiveConnection = Nothing

   'DELETING THE PREVIOUS REPORT IF IT EXISTS
   ExistFile = Dir("C:\Report.xls")
   If ExistFile <> "" Then Kill ("C:\Report.xls")

   'THIS IS WHERE THE FLEXIBILITY COMES IN, PASS THE PARAMETERS AS
   'FIELD INDEXES OF THE RECORDSET FOR THE GROUPING

   sPageLevelPivot = "0"
   sColLevelPivot  = "1"
   'sRowLevelPivot = "2345"    'any combination can be passed
   sRowLevelPivot  = "45"
   sDataPivot      = "6"
   Call GenerateReport(rstemp, sPageLevelPivot, sColLevelPivot, _
                       sRowLevelPivot, sDataPivot, "C:\Report.xls")
   Screen.MousePointer = 0
   Exit_optDet:

   Set rstemp = Nothing
   Exit Sub

Err_optDet:
   Screen.MousePointer = 0
   'VARIOUS ERROR HANDLERS
   Resume Exit_optDet

End Sub

Although it is not present in the code, if required, the pivot report (worksheet "Pivot" in the general procedure) can be locked for most users, so that they cannot change the layout and can be unlocked for users who want to do further analysis by using the Pivot wizard.

I have not written any code for the formatting, but can be incorporated according to the cosmetic requirements.



About the Author

Ramdeep Bhattacharya

A Microsoft Certified Professional working with TCS for the past 5 years with experience in systems analysis, components design, development and testing of client server, Intranet/Internet, N-tier systems. Has developed applications using technologies like VB6.0, ASP, COM, MTS,XML, SQL Server 7.0/2000, MQ Series and MS-Access. MICROSOFT CERTIFICATIONS : Microsoft Certified Professional in Visual Basic 6.0 (Distributed Application); Microsoft Certified Professional in Developing and Implementing Databases with SQL Server 2000

Downloads

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: April 22, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT Database professionals — whether developers or DBAs — can often save valuable time by learning to get the most from their new or existing productivity tools. Whether you're responsible for managing database projects, performing database health checks and reporting, analyzing code, or measuring software engineering metrics, it's likely you're not taking advantage of some of the lesser-known features of Toad from Dell. Attend this live …

  • With JRebel, developers get to see their code changes immediately, fine-tune their code with incremental changes, debug, explore and deploy their code with ease (both locally and remotely), and ultimately spend more time coding instead of waiting for the dreaded application redeploy to finish. Every time a developer tests a code change it takes minutes to build and deploy the application. JRebel keeps the app server running at all times, so testing is instantaneous and interactive.

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds