Pivot Tabellen Erstellen in Excel

  • VB.NET

Es gibt 1 Antwort in diesem Thema. Der letzte Beitrag () ist von minimaster.

    Pivot Tabellen Erstellen in Excel

    Guten Tag,

    ich finde im Internet keine vernünftigen Referenzen zum Thema Pivot über VB.Net.
    Ich will über VB.Net eine automatisierte Pivot Tabelle erstellen lassen. Diese habe ich vorerst über ein
    Makro in Excel erledigt, das war auch soweit kein Problem nur leider kann man das Makro nicht so einfach in VB.net übernehmen...

    VB.NET-Quellcode

    1. Sub pivot()
    3. Dim Spaltenlaenge As Byte
    4. Dim Zeilenlaenge As Byte
    6. Spaltenlaenge = 2
    8. Do Until xlApp.Range("A" & Spaltenlaenge).Value = ""
    9. Spaltenlaenge = Spaltenlaenge + 1
    10. Loop
    11. Spaltenlaenge = Spaltenlaenge - 1
    13. Zeilenlaenge = 1
    14. Do Until xlApp.Cells(1, Zeilenlaenge).Value = ""
    15. Zeilenlaenge = Zeilenlaenge + 1
    16. Loop
    17. Zeilenlaenge = Zeilenlaenge - 1
    20. xlApp.Range(xlApp.Cells(1, 1), xlApp.Cells(Spaltenlaenge, Zeilenlaenge)).Select()
    21. xlApp.Cells(Spaltenlaenge, Zeilenlaenge).Activate()
    23. '''''''''''''' HIER FANGEN DIE PROBLEME AN '''''''''''''''''''
    24. xlMappe.PivotCaches.Add(SourceType:=xlZelle.xlDatabase, SourceData:= _
    25. "Tabelle2!R1C1:R26C9").CreatePivotTable(TableDestination:="", TableName:= _
    26. "PivotTable2")
    28. xlBlatt.PivotTableWizard(TableDestination:=xlBlatt.Cells(3, 1))
    29. xlBlatt.Cells(3, 1).Select()
    30. xlBlatt.PivotTables("PivotTable2").SmallGrid = False
    31. xlBlatt.PivotTables("PivotTable2").AddFields(RowFields:=xlBlatt.Array( _
    32. "Artikel Lot", "Lot Nr", "Farbe Lot"), ColumnFields:="Größe")
    33. xlBlatt.PivotTables("PivotTable2").PivotFields("Bestand").Orientation = _
    34. xlBlatt.xlDataField
    35. ExcelClose()
    36. End Sub


    Wie man sieht selektiert der erstmal den nötigen Bereich, aber ab der makierten Zeile funktioniert gar nichts mehr...
    und da ich leider sonst nichts im Internet finde wollte ich dies bezüglich euch mal um Hilfe bitten:)

    I found it helpful to develop the code in VBA and then I just port it to VB.Net.

    To have good code portability I'm declaring your "xlApp" object in my VB.NEt code as "Application". This way the VB.Net code should run fine in VBA too and vice versa. A bit problematic is that the data type "variant" does not exist in VB.net. Sometimes the variant data type (and especially variant arrays) are very handy and therefore I deliberatly stick to VBA in these cases.

    VB.NET-Quellcode

    1. ' the name "Application" is chosen to have code copy and paste compatibility with the VBA editor
    2. Public WithEvents Application As Microsoft.Office.Interop.Excel.Application


    Specifically in your code this "SourceType:=xlZelle.xlDatabase" looks a bit strange to me.

    Furthermore I would first create a named range for the Pivottable source and then use the named range for Sourcedata.

    The line with PivotTableWizard should not be required at all to create a pivottable via code. I think you can just delete that line without effect.

    Ich merke gerade das ich fälschlicherweise in English geantwortet habe. Ich hoffe es ist trotzdem einigermassen verständlich.

    Noch mal etwas Zusatzcode wie es aussehen könnte - Alles ungetestet

    VB.NET-Quellcode

    1. #Region "Imports"
    2. ' Namespace Imports
    3. Imports Microsoft.Office.Interop.Excel
    4. ' Enum imports
    5. Imports Microsoft.Office.Interop.Excel.Constants
    6. Imports Microsoft.Office.Interop.Excel.XlYesNoGuess
    7. Imports Microsoft.Office.Interop.Excel.XlSortDataOption
    8. Imports Microsoft.Office.Interop.Excel.XlSortOrder
    9. Imports Microsoft.Office.Interop.Excel.XlSortType
    10. Imports Microsoft.Office.Interop.Excel.XlConsolidationFunction
    11. Imports Microsoft.Office.Interop.Excel.XlFindLookIn
    12. Imports Microsoft.Office.Interop.Excel.XlLookAt
    13. Imports Microsoft.Office.Interop.Excel.XlSearchOrder
    14. Imports Microsoft.Office.Interop.Excel.XlSearchDirection
    15. Imports Microsoft.Office.Interop.Excel.XlPasteType
    16. Imports Microsoft.Office.Interop.Excel.XlPattern
    17. Imports Microsoft.Office.Interop.Excel.XlColorIndex
    18. Imports Microsoft.Office.Interop.Excel.XlLineStyle
    19. Imports Microsoft.Office.Interop.Excel.XlBordersIndex
    20. Imports Microsoft.Office.Interop.Excel.XlBorderWeight
    21. Imports Microsoft.Office.Interop.Excel.XlSheetType
    22. Imports Microsoft.Office.Interop.Excel.XlPivotFieldOrientation
    23. Imports Microsoft.Office.Interop.Excel.XlPivotTableMissingItems
    24. Imports Microsoft.Office.Interop.Excel.XlPTSelectionMode
    25. Imports Microsoft.Office.Interop.Excel.XlPivotFieldCalculation
    26. Imports Microsoft.Office.Interop.Excel.XlLayoutRowType
    27. Imports Microsoft.Office.Interop.Excel.XlSourceType
    28. Imports Microsoft.Office.Interop.Excel.XlPivotTableVersionList
    29. Imports Microsoft.Office.Interop.Excel.XlPivotFilterType
    30. Imports Microsoft.Office.Interop.Excel.XlPivotTableSourceType
    31. #End Region
    32. Module ptTools1
    33. Sub create_named_Range_db_with_autosize()
    34. On Error Resume Next
    35. With Application
    36. .ActiveWorkbook.Names.Item("DBrange").Delete()
    37. ' in VBA this is slightly easier, but won't be accepted in Vb.net
    38. ' .ActiveWorkbook.Names("DBrange").Delete()
    39. With .ActiveSheet
    40. .ActiveWorkbook.Names.Add(Name:="DBrange", RefersToR1C1:="=OFFSET(" _
    41. & .Name & "!R" & .UsedRange.Row & "C" & .UsedRange.Column & ",0,0,COUNTA(" _
    42. & .Name & "!C2)," & .UsedRange.Columns.Count & ")")
    44. End With
    45. End With
    46. End Sub
    47. '------------------------------------------------------------------------
    48. Sub CreatePivotTable_fromNamedRange_db(Optional data As String)
    49. Dim wsPT As Worksheet
    50. Dim pt As PivotTable
    51. Dim MyPeriods(6) As Boolean
    52. With Application
    53. .Sheets.Add()
    54. wsPT = .ActiveSheetwsPT.Name = "Pivot" & .Sheets.Count
    55. .ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="=DBrange", _Version:=xlPivotTableVersion12).CreatePivotTable(TableDestination:=wsPT.Name & "!R3C1", _
    56. TableName:="PT" & .Sheets.Count, DefaultVersion:=xlPivotTableVersion12)
    57. On Error Resume Next
    58. wsPT.Select()
    59. .Cells(3, 1).Select()pt = .ActiveSheet.PivotTables("PT" & .Sheets.Count)With pt.PivotFields("Date")
    60. .Orientation = xlColumnField
    61. .Position = 1
    62. End With
    63. pt.PivotFields("Date").PivotFilters.Add(Type:=xlDateBetween, Value1:="1/1/2010", Value2:="6/30/2012")
    64. With pt.PivotFields("Country")
    65. .Orientation = xlRowField
    66. .Position = 1
    67. End With
    68. If data <> "" Then
    69. pt.AddDataField(.ActiveSheet.PivotTables("PT" & .Sheets.Count).PivotFields(data), "Sum of " & data, xlSum)
    70. Elsept.AddDataField(.ActiveSheet.PivotTables("PT" & .Sheets.Count).PivotFields("Revenue"), "Sum of Revenue", xlSum)
    71. pt.AddDataField(.ActiveSheet.PivotTables("PT" & .Sheets.Count).PivotFields("USD Revenue"), "Sum of USD Revenue", xlSum)
    72. End If
    73. On Error GoTo 0
    74. With pt
    75. .InGridDropZones = True
    76. .RowAxisLayout(xlTabularRow)
    77. End With
    78. MyPeriods(0) = False
    79. MyPeriods(1) = False
    80. MyPeriods(2) = False
    81. MyPeriods(3) = False
    82. MyPeriods(4) = True
    83. MyPeriods(5) = True
    84. MyPeriods(6) = True
    85. .Range("B3").Group(Start:=True, End:=True, Periods:=MyPeriods)
    86. ' the easier VBA version
    87. '.Range("B3").Group(Start:=True, End:=True, Periods:=Array(False, False, False, False, True, True, True)) '
    88. End With
    89. End Sub
    90. End Module

    Dieser Beitrag wurde bereits 16 mal editiert, zuletzt von „minimaster“ ()

Facebook
Telefonspende