Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Pivotdatenquelle in unzähligen Exceldateien ändern

Pivotdatenquelle in unzähligen Exceldateien ändern
14.08.2019 15:02:46
Kai
Hallo,
ich habe hier schon viel Hilfe erhalten in den letzten Jahren ohne selbst Fragen stellen zu müssen (vielen dank dafür), aber nun ists wohl mal Zeit dafür.
Mein Problem:
es gibt ca 3000 Exceldateien aus Excel 2003 deren pfad zur pivotdatenquelle aus unerfindlichen gründen geändert und abweichend zu den restlichen 4000 dateien ist.
der unterschied ist lediglich ein unterordner, der anders benannt ist, daher die kurzfristige lösung: symlink des ordners...ist aber natürlich nichts wirklich schönes, daher die frage ob ich nun mit einer art batch oder makro es so lösen kann, dass diese datenquelle in allen dateien geändert wird.
jede datei einzeln zu öffnen und den pfad zu ändern ist bei der schieren menge der dateien einfach nicht umsetzbar

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Nachfragen
14.08.2019 16:21:38
Fennek
Hallo,
im Normalfall sind die Daten einer Pivot-Tabelle im selben Workbook.
Bitte zeige, mit welchem VBA-Code die Quells ausliest.
mfg
AW: Nachfragen
14.08.2019 17:58:07
Kai
Kein vba, nur über den pivot Assistenten den Pfad der quelldaten xls eingetragen inkl spaltendefinition
Und im Pfad ist halt eben ein ordnername falsch und müsst ersetzt werfen
AW: Details!
14.08.2019 18:29:49
Fennek
Hallo,
in den neuen Xl-Versionen gibt es eine andere Pivot als in xl2003. Du musst schon die nötigen Ansätze für eine (mögliche) Lösung liefern!
In den neueren Versionen gibt es im Bereich "Info" den Bereich "links". Steht dort etwas? Mache zumindest einen Screenshot mit den Pfad-Angeben.
mfg
(Dass du ein Problem hast, ist gut rübergekommen. Aber wenn du beim nächsten Post nicht alle nötigen Infos lieferst, kannst du recht schnell auf meiner Ignore-Liste landen. Ist aber kein Problem, es gibt viele andere hier)
Anzeige
Schön formulierte Verwarnung :-)
15.08.2019 07:14:36
lupo1
Warum fällt mir so etwas nie ein?
AW: Pivotdatenquelle in unzähligen Exceldateien ändern
15.08.2019 07:29:33
Kai
ja sorry, wenn infos fehlen, aber ich hab das weder erstellt, noch jemals was mit pivot zu tun gehabt. und neuere versionen sind nicht zur hand, nur 2003
also nochmal kurz umrissen:
-es gibt nen haufen excel-dateien
-in diesen gibt es ein tabellenblatt mit daten, die aus einer zentralen xls datei "aktualisiert" werden
-der pfad zu dieser lautet zb 'X:\überarbeitet\[Vorlage.xls]Prüfvorschrift'!$A:$A
-Problem daran ist, dass der ordner "überarbeitet" nur temporär existierte und dieser nun "aktuell" heißen soll und dies auch tut, da alle dateien nun dort liegen, nur leider eben nur bei ca der hälfte der excel dateien die diese verknüpfung haben. warum und wieso der ordner bei der einen hälfte einwandfrei übernommen wird, sprich variabel ist, auf den ordner, in dem die datei xy und auch die vorlage liegen und bei der anderen nicht, ist mir ein rätsel.
lege ich eine der dateien auf meinen desktop, sucht die datei, die funktioniert auf dem desktop nach der vorlage. mach ich das gleiche mit einer defekten datei, ändert sich nur x: in c: und der rest des pfades bleibt gleich
wenn ihr mir sagt wo und wie genau ich euch weitere infos geben soll, ist das kein problem, aber bitte setzt bei diesem pivot zeug nicht voraus, das ich sofort weiß, was ihr meint ;)
Anzeige
AW: Pivotdatenquelle in unzähligen Exceldateien ändern
15.08.2019 07:31:10
Kai
achso: unter ?/info finde ich keine links
AW: Pivotdatenquelle in unzähligen Exceldateien ändern
15.08.2019 18:37:50
fcs
Hallo Kai Uwe,
es ist in der Tat etwas schwierig mit neueren Excelversionen die Pivot-Auswertungen an einer 2003er-Datei zu testen/anzupassen, wenn die Datenquelle extern ist.
Probiere mal folgendes:
Zeichne ein Makro auf (in deiner persönlichen Makro-Arbeitsmappe speichern)
1. öffne eine Datei mit Pivot-Bericht in der die Daten-Verbindung falsch ist.
2. korrigiere die Datenquelle des Pivotberichtes.
3. aktualisiere den Pivotbericht.
Mit etwas Glück werden alle notwendigen Informationen und Aktionen aufgezeichnet.
Das aufgezeichnete Makro kopierst du hier in deine Antwort.
Dabei einmal das Code-Tag (via Schaltfläche) in der Antwort einfügen und den Makrotext zwischen den eckigen Klammern &gt&lt einfügen.
Alternative: Makro-Code in eine Text-Datei kopieren und diese hier hochladen.
Je nachdem wie gut die Aufzeichnung ist musst der Code jetzt so angepasst werden, dass er allgemein auf alle deine Problem-Dateien angewendet werden kann.
Bei dieser Anpassung können wir dir dann behilflich sein.
LG
Franz
Anzeige
AW: Pivotdatenquelle in unzähligen Exceldateien ändern
16.08.2019 07:00:31
Kai
das einzige was dabei rumkommt, wenn der pfad korrigiert wird, ist:
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'[Vorlage.xls]Prüfvorschrift'!C1"
AW: Pivotdatenquelle in unzähligen Exceldateien ändern
16.08.2019 12:48:33
fcs
Hallo Kai Uwe,
versuche es mal so, dass du den Pfad der Datei mit ins Makro einbaust.
Beispiel:
    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'C:\Users\Public\NeuTest\[Vorlage.xls]Prüfvorschrift'!C1"

Um jetzt eine Vielzahl von Dateien automatisch abzuarbeiten müsste man alle relevanten Dateien in einer Schleife abarbeiten und erst prüfen, ob der Pivotbericht eine falsche Quelle hat. Wenn ja, dann Quelle ändern, wenn nein, dann Quelle anpassen.
Wenn du dir die Zeit nehmen kannst, dann kannst du die Dateien manuell öffnen un manuell die Quelle per Makro anpassen.
LG
Franz
Gruß
Franz
Anzeige
AW: Pivotdatenquelle in unzähligen Exceldateien ändern
16.08.2019 12:55:52
Kai
der komplette pfad wird mir bei der aufzeichnung korrekt angezeigt, nur nicht ins makro geschrieben, wenns der richtige ist. setz ich den falschen ein nimmt er ihn mit rein:
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'X:\überarbeitet\[Vorlage.xls]Prüfvorschrift'!C1"
wenn ich das nunrichtig sehe, brauch ich nun ein skript/makro was alle dateien in ordner x öffnet und dort
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'[Vorlage.xls]Prüfvorschrift'!C1" ausführt, speichert und wieder schließt, dann nächste datei?
weil richtig wäre es, wenn der ordner, der gleiche ist, wie die hauptdatei, daher nehm ich an ohne pfad und somit dann wohl auch variabel. falsch ist es hingegen wenn der eingetragene ordner eben nicht der quellordner der datei ist
Anzeige
AW: Pivotdatenquelle in unzähligen Exceldateien ändern
18.08.2019 13:48:57
fcs
Hallo Kai Uwe,
ich hab mal ein entsprechendes Makro geschrieben.
Es funktioniert auch unter der neuesten Excel-Version (Office 365 in 2019)
LG
Franz
Sub Updatei_Piovot_Quelle()
Dim wkb As Workbook
Dim wksPivot As Worksheet
Dim objPivotTab As PivotTable
Dim strSource As String, strSourceData As String
Dim varPfad As Variant, strDatei As String
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Bitte Ordner mit den zu aktualisierenden Dateien auswählen"
If .Show = -1 Then
varPfad = .SelectedItems(1)
Else
Exit Sub
End If
End With
strSource = "Vorlage.xls" 'name der Quelldatei der Pivot-Tabellenberichte
strDatei = Dir(varPfad & Application.PathSeparator & "*.xls", vbNormal)
Application.ScreenUpdating = False
Do Until strDatei = ""
Application.StatusBar = "Aktualisiere Datei: " & strDatei
Select Case LCase(strDatei)
Case LCase(strSource), LCase(ThisWorkbook.Name)
'Bei diesen Dateinamen Pivot-Update nicht ausführen
Case Else
Set wkb = Application.Workbooks.Open(varPfad _
& Application.PathSeparator & strDatei, UpdateLinks:=False)
Set wksPivot = wkb.Worksheets(1)
wksPivot.Activate
If wksPivot.PivotTables.Count > 0 Then
Set objPivotTab = wksPivot.PivotTables(1)
strSourceData = objPivotTab.SourceData
'                MsgBox "PivotSource: " & strSourceData
'Überprüfen, ob Quelldatei in anderem Verzeichnis
If Left(strSourceData, InStr(strSourceData, "]"))  _
"'" & Mid(varPfad, InStrRev(varPfad, Application.PathSeparator) + 1) _
& Application.PathSeparator & "[" & strSource & "]" Then
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, _
SourceData:="'" & varPfad & Application.PathSeparator _
& "[" & strSource & "]Prüfvorschrift'!C1"
'                    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, _
SourceData:="'" & varPfad & Application.PathSeparator _
& "[" & strSource & "]Prüfvorschrift'!A1:D7" 'für meine Test-Dateien
Application.DisplayAlerts = False
wkb.Save
Application.DisplayAlerts = True
End If
End If
wkb.Close savechanges:=False
End Select
strDatei = Dir
Loop
Application.ScreenUpdating = True
Application.StatusBar = False
MsgBox "Fertig", vbOKCancel, "Pivottabs - Quelle aktualisieren"
End Sub

Anzeige
AW: Pivotdatenquelle in unzähligen Exceldateien ändern
19.08.2019 09:18:42
Kai
vielen vielen dank für deine arbeit fcs!! das macht genau das was ich gesucht habe! echt, richtig klasse!
gelöst, dann aber nicht mehr offen... owT
19.08.2019 11:54:00
Werner

307 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige