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

    Pivot Table via VBA (2003)

    It is GREAT to have the Lounge open again!

    I have two existing "challenges".

    A little background first. I have successfully created an Excel spreadsheet from Access. Then via code, I have attempted to create a Pivot table. The code seems to work, in that it does create a pivot table with row and column headings, but no data. The first help I need is to find out why Excel is still running after completing the function, and second, why there is no data in the pivot table. I can quit Excel via Windows Task Manager, then open the spreadsheet and a new sheet has been added and the pivot table is there without data. I can then select the properties of the column field, set it to "Count" and voila', the pivot table looks GREAT.

    The following is a copy of the offending code:

    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.
    Set xlApp = CreateObject("Excel.Application")

    ' Open the spreadsheet to where data was exported.
    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 = Worksheets("sqCore")

    For Each PT In WSD.PivotTables
    PT.TableRange2.Clear
    Next PT

    FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row
    FinalCol = WSD.Cells(1, Columns.Count).End(xlToLeft).Column
    Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
    Set PTCache = ActiveWorkbook.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", "Legacy Program", "Loss Development Factor"), _
    ColumnFields:="Major Coverage Line"

    'Set up the data fields
    With PT.PivotFields("Months")
    .Orientation = xlDataField
    .Function = xlCount
    .Position = 1
    End With

    ' calc the pivot table
    PT.ManualUpdate = False
    PT.ManualUpdate = True

    'format the pivot table
    PT.TableStyle = "PivotStyleMedium10"

    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
    MsgBox "Done"
    Exit Function

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

    End Function

    Any help is greatly appreciated.

    Ken

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

    Re: Pivot Table via VBA (2003)

    When using Automation, i.e. controlling one application from another in VBA, you must take great care to qualify all objects you use as belonging directly or indirectly to the Application object you create, in your case xlApp. If you don't, you may create a second instance of the application that will remain in memory after you end your Application object.

    In your code, the first culprit is the line:

    Set WSD = Worksheets("sqCore")

    Since you don't specify what Worksheets belongs to, it spawns a new instance of Excel. You should change the line to

    Set WSD = xlWrkbk.Worksheets("sqCore")

    Other problem lines are

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

    Change them to

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

    And

    Set PTCache = ActiveWorkbook.PivotCaches.Add(xlDatabase, PRange)

    should be

    Set PTCache = xlWrkbk.PivotCaches.Add(xlDatabase, PRange)

    Does this solve your problems?

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

    Re: Pivot Table via VBA (2003)

    Hans,

    Thanks!

    That fixed the Excel not closing. However, I still get a pivot table with row and column heading but no totals. Any ideas?

    thanks,

    Ken

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

    Re: Pivot Table via VBA (2003)

    I'll look into it later, if nobody else replies.

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

    Re: Pivot Table via VBA (2003)

    Hans,

    I think I found the culprit. It helps if you (or should I say "I") use the right names for the right columns.

    'Set up the data fields
    With PT.PivotFields("Months")

    Should have been:

    'Set up the data fields
    With PT.PivotFields("Major Coverage Line")

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

    Re: Pivot Table via VBA (2003)

    Hans,

    I spoke too soon. Making the change in the field name "'Months" did not resolve the problem. I still end up with the same results.

    Whenever you have the time, feel free to play with the code.

    thanks,
    Ken

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

    Re: Pivot Table via VBA (2003)

    Edited by HansV to replace zip file that contained an RTF document that contained a picture with the picture itself.

    Hans,

    thank you so much. I appreciate your time. For your convenience I have enclosed a picture of the results when I open the spreadsheet in Excel after running the code. It is probably something stupid and simple. Thank you for your patience with my ignorance.

    Ken
    Attached Images Attached Images
    • File Type: png x.png (48.0 KB, 0 views)

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

    Re: Pivot Table via VBA (2003)

    You need to update the pivot table. Merely setting updating to automatic then to manual again doesn't help. Replace the lines

    PT.ManualUpdate = False
    PT.ManualUpdate = True

    with

    PT.Update

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

    Re: Pivot Table via VBA (2003)

    Thank you, Hans! You continue to amaze me with your wealth of knowledge. Where do you find these things? I purchased several books in the past week seeking solutions, and yet you were able to resolve in a few spare minutes between all the other things that you do!

    Thanks!

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

    Re: Pivot Table via VBA (2003)

    Trial and error, mostly <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    I created a very small sample workbook and ran your code. I noticed that the pivot table did have a data field but it wasn't displayed. Refreshing the pivot table using the ! button on the toolbar made it visible. So the pivot table clearly only needed to be updated...

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

    Re: Pivot Table via VBA (2003)

    Hans,

    Thank you so much. In the interim, I made a couple of minor modifications and now Excel is not closing again. Can you, at a glance, tell me what I am missing? Thanks!
    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 which you 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")

    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 = ActiveWorkbook.PivotCaches.Add(xlDatabase, PRange)
    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", "Legacy Program", "Loss Development Factor"), _
    ColumnFields:="Major Coverage Line"

    'Set up the data fields
    With PT.PivotFields("Claim Number")
    .Orientation = xlDataField
    .Function = xlCount
    .Position = 1
    End With

    xlWrkbk.ActiveSheet.PivotTables("PT4FutureUse").Pi votFields("Major Coverage Line"). _
    Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
    False, False)

    ' calc the pivot table
    'PT.ManualUpdate = False
    'PT.ManualUpdate = True
    PT.Update

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

    PT.TableStyle = "PivotStyleMedium10"

    Columns("A:H").Select
    Selection.ColumnWidth = 12.29
    Rows("3:4").Select
    With 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
    '================================================= =======

    I believe once you show me the err of my ways in the code above, I might be able to keep myself straightened out..

    thanks,

    Ken

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

    Re: Pivot Table via VBA (2003)

    It's the same error as before. This time it's in the part

    Columns("A:H").Select
    Selection.ColumnWidth = 12.29
    Rows("3:4").Select
    With Selection

    Columns is not prefixed with a valid Excel object.
    Selection is not prefixed with a valid Excel object (twice).
    Rows is not prefixed with a valid Excel object.

    Prefix each of them with <code>xlApp.</code> so that they belong to the xlApp object you created.

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

    Re: Pivot Table via VBA (2003)

    Hans,

    Once again I owe you a thank you. May I send you a Stetson from Texas to show my appreciation?

    Ken

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

    Re: Pivot Table via VBA (2003)

    Thanks for the offer, but I never wear a hat.. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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