Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    VBA Pivot Table? (2003)

    With the help of the fine folks here in the lounge, specifically Hans, I had created two that were well received. I am now tasked with creating 7 pivot tables in the same Excel file using data in Access using the same approach (i.e. automating with VBA).

    To avoid asking several questions over several attempts to create them, I would prefer to have a better understanding of the syntax. I purchased the "Mr. Excel" book "Pivot Table Data Crunching". It is a good book, but unfortunately, there is only one section on VBA automation, and not enough detail for me to get a good understanding. Especially since the VBA in the book assumes you are running the code in Excel, and not Access.

    Below is one function I used to create one of the previous pivot tables.

    My questions are:
    1. I want the pivot tables to use the same source data, how do I save memory using the same source data on multiple pivot tables?
    2. How do I control the name of the newly created sheet that contains the pivot table?
    3. Where do I "assign" the name of the destination sheet for each pivot table?
    4. What does the following do?
    xlWrkbk.ActiveSheet.PivotTables("PT4FutureUse").Pi votFields("MCL"). _
    Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
    False, False)


    ' ================================================== =========================
    Function FormatPivot2(strFileName As String, strSheetName As String)
    Dim xlWrkbk As Excel.Workbook
    Dim xlChartObj As Excel.Chart
    Dim xlSourceRange As Excel.Range
    Dim xlColPoint As Excel.Point
    Dim xlApp As Excel.Application
    Dim strPTName As String

    '===========================================
    ' Added per Pivot Table Book
    Dim WSD As Worksheet
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim PRange As Range
    Dim FinalRow As Long
    Dim FinalCol As Long

    '===========================================

    strPTName = "PT4FutureUse"

    On Error GoTo Err_FormatPivot2

    ' Create an Excel workbook file based on the
    ' object specified in the second argument.

    ' Create a Microsoft Excel object. THIS OPENS AN INSTANCE OF EXCEL
    Set xlApp = CreateObject("Excel.Application")

    ' Open the spreadsheet to that has the exported the data.
    Set xlWrkbk = xlApp.Workbooks.Open(strFileName)


    ' Determine the size of the range and store it.
    Set xlSourceRange = xlWrkbk.Worksheets(strSheetName).Range("a1").Curre ntRegion

    xlWrkbk.Worksheets(1).Cells.Select

    ' Create the Pivot table in this section
    Set WSD = xlWrkbk.Worksheets("sqCore")

    ' Clears any pivot tables in the spreadsheet's memory
    For Each PT In WSD.PivotTables
    PT.TableRange2.Clear
    Next PT

    FinalRow = WSD.Cells(WSD.Rows.Count, 1).End(xlUp).Row
    FinalCol = WSD.Cells(1, WSD.Columns.Count).End(xlToLeft).Column
    Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)

    Set PTCache = xlWrkbk.PivotCaches.Add(xlDatabase, PRange)
    Set PT = PTCache.CreatePivotTable("", strPTName, , xlPivotTableVersion10)

    'Turn Off updating while building the table
    PT.ManualUpdate = True

    'Set up the row and column fields
    PT.AddFields RowFields:=Array("Months", "LP", "LDFactor"), _
    ColumnFields:="MCL"

    'Set up the data fields
    With PT.PivotFields("Claim Number")
    .Orientation = xlDataField
    .Function = xlCount
    .Position = 1
    End With
    ' Other datafields can be added by using above as model
    'With PT.PivotFields("OtherField")
    ' .Orientation = xlDataField
    ' .Function = xlCount ' Can count or sum etc.
    ' .Position = x ' Give it position preferred
    'End With

    ' NOT SURE WHAT THIS DOES
    xlWrkbk.ActiveSheet.PivotTables("PT4FutureUse").Pi votFields("MCL"). _
    Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
    False, False)

    ' calc the pivot table
    PT.Update

    'format the pivot table
    Me.txtCorePivotStatus = "Formatting Pivot Table."
    Me.Repaint

    PT.TableStyle = "PivotStyleMedium10"
    xlApp.Columns("A:H").Select
    xlApp.Selection.ColumnWidth = 12.29
    xlApp.Rows("3:4").Select
    With xlApp.Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With


    Exit_FormatPivot2:

    Set xlSourceRange = Nothing
    Set xlColPoint = Nothing
    Set xlChartObj = Nothing

    ' Close and save the workbook
    xlWrkbk.Close SaveChanges:=True
    Set xlWrkbk = Nothing

    ' Quit Excel
    xlApp.Quit
    Set xlApp = Nothing
    Exit Function

    Err_FormatPivot2:
    MsgBox CStr(Err) & " " & Err.Description
    Resume Exit_FormatPivot2

    End Function
    ' ================================================== =========================


    If I can get to the point of understanding and seeing the "big" picture, I will be less likely to have ask what appear to be the same type of question repetitively and hopefully share what I learn with others.

    Thanks in advance for any ideas. If anyone knows of a better source than Woody's to get the answers, please point me in the right direction.

    Ken

  2. #2
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: VBA Pivot Table? (2003)

    Hans,

    The reason I posted it in Access is all the code is run from VBA in Access, and the data is in Access. I don't think the sample code will run in Excel, but I am not sure.

    Thanks,

    Ken

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Pivot Table? (2003)

    The code may be run from Access, but it has nothing to do with Access. Your question is about worksheets and pivot tables, so it is about Excel VBA, *not* about Access VBA.

  4. #4
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: VBA Pivot Table? (2003)

    Hans,
    That makes sense. I just have not worked in Excel before. Sorry.

    Ken

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Pivot Table? (2003)

    1) You already have the "magic" code:

    Set PTCache = xlWrkbk.PivotCaches.Add(xlDatabase, PRange)
    Set PT = PTCache.CreatePivotTable("", strPTName, , xlPivotTableVersion10)

    A pivot cache represents the source data for one or more pivot tables. You can create multiple pivot tables from the same pivot cache using the CreatePivotTable method of the pivot cache.

    2) and 3) seem to be the same question. The first argument of CreatePivotTable is the target cell. You can create a worksheet first, then specify a cell on this sheet:

    Dim wsh As Excel.Worksheet
    Set wsh = xlWrkbk.Worksheets.Add
    wsh.Name = "MySheet"
    Set PT = PTCache.CreatePivotTable(wsh.Range("A1"), strPTName, , xlPivotTableVersion10)

    4) The SubTotals property specifies which types of subtotals are displayed for the field. There are 12 possible types: Automatic, Sum, Count, ..., Varp. The argument to SubTotals is an array of 12 True/False values. In your example, all values are False, meaning that subtotals are turned off.
    Type SubTotals in a module or in the Immediate window and press F1 to get more detailed help.

  6. #6
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: VBA Pivot Table? (2003)

    Hans,

    Thanks. I'll try incorporating your recommendations and see what shakes out. This is not intuitive to me, and one reason I am always impressed with your clean and "simple" approach. I seem to make things more complicated than they need to be until I truly grasp a concept.

    Thanks again!

    Ken

Posting Permissions

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