Office: Excel 2010 - Pivot CommandText der Verbindung ändern

Hallo zusammen.

ich habe den in einer Verbindung hinterlegten SQL-Befehl variabel gestalten.
Also so das ich in Zeile A1 einen Code eingebe und dieser im WHERE-Bereich hinzugefügt wird.

Das auslesen des Commandtext klappt und auch das anpassen ist kein problem.
Möchte ich aber nun den neuen Commandtext an die Verbindung übergeben generiert er eine neue Verbindung mit neuem namen und ordent diese der zugewiesenen pivot zu. Dadurch ist das Makro nicht weiter nutzbar, da sich der Verbindungsname geändert hat.

Hat jemand eine Idee was ich da falsch mache?
Der Übergabecode ist wie folgt:
ActiveWorkbook.Connections("DATEN").ODBCConnection.CommandText = vSQL
in vSQL ist die komplette SQL-Abfrage hinterlegt.

Gruß und Danke
Helmbarte

 

Grüezi Helmbarte

Hmmmm, das Fragment alleine kann IMO noch nicht für das Neu-Anlegen einer Verbindung verantwortlich sein.

Wie lautet denn der komplette Code, den Du vewendest?

Vermutlich solltest du nicht die Verbindung selbst, sondern nur den Pivot-Cache verändern.

 

Hallo Thomas.

ja das habe ich auch zuerst gemacht (den Pivot-Cache verändern). Beziehe ich mich expliziet auf eine Pivot klappt dies auch ohne Probleme.
Jedoch funktioniert dieses Vorgehen nur wenn die von der Pivot genutzte Verbindung alleine von Ihr verwendet wird. Ansonsten läuft er in einen Fehler beim übergeben des neuen CommandText.
In meinem Fall ziehen sich aber 10 Pivots die Daten aus einer Verbindung. Jeweils anders gefiltert um es übersichtlicher zu haben.
Aus diesem Grund wollte ich mich nun dirrekt auf die Verbindung beziehen und diese somit für alle Pivots zentral ändern.

Code:

Sub Makro3()

Dim vSQL, iPos, iLen, iRLen, textRechts, vSQLnew, vSQLnewb

'Auslesung des aktuellen Codes
vSQL = ActiveWorkbook.Connections("Verbindung2").ODBCConnection.CommandText

'Berechnung der Position wo sich WHERE befindet
iPos = InStr(1, vSQL, "WHERE")  'Liefert die Stelle wo Where beginnt
iLen = Len(vSQL)                'Ermittelt die gesamtlänge des Codes
iRLen = iLen - iPos + 1         'Ermittelt die länge des Codes ab Where + 1 da sonst "W" nicht gezählt wird
textRechts = Right(vSQL, iRLen)    'Zeigt den ganzen Text nach Where

'Ermittelt den neuenn WHERE-Bereich
vSQLnew = "WHERE v.code = '" & Range("a1").Value & "' "

'Liefert den gesamten neuen Code indem es den WHERE-Bereich austauscht / Neu gegen Alt
vSQLnewb = Replace(vSQL, textRechts, vSQLnew)

'Überschreibt den alten Bereich mit dem neuen Bereich
vSQL = vSQLnewb
'#########  Bis hierhin kein Problem  ################################

[COLOR=#ff0000]ActiveWorkbook.Connections("Verbindung2").ODBCConnection.CommandText = vSQL[/COLOR]

End Sub

Der Code soweit unveränder nur Anfang und Ende ausgetauscht und somit nur bezogen auf eine Pivot funktioniert:

Code:

vSQL = ActiveSheet.PivotTables(1).PivotCache.Sql
.......
ActiveSheet.PivotTables(1).PivotCache.Sql = vSQL

Gruß,
Helmbarte

 

Grüezi Helmbarte

Hmmm, hast Du denn die Daten in einem Tabellenblatt als Datenbereich 'zwischengelagert', oder ist die Verbindung noch immer die einzige Quelle für den Pivot-Cache?

Wenn zweites dann wird das aus denselben Gründen nicht klappen weil mehrere Pivots auf diesen Cache zugreifen.


Importiere daher die Daten in ein Tabellenblatt und verwende diese dann als Basis für die Pivot-Tabellen.

Dann kannst Du die Daten aktualisieren und anschliessend die Pivot-Tabellen ebenfalls.

 

Hallo Thomas,

die Pivots greifen alle auf die gleiche Verbindung zu.
Eine Zwischenspeicherung der Daten erfolgt nicht.
Würde dies nicht die Geschwindigkeit einschränken und zu einer Doppelspeicherung führen (400.000Zeilen;30Spalten)?
Gibt es keine Möglichkeit den Fehler zu umgehen?
Ich probiere aber mal deinen Vorschlag. Danke hierfür.

Gruß,
Helmbarte

 

Grüezi Helmbarte

Das ist ein kleiner Irrtum - Pivots greifen immer auf den Pivot-Cache zu.
In deinem Falle greifen mehrere PTs auf einen gemeinsamen Cache zu und daher lässt sich dieser nicht direkt manipulieren.

Aus diesem Grunde bleibt nur die Ausweich-Lösung einer Abfrage welche die Daten in ein Tabellenblatt importiert und der Zugriff des Pivot-Caches dann auf diese importierten Daten. Das hat zwar eine Verdoppelung des Speicherbedarfes zur Folge (oder Du lässt die Daten der Abfrage beim Schliessen nicht speichern), ist aber meines Wissens die einzige Alternative in diesem Punkt.