Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 1 of 1

Thread: Creating PivotTable-reports from VB 6.0

  1. #1
    Join Date
    25th January 2003
    Location
    Ístersund, Sweden
    Posts
    2,451

    Creating PivotTable-reports from VB 6.0

    Hi all,

    Creating reports in Excel is a common task and also to retrieve data from databases.

    For several reason we may decide to create a standalone reporttool in VB instead of doing it from inside Excel.

    When setting up reports with the Pivottable and particular if we want to have the option to update the table ( i e retrieve updated data from the source) we must be aware of that there exist certain limitations to use a classic ADO-approach and therefore are (still) forced to use the ODBC-approach.

    Please see Populate pivottables using ADO

    In the example early binding is used and therefore it require that we set references to the following library:

    * Microsoft Excel 9.0 or later

    What we need:
    * Microsoft Visual Basic 6.0
    * Microsoft Excel 2000 and later
    * The example database Northwind.mdb


    Code:
    Option Explicit
    
    Dim xlApp As Excel.Application
    Dim xlWbook As Excel.Workbook
    Dim xlWSheet As Excel.Worksheet
    Dim xlptCache As Excel.PivotCache
    Dim xlptTable As Excel.PivotTable
                          
    Const stCon As String = "ODBC;DSN=MS Access Database;" & _
                                     "DBQ=C:\Northwind.mdb;DefaultDir=C:\;" & _
                                     "DriverId=25;FIL=MS Access;" & _
                                     "MaxBufferSize=2048;PageTimeout=5;"
    
    
    Const stSQL As String = "SELECT ShipCountry, " & _
                                      "COUNT(Freight) AS [# Of Shipments], " & _
                                      "SUM(Freight) AS [Total Freight] " & _
                                      "FROM Orders " & _
                                      "GROUP BY ShipCountry;"
    
                           
    Public Function Create_PivotTable_Report(ByVal stFilename As String)
    Dim bStarted As Boolean
            
    'If a session of Excel is already running then grab it.
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    On Error GoTo 0
    
    'Otherwise instantiate a new instance.
    'Keep in mind that no active Add-ins will be available unless they are
    'of the type COM.
    If xlApp Is Nothing Then
        'Set the flag to remember who have started the session.
        bStarted = True
        Set xlApp = New Excel.Application
    End If
    
    'Create a new workbook with one worksheet only.
    Set xlWbook = xlApp.Workbooks.Add(xlWBATWorksheet)
    
    With xlWbook
        Set xlWSheet = .Worksheets(1)
        Set xlptCache = .PivotCaches.Add(SourceType:=xlExternal)
    End With
    
    'Populate the Pivotcache.
    With xlptCache
        .Connection = stCon
        .CommandText = stSQL
        .CommandType = xlCmdSql
    End With
    
    'Create the Pivottable.
    Set xlptTable = xlWSheet.PivotTables.Add( _
                           PivotCache:=xlptCache, _
                           TableDestination:=xlWSheet.Range("D4"), _
                           TableName:="PT_Report")
                            
    'Setup the pivottable.
    With xlptTable
        .ManualUpdate = True
        .PivotFields("ShipCountry").Orientation = xlRowField
        .PivotFields("# Of Shipments").Orientation = xlDataField
        .PivotFields("Total Freight").Orientation = xlDataField
        .Format xlTable2
        .ManualUpdate = False
        .ManualUpdate = True
    End With
    
    'Save the created workbook.
    xlWbook.SaveAs stFilename
    
    'If this procedure have started Excel then the UserControl of Excel must be
    'set to True and we need to make Excel visible.
    If bStarted Then
        With xlApp
            .Visible = True
            .UserControl = True
        End With
    End If
    
    'Switch to Excel.
    AppActivate (xlApp)
    
    'Release the objects from memory.
    Set xlptTable = Nothing
    Set xlptCache = Nothing
    Set xlWSheet = Nothing
    Set xlWbook = Nothing
    Set xlApp = Nothing
    
    End Function
    Last edited by XL-Dennis; June 27th, 2005 at 08:54.
    Kind regards,
    Dennis

    .NET & Excel | 2nd edition PED | MVP

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Training: PivotTable I: Get started with PivotTable reports in Excel 2007
    By Dave Hawley in forum Free Microsoft Excel 2003 Tutorials
    Replies: 0
    Last Post: December 23rd, 2008, 18:17
  2. Most Searched Training: PivotTable I: Get Started with PivotTable reports in Excel 2007
    By Dave Hawley in forum Free Microsoft Excel 2007 Tutorials
    Replies: 0
    Last Post: February 8th, 2008, 11:05
  3. Most Searched for Training: PivotTable I: Get Started with PivotTable reports in Excel 2007
    By Dave Hawley in forum Free Microsoft Excel 2007 Tutorials
    Replies: 0
    Last Post: January 28th, 2008, 11:12

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno