PIVOT-Datenquelle (ODBC) per VBA ändern
01.12.2009 13:48:53
NoNet
Hallo W.,
was ich aus Deinen Beispieldateien herauslesen konnte :
Die "Auswertung" ist mit den Daten der Mappe "Daten" per ODBC verknüpft.
Der Verknüpfungsstring lautet :
ODBC;DSN=Excel-Dateien;DBQ=H:\TABELLEN\66\664\6647\664711_Kläranlagen\664711KA-Analysen\ _
6647KA_Test.xls;
DefaultDir=H:\TABELLEN\66\664\6647\664711_Kläranlagen\664711KA-Analysen;DriverId=790;MaxBufferSize=2048;PageTimeout=5;
Genau hier muss man ansetzen : Im ODBC-Verknüpfungsstring mus man den Dateinamen
H:\TABELLEN\66\664\6647\664711_Kläranlagen\664711KA-Analysen\6647KA_Test.xls
durch den gewünschten Dateinamen ersetzen.
Das kann man mit folgendem Makro erreichen (hier : Datei "S:\Daten\Ordner\....\" - bitte anpassen !)
Makro bitte in ein allgemeines Modul (z.B. "Modul1") im VBA-Editor kopieren :
Sub PivotQuelleODBCAendern()
'Ändert die Datenquelle des ersten PIVOT-Caches der Mappe
'Gilt nur für PIVOT-Tabellen, deren Quelle per ODBC mit anderer Mappe verknüpft ist !
'01.12.2009, NoNet - www.excelei.de (z.Zt. OFFLINE !)
Dim strConn As String, strQuelleAlt As String, strQuelleNeu As String
If ActiveWorkbook.PivotCaches(1).SourceDataFile "" Then
strQuelleAlt = ActiveWorkbook.PivotCaches(1).SourceDataFile
Else
strConn = ActiveWorkbook.PivotCaches(1).Connection
End If
strQuelleAlt = Mid(strConn, InStr(strConn, "DBQ=") + 4, InStr(strConn, ";DefaultDir=") - _
InStr(strConn, "DBQ=") - 4)
'Falls Quelldatei in beliebigem Ordner unter beliebigem Namen gespeichert ist :
'strQuelleNeu = "S:\Daten\Ordner\Quelldaten\Quelle 2009-11-30.xls"
'Falls Quelldatei im gleichen Ordner liegt wie die Auswertungsdatei und Dateiname gleich _
bleibt, dann diese Variante :
strQuelleNeu = ActiveWorkbook.Path & "\" & Split(strQuelleAlt, "\")(UBound(Split( _
strQuelleAlt, "\")))
strConn = Replace(strConn, strQuelleAlt, strQuelleNeu)
'strConn = Replace(strConn, "Excel-Dateien", "Excel Files") 'falls englische Excel-Version ! _
ActiveWorkbook.PivotCaches(1).Connection = strConn
End Sub
Testen konnte ich das allerdings nur ansatzweise mit Deinen Dateien, da die Auswertung auf Daten zugreift, die in der "Daten"-Datei nicht mehr existieren (wie von dir beschrieben : gelöscht).
Dies geht auch aus dem SQL-String der PIVOT-Abfrage hervor :
SELECT Kläranlagen.Kläranlage, Kläranlagen.Art, Kläranlagen.Betreiber,
Kläranlagen.Parameter, Kläranlagen.`ÜW min`, Kläranlagen.`ÜW max`,
Kläranlagen.`Zus#`, Kläranlagen.Messwert, Kläranlagen.Einheit,
Kläranlagen.Prüfdatum, Kläranlagen.Labor, Kläranlagen.`Prüfer `,
Kläranlagen.`Vergleich ÜW/MW`, Kläranlagen.Ergebnis,
Kläranlagen.`Abw#Abgabe`, Kläranlagen.Jahr, Kläranlagen.Analyseverfahren,
Kläranlagen.`Anhang (AbwV)`, Kläranlagen.`GK n# Anh# 1 AbwV`,
Kläranlagen.MA, Kläranlagen.`Probe für KA-Schau`,
Kläranlagen.`KA-Schau (Jahr)`
FROM Kläranlagen Kläranlagen
Union select* from Beneke Beneke
Union select* from Damme Damme
Union select* from Dinklage Dinklage
Union select* from Goldenstedt Goldenstedt
Union select* from Hagola Hagola
Union select* from Holdorf Holdorf
Union select* from LNordlohne LNordlohne
Union select* from LRießel LRießel
Union select* from Nietfeld Nietfeld
Union select* from Ovobest Ovobest
Union select* from SDüpe SDüpe
Union select* from SHonkW SHonkW
Union select* from Siemer Siemer
Union select* from Stolle Stolle
Union select* from Vechta Vechta
Union select* from Visbek Visbek
Teste das Makro doch bitte für diese Dateien und gib hier eine kurze (Mis-)Erfolgsmeldung als Feedback. Danke.
Gruß, NoNet