Find Code:
All Words
Any of the Words
Exact Phrase
Home
:
Code
:
Forums
:
Submit
:
Mailing List
:
About
:
Contact
Code
All
VB.NET
ASP.NET
C#
VB Classic
ASP Classic
Snippets
Popular
Resources
Submit Code
Forums
Articles
Tips
Links
Books
Contest
Link to us
Generate Excel Pivot Reports using VB
Author:
Ramdeep Bhattacharya
E-mail:
Click to e-mail author
Submitted:
9/25/2004
Version:
VB6
Compatibility:
VB6
Category:
ActiveX
Downloads:
11759
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 for Excel reporting requirements in VB projects, which will offer great flexibility in grouping and summarising data by any report field. I am attaching the code for the general procedure. The parameters which the procedure expects are: 1>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 diconnnected ADODB recordset.)The query used to generate the data need not use group by clause , rollup or cube operators as the grouping and summarising will be taken care by the pivot table itself. 2>The fields in the above recordset which 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 then show how the procedure can be invoked and also attach a sample Excel report which can be generated. References The sample code needs 2 references: 1> MICROSOFT ACTIVEX DATA OBJECTS LIBRARY(ANY VERSION ABOVE 2.0) 2> MICROSOFT EXCEL 9.0 OBJECT LIBRARY Note: The version of MS Excel used for this code snippet is 2000. Though not present in the code but 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 would wish to do further analysis by using the Pivot wizard.
Home
|
Forums
|
Submit
|
Books
|
Mailing List
|
Advertising
|
About
|
Contact
© 2024 A1VBCode. All rights reserved.
Legal disclaimer & terms of use
Privacy statement