Hallo Arne,
Ich mach das mit einem Makro.
Du solltest entweder ein neues Tabellenblatt erzeugen, oder die Tabelle leeren.
Dann das Makro ausführen.
Bei mir und einer Oracle Datenbank funktioniert das. Du musst natürlich Deine Abfrage und Deine Datenbank anpassen.
So kann man immer seine Abfrage ändern und eine neue Pivot erzeugen
Gruß
Fritz
PS.: Dies ist ein Ausschnitt aus einem größeren Makro. Kann sein, dass Du die Variablen nicht alle benötigst. Habe nicht darauf geachtet.
Sub MakePiewo()
Public ODBCDrv$
Public ODBCQuelle$
Public Server$
Public Beschreibung$ ' optional
Dim ProIdStr$, MapName$, TabName$
Dim ProId()
Dim Proj_Id_Str$
Dim SQLString$
Dim CN As New ADODB.Connection
Dim Rs As ADODB.Recordset
Dim ListItem$
Dim Counter&
Dim ProjektString$
Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command
Dim I&, J&, K&, X&, Y&, Z&, Result&
On Error Resume Next
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = "ODBC;DSN=XXX;UID=XXX;PWD=XXX;SERVER=XXX;"
'.Connection = _
"ODBC;DRIVER={Microsoft ODBC for Oracle};UID=pas;PWD=XXX;SERVER=XXX;"
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT EXP_LBPIVOT_V_V1.ELBPI_EXPL_ID, EXP_LBPIVOT_V_V1.ELBPI_SATZART, EXP_LBPIVOT_V_V1.ELBPI_KUND_ID, EXP_LBPIVOT_V_V1.ELBPI_KUND_BEZEICHNUNG, EXP_LBPIVOT_V_V1.ELBPI_PROJ_ID, EXP_LBPIVOT_V_V1.ELBPI_P" _
, _
"ROJ_BEZEICHNUNG, EXP_LBPIVOT_V_V1.ELBPI_KUGE_ID_REGION, EXP_LBPIVOT_V_V1.ELBPI_KUGE_FREMD_ID_REGION, EXP_LBPIVOT_V_V1.ELBPI_KUGE_ID_GEBIET, EXP_LBPIVOT_V_V1.ELBPI_KUGE_FREMD_ID_GEBIET, EXP_LBPIVOT_V_V" _
, _
"1.ELBPI_KUGE_ID_BEZIRK, EXP_LBPIVOT_V_V1.ELBPI_KUGE_FREMD_ID_BEZIRK, EXP_LBPIVOT_V_V1.ELBPI_KENNZEICHEN, EXP_LBPIVOT_V_V1.ELBPI_GEBE_ID, EXP_LBPIVOT_V_V1.ELBPI_GEBE_BEZEICHNUNG, EXP_LBPIVOT_V_V1.ELBPI" _
, _
"_DIST_BEZEICHNUNG, EXP_LBPIVOT_V_V1.ELBPI_GEBI_BEZEICHNUNG, EXP_LBPIVOT_V_V1.ELBPI_EINS_WVSID, EXP_LBPIVOT_V_V1.ELBPI_EINS_MATCHCODE, EXP_LBPIVOT_V_V1.ELBPI_EINS_ADRE_PLZ_ORT, EXP_LBPIVOT_V_V1.ELBPI_E" _
, _
"INS_ADRE_STRASSE, EXP_LBPIVOT_V_V1.ELBPI_EINS_ADRE_ORT, EXP_LBPIVOT_V_V1.ELBPI_MARKTUNDADRESSE, EXP_LBPIVOT_V_V1.ELBPI_KUEI_FREMDID, EXP_LBPIVOT_V_V1.ELBPI_KUEI_BEZEICHNUNG, EXP_LBPIVOT_V_V1.ELBPI_TAE" _
, _
"T_KUERZEL, EXP_LBPIVOT_V_V1.ELBPI_WERT_01, EXP_LBPIVOT_V_V1.ELBPI_WERT_02, EXP_LBPIVOT_V_V1.ELBPI_WERT_03, EXP_LBPIVOT_V_V1.ELBPI_WERT_04, EXP_LBPIVOT_V_V1.ELBPI_WERT_05, EXP_LBPIVOT_V_V1.ELBPI_WERT_0" _
, _
"6, EXP_LBPIVOT_V_V1.ELBPI_WERT_07, EXP_LBPIVOT_V_V1.ELBPI_WERT_08, EXP_LBPIVOT_V_V1.ELBPI_WERT_09, EXP_LBPIVOT_V_V1.ELBPI_WERT_10, EXP_LBPIVOT_V_V1.ELBPI_WERT_11, EXP_LBPIVOT_V_V1.ELBPI_WERT_12, EXP_L" _
, _
"BPIVOT_V_V1.ELBPI_WERT_KUMULIERT, EXP_LBPIVOT_V_V1.ELBPI_ERSTELLUNGSDATUM" & Chr(13) & "" & Chr(10) & "FROM PASEXP.EXP_LBPIVOT_V_V1 EXP_LBPIVOT_V_V1" & Chr(13) & "" & Chr(10) & "WHERE (EXP_LBPIVOT_V_V1.ELBPI_PROJ_ID in (" & ProjektString & ")" & ")" _
)
.CreatePivotTable TableDestination:="[" & MapName & "]Tabelle1!R3C1", TableName _
:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
End With
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Daten", _
PageFields:=Array("ELBPI_KUGE_FREMD_ID_BEZIRK", "ELBPI_KUGE_FREMD_ID_GEBIET", _
"ELBPI_KUGE_FREMD_ID_REGION", "ELBPI_KUND_BEZEICHNUNG", "ELBPI_PROJ_BEZEICHNUNG", "ELBPI_TAET_KUERZEL", _
"ELBPI_ERSTELLUNGSDATUM", "ELBPI_SATZART", "ELBPI_KUEI_FREMDID", "ELBPI_MARKTUNDADRESSE") ', "ELBPI_KUEI_BEZEICHNUNG", "ELBPI_EINS_ADRE_STRASSE", _
"ELBPI_EINS_ADRE_PLZ_ORT", "ELBPI_EINS_ADRE_ORT")
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ELBPI_WERT_01")
.Orientation = xlDataField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ELBPI_WERT_02")
.Orientation = xlDataField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ELBPI_WERT_03")
.Orientation = xlDataField
.Position = 3
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ELBPI_WERT_04")
.Orientation = xlDataField
.Position = 4
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ELBPI_WERT_05")
.Orientation = xlDataField
.Position = 5
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ELBPI_WERT_06")
.Orientation = xlDataField
.Position = 6
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ELBPI_WERT_07")
.Orientation = xlDataField
.Position = 7
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ELBPI_WERT_08")
.Orientation = xlDataField
.Position = 8
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ELBPI_WERT_09")
.Orientation = xlDataField
.Position = 9
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ELBPI_WERT_10")
.Orientation = xlDataField
.Position = 10
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ELBPI_WERT_11")
.Orientation = xlDataField
.Position = 11
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ELBPI_WERT_12")
.Orientation = xlDataField
.Position = 12
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("ELBPI_WERT_KUMULIERT"). _
Orientation = xlDataField
Application.CommandBars("PivotTable").Visible = False
ActiveWorkbook.ShowPivotTableFieldList = False
'-------------------------Row-Fields--------------------------
'Range("A12").Select
With ActiveSheet.PivotTables("PivotTable1").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With
'Range("A5").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ELBPI_KUEI_FREMDID")
.Orientation = xlRowField
.Position = 1
End With
'Range("A5").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"ELBPI_MARKTUNDADRESSE")
.Orientation = xlRowField
.Position = 3
End With
'Range("A6").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ELBPI_SATZART")
.Orientation = xlRowField
.Position = 6
End With
'-------------------------Page-Fields sortieren -------------------------------------
' Range("A9").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"ELBPI_KUND_BEZEICHNUNG")
.Orientation = xlPageField
.Position = 7
End With
'Range("A6").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"ELBPI_PROJ_BEZEICHNUNG")
.Orientation = xlPageField
.Position = 6
End With
'Range("A7").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"ELBPI_KUGE_FREMD_ID_REGION")
.Orientation = xlPageField
.Position = 5
End With
'Range("A8").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"ELBPI_KUGE_FREMD_ID_GEBIET")
.Orientation = xlPageField
.Position = 4
End With
'Range("A9").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"ELBPI_KUGE_FREMD_ID_BEZIRK")
.Orientation = xlPageField
.Position = 3
End With
'Range("A10").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ELBPI_TAET_KUERZEL")
.Orientation = xlPageField
.Position = 2
End With
Rows(3).Delete
'------Überschriften-------------------
FitColumns
Call SaveTabelleAs("C:\temp\xxx.xls", False)
End Sub